This discussion is archived
5 Replies Latest reply: Oct 26, 2012 1:07 AM by John Stegeman RSS

recursive subquery

654930 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    654930 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points