SQL Side Note on Recursion Versus Analytics (IT Toolbox Blogs)



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[1] = 'SELECT count(*) cnt -- comment type#1'
, txt[2] = ' FROM dual /* comment type #2 */ table_alias'
, txt[3] = ' WHERE 1 = 1 /* start multiline comment'
, txt[4] = ' ... other comments here ...'
, txt[5] = ' ... and more here ...'
, txt[6] = ' ... end of comment */ AND 2 = 2'
, txt[7] = ' UNION ALL '
, txt[8] = 'SELECT COUNT(1) ct'
, txt[9] = ' FROM dual /* comment type #2 */ table_alias'
, txt[10] = ' WHERE 1 = 1 /* start multiline comment'
, txt[11] = ' ... other comments here ...'
, txt[12] = ' ... and more here ...'
, txt[13] = ' ... end of comment */ AND 2 = 2'
, txt[14] = ';'
) 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.

 

 


 

 

After all you can use recursion or looping for the Fibonacci series. Certainly, I almost used recursion when looking at the Speed Phreak queries, but again decided on a “pure analytics” solution.

 

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.

 

<END>

 

The wikipedia image is a tree created using the Logo programming language and relying heavily on recursion. The image is by Brentsmith101 – Own work, Public Domain.

 
Oracle 12c
 
comments
 
/*
 

 
removal
 
analytic function
 
recursive SQL

Source: SANS ISC SecNewsFeed @ May 2, 2017 at 12:09PM

0
Share