5 Replies Latest reply: Oct 26, 2012 3:07 AM by John Stegeman RSS

    recursive subquery

    ricardo.tomas
      I have a table with origin and destination ids.
      There can be a dynamic number of connections ( not expecting more than 5) and the relation is always one to one: A -->B-->C-->D
      A or B or C or D can only apear in a relation, This mean A-->C cannot happen because A e already connected to B.

      I want to create a querie that receives the last destination values and return a column with the origin values that are related with that value:
      for example:
      if my parameter value is D, my result will be a column with three rows : A,B,C
      if my parameter value is C, my result will be a column with two rows : A,B

      example:
      create table test_list as (
      select 32000 origin, 68200 destination from dual
      union all
      select 60000 origin, 168200 destination from dual
      union all
      select 8200 origin, 36600 destination from dual
      union all
      select 36600 origin, 8400 destination from dual
      union all
      select 8400 origin, 61800 destination from dual
      )
      I tried conect by prior and connect by root but could not achieve it!
      found also some articles about "Recursive Subquery Factoring" but that got even worst because I could not get it to work.
      My database is "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production"

      SELECT origin   from test_list  where 
          origin in (select *  FROM (
           SELECT CONNECT_BY_ROOT destination
            FROM test_list
          CONNECT BY PRIOR origin = destination and destination =68200 )
      )
      So in my list:
      32000 connects to 68200
      60000 connects to 168200
      8200 connects to 36600
      36600 connects to 8400
      8400 connects to 61800

      My expected results are:
      If parameter "VALUE_TO_SEARCH" = 68200 I expect only: 32000
      If parameter "VALUE_TO_SEARCH" = 168200 I expect only: 60000
      If parameter "VALUE_TO_SEARCH" = 61800 I expect : 8400,36600,8200
      If parameter "VALUE_TO_SEARCH" = 32000 I expect no results.

      What should be the best method to use, for best performance and in case is with the CONNECT_BY_ROOT and " CONNECT BY PRIOR" what am I doing wrong there?

      Best regards,
      Ricardo Tomás
        • 1. Re: recursive subquery
          Solomon Yakobson
          Use START WITH clause:
          SQL> select * from test_list;
          
              ORIGIN DESTINATION
          ---------- -----------
               32000       68200
               60000      168200
                8200       36600
               36600        8400
                8400       61800
          
          SQL> select  origin
            2    from  test_list
            3    start with destination = &destination
            4    connect by destination = prior origin
            5  /
          Enter value for destination: 68200
          old   3:   start with destination = &destination
          new   3:   start with destination = 68200
          
              ORIGIN
          ----------
               32000
          
          SQL> /
          Enter value for destination: 168200
          old   3:   start with destination = &destination
          new   3:   start with destination = 168200
          
              ORIGIN
          ----------
               60000
          
          SQL> /
          Enter value for destination: 61800
          old   3:   start with destination = &destination
          new   3:   start with destination = 61800
          
              ORIGIN
          ----------
                8400
               36600
                8200
          
          SQL> /
          Enter value for destination: 32000
          old   3:   start with destination = &destination
          new   3:   start with destination = 32000
          
          no rows selected
          
          SQL> 
          SY.
          • 2. Re: recursive subquery
            Frank Kulash
            Hi,

            Here's one way, using CONNECT BY:
            VARIABLE  value_to_search     NUMBER
            EXEC     :value_to_search := 61800;
            EXEC     :value_to_search := 168200;
            -- Only the last one above matters
            
            
            SELECT      origin
            ,      CONNECT_BY_ROOT destination     AS given_destination     -- If wanted
            FROM      test_list
            START WITH     destination = :value_to_search
            CONNECT BY     destination = PRIOR origin
            ;
            • 3. Re: recursive subquery
              Frank Kulash
              Hi,

              Using a recursive WITH clause:
              VARIABLE  value_to_search     NUMBER
              EXEC     :value_to_search := 168200;
              EXEC     :value_to_search := 61800;
              -- Only the last one above matters
              
              
              WITH     tree_results (origin, given_destination)     AS
              (
                   SELECT  origin, destination
                   FROM     test_list
                   WHERE     destination     = :value_to_search
                 UNION ALL
                      SELECT  t.origin
                   ,     r.given_destination
                   FROM     test_list     t
                   JOIN     tree_results  r  ON  r.origin  = t.destination
              )
              SELECT     *
              FROM     tree_results
              ;
              • 4. Re: recursive subquery
                ricardo.tomas
                This seems to be the fastest solution, the explain planis having less cost.
                The first two option have a cost of :ALL_ROWS Cost: 7 Bytes: 52 Cardinality: 2 and this last one is: ALL_ROWS Cost: 5 Bytes: 52 Cardinality: 2

                Thanks,
                Ricardo Tomás
                • 5. Re: recursive subquery
                  John Stegeman
                  the explain plan is having less cost.
                  That is no guarantee whatsoever that the "cheaper" query is faster. In an ideal world, it would be, but it's not. The only way you can tell which is faster is to trace the queries and analyse the tkprof output.