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