While tackling some problems recently, I’ve been mulling around the question: when must I use recursive SQL (or similar MODEL clause) because analytic functions simply won’t solve the problem?
My mulling was instigated by my correction to my post “Oracle 12c: Removing Multi-line Comments the Analytic Function Way” (following up on the comment by Iudith Mentzel). My correction used a recursive subquery to ‘connect’ the starting line# and finishing line# of a /*…*/ style comment.
And yet… and yet…
I knew that recursion wasn’t necessary. The obvious means of solving the issue, sure.
Yet I could come up with an alternative query that produces the same output, but isn’t recursive. My non-recursive query is:
WITH sample_SQL ( line#, txt ) AS ( SELECT line#, txt FROM dual MODEL DIMENSION BY (1 as line#) MEASURES (cast(null as varchar2(50)) as txt) RULES ( txt = 'SELECT count(*) cnt -- comment type#1' , txt = ' FROM dual /* comment type #2 */ table_alias' , txt = ' WHERE 1 = 1 /* start multiline comment' , txt = ' ... other comments here ...' , txt = ' ... and more here ...' , txt = ' ... end of comment */ AND 2 = 2' , txt = ' UNION ALL ' , txt = 'SELECT COUNT(1) ct' , txt = ' FROM dual /* comment type #2 */ table_alias' , txt = ' WHERE 1 = 1 /* start multiline comment' , txt = ' ... other comments here ...' , txt = ' ... and more here ...' , txt = ' ... end of comment */ AND 2 = 2' , txt = ';' ) ORDER BY line# ), preprep AS ( SELECT line# , CASE WHEN txt like '%--%' THEN SUBSTR(txt,1,INSTR(txt,'--')-1) ELSE txt END as txt FROM sample_SQL ), prep AS ( SELECT line#, txt , case when txt like '%/*%' then line# end as startline# , case when txt like '%/*%' then INSTR(txt,'/*') end as start#pos , case when txt like '%*/%' then line# end AS endline# , case when txt like '%*/%' then INSTR(txt,'*/') end as end#pos , max(case when txt like '%/*%' then line# end) over(order by line#) as upstart#2 , min(case when txt like '%*/%' then line# end) over(order by line# desc) as downend#2 FROM preprep ), munch AS ( SELECT p.* , case when startline# = line# then substr(txt,1,start#pos-1) end ||case when endline# = line# then substr(txt,end#pos+2,999) end ||case when line# between upstart#2 and min(downend#2) OVER (partition by upstart#2) then null else txt end as txt2 FROM prep p ) SELECT line# as orig# , row_number()over(order by line#) as line# , txt2 FROM munch m WHERE txt2 IS NOT NULL ORDER BY line#; ORIG# LINE# TXT2 ----- ---------- ------------------------ 1 1 SELECT count(*) cnt 2 2 FROM dual table_alias 3 3 WHERE 1 = 1 6 4 AND 2 = 2 7 5 UNION ALL 8 6 SELECT COUNT(1) ct 9 7 FROM dual table_alias 10 8 WHERE 1 = 1 13 9 AND 2 = 2 14 10 ;
I bolded (above) the additional analytics I used. I also modified the final CASE statement in the “munch” subquery since I added an analytic function there as well.
There’s probably some fundamental theorem of recursive algorithms that explains it: wikipedia says “Recursion and iteration are equally expressive: recursion can be replaced by iteration with an explicit stack, while iteration can be replaced with tail recursion.” I just don’t immediately grasp how that statement operationalizes into converting recursive SQL into non-recursive SQL and vice versa.
Source: SANS ISC SecNewsFeed @ May 2, 2017 at 12:09PM