更有效地使用聯合

尋找一種更有效的方式來實現我的觀點。此視圖將表連接到自身以顯示數據庫中每個部分的使用位置。換句話說，如果我是某個部分的一部分，則將我列為子部分和部分。它對 10 個級別的子元件執行此操作。

我沒有創建此視圖，但結果將 330k 記錄變為超過 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;

這看起來像一個典型的分層查詢，但它是以“手動”方式實現的。我認為你不應該專注於UNION這裡。您應該使用該CONNECT BY子句將其重寫為真正的分層查詢。像這樣的東西（只是一個例子，不一定是你需要的）：

CREATE OR REPLACE VIEW PART_COMPLETE_WU
(wu_part, lvl, type, top_part)
SELECT
 P.ID, LEVEL ,'P', R.WORKORDER_BASE_ID
FROM
 PART P, REQUIREMENT R
WHERE
 R.WORKORDER_TYPE = 'M'
 AND R.PART_ID = P.ID
 AND LEVEL <= 10
CONNECT BY PRIOR R.WORKORDER_BASE_ID = R.PART_ID
UNION
SELECT
 D.ID, LEVEL, 'D', R.WORKORDER_BASE_ID
FROM
 DOCUMENT D, DOCUMENT_REF_WO D1, REQUIREMENT R
WHERE
 R.WORKORDER_TYPE = 'M'
 AND D1.DOCUMENT_ID = D.ID
 AND R.PART_ID = D1.WORKORDER_BASE_ID
 AND LEVEL <= 10
CONNECT BY PRIOR R.WORKORDER_BASE_ID = R.PART_ID;

有關分層查詢的更多資訊：這裡

引用自：https://dba.stackexchange.com/questions/122680