0

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;
Improve this question
2
  • Sorry Matt, not sure where to start ... that query is "nasty" .. and the underlying design is the root cause .. not the UNION itself. I would strongly recommend a redesign of that beast ... that ... is ... so ... nasty. O.o (my other advice is: be patient .. this is going to take a while to look through that and think of options :( )
    – Ditto
    Commented Dec 2, 2015 at 18:49
  • Agreed and thank you. Going to look into hierarchical queries and the with clause. I'll post any good Ideas I find.
    – Matt D
    Commented Dec 3, 2015 at 14:36

2 Answers 2

Reset to default
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

Improve this answer
1
  • Nice, I was thinking this might be an option, but couldn't wrap my head around it today - too many distractions :)
    – Ditto
    Commented Dec 2, 2015 at 19:39
0

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

Improve this answer
3
  • The final report does a select distinct from this view. Not sure of the effect of UNION ALL on the report (most likely none). Doing a select count(*) appears as if it shaves 1 minute off of execution and adds about a million rows to the result
    – Matt D
    Commented Dec 2, 2015 at 16:47
  • @byrdzeye - if you look closely at the first 2 queries, you'll see quickly that they do in fact overlap ... this entire query/design is a nightmare .. O.o
    – Ditto
    Commented Dec 2, 2015 at 18:47
  • Yes they do 10 times. Oh and the 2nd half of the view does the same thing but from the document table rather than parts.
    – Matt D
    Commented Dec 3, 2015 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.