More Efficient Use of Union

All we need is an easy explanation of the problem, so here it is.

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list me as being in the sub part and the part. It does this for 10 levels of sub parts.

I didn’t create this view but the result turns 330k records into over 2 million. The resulting view is then used in various reports. The operation is currently taking over 13 minutes to run. I’m thinking this view was a great idea when where were 2000 parts but at 75,000 it is now unusable.

Here’s the SQL (on 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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This looks like a typical hierarchical query, but it was implemented the “manual” way. I do not think you should focus on the UNION here. You should rather rewrite this as a true hierarchical query by using the CONNECT BY clause. Something like this (just an example, not necessarily what you need):

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;

More about hierarchical queries: here

Method 2

If your conditions can not return records that another returns, then a UNION ALL vs a UNION will help considerably.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply