Start With "IN" Connect Prior - Performance is Awful
I'm new here - so assist me if I've posted in the wrong area... please.
The following two queries should provide exactly the same results.
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 ) )
CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;
SELECT CONNECT_BY_ROOT(IXKITL) SO_ITEM, LEVEL, IXKITL, IXKIT, IXMMCU, IXCPNB/100 SEQ, IXITM, IXLITM, IXQNTY/1000 AS QNTY