"Recursive query" to "while loop"

Database
Enthusiast

"Recursive query" to "while loop"

Hi

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 );
2 REPLIES
Junior Contributor

Re: "Recursive query" to "while loop"

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).

 

Junior Supporter

Re: "Recursive query" to "while loop"

Hi Sherin,

 

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.

 

Thanks,

Rohan Sawant