寻找一种更有效的方法来实现我的观点。此视图将表连接到自身以显示数据库中每个部分的使用位置。换句话说，如果我是某个部分的一部分，请将我列为子部分和部分。它为 10 个级别的子部分执行此操作。
我没有创建此视图，但结果将 33 万条记录变成了超过 200 万条。生成的视图随后用于各种报告。该操作目前需要 13 多分钟才能运行。我认为这个视图在 2000 个零件的位置时是个好主意，但在 75,000 个时它现在无法使用。
这是 SQL（在 Oracle 10g 上）：
CREATE OR REPLACE VIEW PART_COMPLETE_WU
(wu_part, lvl, type, top_part)
AS
SELECT P.ID, 1,'P',R1.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1
WHERE R1.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
UNION
SELECT P.ID, 2,'P', R2.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2
WHERE R2.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
UNION
SELECT P.ID, 3,'P', R3.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3
WHERE R3.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
UNION
SELECT P.ID, 4,'P', R4.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4
WHERE R4.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
UNION
SELECT P.ID, 5,'P', R5.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5
WHERE R5.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
UNION
SELECT P.ID, 6,'P', R6.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5, REQUIREMENT R6
WHERE R6.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
UNION
SELECT P.ID, 7,'P', R7.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7
WHERE R7.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
UNION
SELECT P.ID,8,'P', R8.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8
WHERE R8.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
UNION
SELECT P.ID,9,'P', R9.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9
WHERE R9.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
UNION
SELECT P.ID,10,'P', R10.WORKORDER_BASE_ID FROM PART P, REQUIREMENT R1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9, REQUIREMENT R10
WHERE R10.WORKORDER_TYPE = 'M'
AND R1.PART_ID = P.ID
AND R2.PART_ID = R1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
AND R10.PART_ID = R9.WORKORDER_BASE_ID
UNION
SELECT D.ID, 1, 'D', D1.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1
WHERE D1.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
UNION
SELECT D.ID, 2,'D', R2.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2
WHERE R2.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID =D1.WORKORDER_BASE_ID
UNION
SELECT D.ID, 3, 'D', R3.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3
WHERE R3.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID =D1.WORKORDER_BASE_ID
AND R3.PART_ID =R2.WORKORDER_BASE_ID
UNION
SELECT D.ID, 4, 'D', R4.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4
WHERE R4.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
UNION
SELECT D.ID, 5, 'D', R5.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5
WHERE R5.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
UNION
SELECT D.ID, 6, 'D', R6.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6
WHERE R6.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
UNION
SELECT D.ID, 7, 'D', R7.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7
WHERE R7.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
UNION
SELECT D.ID, 8, 'D', R8.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8
WHERE R8.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
UNION
SELECT D.ID, 9, 'D', R9.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9
WHERE R9.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
UNION
SELECT D.ID, 10, 'D', R10.WORKORDER_BASE_ID FROM DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R2, REQUIREMENT R3, REQUIREMENT R4, REQUIREMENT R5,
REQUIREMENT R6, REQUIREMENT R7, REQUIREMENT R8, REQUIREMENT R9, REQUIREMENT R10
WHERE R10.WORKORDER_TYPE = 'M'
AND D1.DOCUMENT_ID = D.ID
AND R2.PART_ID = D1.WORKORDER_BASE_ID
AND R3.PART_ID = R2.WORKORDER_BASE_ID
AND R4.PART_ID = R3.WORKORDER_BASE_ID
AND R5.PART_ID = R4.WORKORDER_BASE_ID
AND R6.PART_ID = R5.WORKORDER_BASE_ID
AND R7.PART_ID = R6.WORKORDER_BASE_ID
AND R8.PART_ID = R7.WORKORDER_BASE_ID
AND R9.PART_ID = R8.WORKORDER_BASE_ID
AND R10.PART_ID = R9.WORKORDER_BASE_ID;