Can I Just Growl??
The following query returns 87 unique Sales Order Item Numbers, nearly instantly:
SELECT DISTINCT SDITM FROM (SELECT SDITM FROM TESTDTA.F4211, TESTDTA.F3002
WHERE SDITM=IXKIT AND SDMCU=IXMMCU AND SDMCU=' 2620001' AND SDNXTR<'560' AND SDPDDJ<115108 AND SDITM != 0 AND ROWNUM<=10000);
If I cut/paste the results into the following, the results return in less than 60 seconds:
SELECT CONNECT_BY_ROOT(IXKITL) SO_ITEM, LEVEL, IXKITL, IXKIT, IXMMCU, IXCPNB/100 SEQ, IXITM, IXLITM, IXQNTY/1000 AS QNTY
FROM TESTDTA.F3002
WHERE IXTBM='M' AND IXMMCU=' 2620001'
START WITH IXKIT IN (59,163281,268267,307497,307512,494557,618101,1635295,1745226,
1838945,1838946,1838947,1838948,1838949,1838950,1869979,1879401,1885481,1886991,
1886992,1891497,1909647,1970791,1970792,1970797,1970798,1977001,1977002,1977003,
1977004,1977005,1977006,1977007,1977008,1977009,1977010,1989970,1989971,1989972,
1989973,2003743,2012919,2012923,2065511,2065513,2065515,2065517,2065519,2065521,
2065523,2065525,2065527,2065529,2065531,2093584,2109586,2109587,2109588,2109589,
2109590,2109591,2109592,2109593,2109594,2161645,2161646,2161647,2161648,2161649,
2161650,2161651,2161652,2161653,2161654,2161655,2163329,2167705,2167706,2167707,
2167708,2170245,2170246,2170247,2170248,2181266,2181267,2181268)
CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;
Now, if I combine the Original Query and the recursive query - the performance is so bad that I kill the query after 20 minutes:
SELECT CONNECT_BY_ROOT(IXKITL), LEVEL, IXKITL, IXKIT, IXMMCU, IXCPNB/100 SEQ, IXITM, IXLITM, IXQNTY/1000 AS QNTY
FROM TESTDTA.F3002
WHERE IXTBM='M' AND IXMMCU=' 2620001'
START WITH IXKIT IN (SELECT DISTINCT SDITM FROM (SELECT SDITM FROM TESTDTA.F4211, TESTDTA.F3002
WHERE SDITM=IXKIT AND SDMCU=IXMMCU AND SDMCU=' 2620001' AND SDNXTR<'560' AND SDPDDJ<115108 AND SDITM != 0 AND ROWNUM<=10000) )
CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;
Is there another way I should be writing this thing?
If I limit the ROWNUM to 140, the subquery returns three Item Numbers - and the query completes in seconds. If I change the limit to 150, the subquery returns 10 records and the query completes in fifteen minutes. If I cut/paste the ten item numbers into the IN statement, the query returns instantly....
The performance issue makes no sense to me - so I gotta find a better way to write this.
Goal - explode the BOM into a temp file for every open sales order (QUICKLY)... Using ER - the logic takes about an hour for one business unit. The thought was that SQL would do it quicker?
(db)