SyntaxEditor Code Snippet
Could you please provide immediate help to re-WRITE the below RECURSIVE QUERY INTO a "while loop" This RECURSIVE QUERY IS pulling 11367034, yes there are dupes (FULL dupes) AND we need the dupes AS well. This has a bad performance. Please HELP..!! :( I tried re-writing IN many ways... BUT the performance still did NOT improve.
--Below is the query which need to be transformed from "recursive query" to "while loop"
INSERT INTO USER_DB.TARGET_TBL ( dev_step ,trvrs_IC ,RNK1 ,RNK2 ,DEPTH ,RT ) WITH RECURSIVE REC_TBL(dev_step,trvrs_ic,r1,r2,depth,rt) AS ( SELECT dev_step ,trvrs_ic ,rnk AS R1 ,rnk AS R2 , 1 AS depth ,dev_step AS RT FROM USER_DB.TBL1 WHERE dev_step IN (SELECT dev_step FROM USER_DB.CHK_DEV_STEP_TBL GROUP BY dev_step) UNION ALL SELECT FROM_TBL.dev_step ,FROM_TBL.trvrs_ic ,FROM_TBL.rnk AS R1 ,REC_TBL.r1 AS R2 ,REC_TBL.depth + 1 ,REC_TBL.rt AS RT FROM USER_DB.TBL1 FROM_TBL , REC_TBL WHERE REC_TBL.trvrs_ic=FROM_TBL.dev_step AND REC_TBL.R1 < FROM_TBL.RNK --AND n.r1 > np1.rnk ) SELECT * FROM REC_TBL;
CREATE MULTISET TABLE USER_DB.TARGET_TBL ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( dev_step VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, trvrs_IC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, RNK1 INTEGER, RNK2 INTEGER, DEPTH INTEGER, RT VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( dev_step ,trvrs_IC ,RNK1 ,RNK2 ,DEPTH ,RT );
I doubt that a loop will have better performance when you actually need duplicate rows (of course, I can't imagine any reason why you would need them).
This is a late reply on the query but I think rewriting a recursive query using a for or a while loop will help. I came across such scenarios. It will work only if a large number of independent rows are accessed at each level. I can help you rewrite it in case there is still a necessity to do it.