CODEWITHSUNDEEP
sqloracle-databaserecursioncycle
user
user

Reputation: 167

oracle recursive avoid cycle

following query was developed on https://www.tutorialspoint.com/execute_sql_online.php and works as designed: 

WITH helptab1 
     AS (SELECT "a"    AS cust, 
                1      AS numb, 
                "m006" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "b"    AS cust, 
                1      AS numb, 
                "m006" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "b"    AS cust, 
                1      AS numb, 
                "m777" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "b"    AS cust, 
                2      AS numb, 
                "m018" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "c"    AS cust, 
                2      AS numb, 
                "m454" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "c"    AS cust, 
                5      AS numb, 
                "m008" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "c"    AS cust, 
                5      AS numb, 
                "m090" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "c"    AS cust, 
                6      AS numb, 
                "m789" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "c"    AS cust, 
                7      AS numb, 
                "m191" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "d"    AS cust, 
                9      AS numb, 
                "m006" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "d"    AS cust, 
                1      AS numb, 
                "m123" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "e"    AS cust, 
                3      AS numb, 
                "m567" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "f"    AS cust, 
                3      AS numb, 
                "m777" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "g"    AS cust, 
                3      AS numb, 
                "m888" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "g"    AS cust, 
                3      AS numb, 
                "m765" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "g"    AS cust, 
                4      AS numb, 
                "m543" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "h"    AS cust, 
                6      AS numb, 
                "m888" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "h"    AS cust, 
                6      AS numb, 
                "m090" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "h"    AS cust, 
                6      AS numb, 
                "m001" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "h"    AS cust, 
                7      AS numb, 
                "m008" AS val 
         FROM   dual 
         UNION ALL 
         SELECT "h"    AS cust, 
                7      AS numb, 
                "m090" AS val 
         FROM   dual), 
     helptab2 
     AS (SELECT /*+materialize */ DISTINCT cust, 
                                           numb, 
                                           val, 
                                           1 AS counter 
         FROM   helptab1 
         WHERE  val = "m765"), 
     basic (cust, numb, val, counter) 
     AS (SELECT DISTINCT cust, 
                         numb, 
                         val, 
                         1 
         FROM   helptab2 
         UNION ALL 
         SELECT DISTINCT hlp1.cust, 
                         hlp1.numb, 
                         hlp1.val, 
                         counter + 1 
         FROM   basic bas 
                join helptab1 hlp1 
                  ON ( hlp1.cust = bas.cust 
                       AND hlp1.numb = bas.numb ) 
                      OR ( hlp1.val = bas.val ) 
         WHERE  counter <= 10), 
     helptab3 
     AS (SELECT DISTINCT cust, 
                         numb, 
                         val, 
                         counter 
         FROM   basic 
         ORDER  BY cust, 
                   numb, 
                   val) 
SELECT DISTINCT cust, 
                numb, 
                val 
FROM   helptab3;

but there are two problems, the part "where counter <= 10" isn't really smart AND it can't be executed on Oracle (12g) Developer. what needs to be done that it's running on Oracle? and how to handle the recursive part, so there is no limit and no cycle?

Upvotes: 0

Views: 1260

Answers (1)

trincot
trincot

Reputation: 350310

Some issues in your query:

  • Literal strings need to be delimited by single quotes, not double quotes. So for example: 'm765' instead of "m765". The latter is interpreted as a column reference.
  • distinct is not allowed in recursive (second) part of a recursive query. But as you still apply a distinct in the next with query, you can live without it, but with a tremendous cost in performance. NB: In the first part of the query distinct is not needed, as you already did that in helptab2
  • For the counter <= 10 to work in a recursive query, it really needs to be a join condition, not a condition in the where clause.

With those changes applied, the query works but obviously becomes very slow as the recursive part will collect many of the same records and recurses into cycles.

Cycles can be prevented with the cycle clause following the recursive with query:

with helptab1 as ( 
        select 'A' as cust, 1 as numb, 'm006' as val from dual union all 
        select 'B' as cust, 1 as numb, 'm006' as val from dual union all 
        select 'B' as cust, 1 as numb, 'm777' as val from dual union all 
        select 'B' as cust, 2 as numb, 'm018' as val from dual union all 
        select 'C' as cust, 2 as numb, 'm454' as val from dual union all
        select 'C' as cust, 5 as numb, 'm008' as val from dual union all
        select 'C' as cust, 5 as numb, 'm090' as val from dual union all
        select 'C' as cust, 6 as numb, 'm789' as val from dual union all
        select 'C' as cust, 7 as numb, 'm191' as val from dual union all
        select 'D' as cust, 9 as numb, 'm006' as val from dual union all
        select 'D' as cust, 1 as numb, 'm123' as val from dual union all
        select 'E' as cust, 3 as numb, 'm567' as val from dual union all
        select 'F' as cust, 3 as numb, 'm777' as val from dual union all
        select 'G' as cust, 3 as numb, 'm888' as val from dual union all
        select 'G' as cust, 3 as numb, 'm765' as val from dual union all
        select 'G' as cust, 4 as numb, 'm543' as val from dual union all
        select 'H' as cust, 6 as numb, 'm888' as val from dual union all
        select 'H' as cust, 6 as numb, 'm090' as val from dual union all
        select 'H' as cust, 6 as numb, 'm001' as val from dual union all
        select 'H' as cust, 7 as numb, 'm008' as val from dual union all
        select 'H' as cust, 7 as numb, 'm090' as val from dual), 
    basic (cust,numb,val) as (
        select distinct cust, numb, val
        from   helptab1
        where  val = 'm765' 
        union all
        select hlp1.cust, hlp1.numb, hlp1.val
        from   basic bas 
        join   helptab1 hlp1 
                on hlp1.cust = bas.cust and hlp1.numb = bas.numb
                or hlp1.val = bas.val)
        cycle cust, numb, val set cycle to 1 default 0
    select   distinct cust, numb, val
    from     basic;

This can also be done with the alternative connect by syntax for hierarchical queries, which has the nocycles option:

with helptab1 as ( 
        select 'A' as cust, 1 as numb, 'm006' as val from dual union all 
        select 'B' as cust, 1 as numb, 'm006' as val from dual union all 
        select 'B' as cust, 1 as numb, 'm777' as val from dual union all 
        select 'B' as cust, 2 as numb, 'm018' as val from dual union all 
        select 'C' as cust, 2 as numb, 'm454' as val from dual union all
        select 'C' as cust, 5 as numb, 'm008' as val from dual union all
        select 'C' as cust, 5 as numb, 'm090' as val from dual union all
        select 'C' as cust, 6 as numb, 'm789' as val from dual union all
        select 'C' as cust, 7 as numb, 'm191' as val from dual union all
        select 'D' as cust, 9 as numb, 'm006' as val from dual union all
        select 'D' as cust, 1 as numb, 'm123' as val from dual union all
        select 'E' as cust, 3 as numb, 'm567' as val from dual union all
        select 'F' as cust, 3 as numb, 'm777' as val from dual union all
        select 'G' as cust, 3 as numb, 'm888' as val from dual union all
        select 'G' as cust, 3 as numb, 'm765' as val from dual union all
        select 'G' as cust, 4 as numb, 'm543' as val from dual union all
        select 'H' as cust, 6 as numb, 'm888' as val from dual union all
        select 'H' as cust, 6 as numb, 'm090' as val from dual union all
        select 'H' as cust, 6 as numb, 'm001' as val from dual union all
        select 'H' as cust, 7 as numb, 'm008' as val from dual union all
        select 'H' as cust, 7 as numb, 'm090' as val from dual), 
    basic as (
        select     cust, numb, val
        from       helptab1
        connect by nocycle
                   prior cust = cust and prior numb = numb
                or prior val = val
        start with val = 'm765')
    select   distinct cust, numb, val
    from     basic;

Upvotes: 1

Related Questions