1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Connect by prior - working

Discussion in 'SQL PL/SQL' started by mukulverma2408, Aug 20, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    Can anyone help me to explain, how does connect by prior works in finding the hierarchical structure?

    I have gone through some tutorials but no luck.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,344
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think if you understand the lag() function you'll begin to understand 'connect by prior'. Let's look at an example:

    Code (SQL):
    SQL> WITH ord_recs AS(
      2          SELECT title, nr, parent
      3          FROM contents
      4          ORDER BY title
      5  )
      6  SELECT lpad(' ', level -1)||title||'('||nr||')'
      7  FROM ord_recs
      8  CONNECT BY parent = prior nr
      9  START WITH nr = 55580
    10  /

    LPAD('',LEVEL-1)||TITLE||'('||NR||')'
    --------------------------------------------------------------------------------------------------------------------------------
    Home(55580)
    FIRST(55581)
      FIRST - 1(55585)
      FIRST - 3(55586)
      FIRST - 2(55587)
       FIRST - 2 - 1(55591)
    SECOND(55582)
      SECOND - 1(55588)
    Third(55583)
      Third - 1(55589)
       Third - 1 - 1(55592)
      Third - 2(55590)
    Fourth(55584)

    13 ROWS selected.


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 4012745273

    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |   182 | 28392 |    22  (10)| 00:00:01 |
    |   1 |  TEMP TABLE TRANSFORMATION               |                           |       |       |         |             |
    |   2 |   LOAD AS SELECT                         | SYS_TEMP_0FD9D6612_4554AE |       |       |         |             |
    |   3 |    SORT ORDER BY                         |                           |    13 |  2028 |    19   (6)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL                    | CONTENTS                  |    13 |  2028 |    18   (0)| 00:00:01 |
    |*  5 |   CONNECT BY NO FILTERING WITH START-WITH|                           |       |       |         |             |
    |   6 |    VIEW                                  |                           |    13 |  2028 |     2   (0)| 00:00:01 |
    |   7 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6612_4554AE |    13 |  2028 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       5 - access("PARENT"=PRIOR "NR")
           FILTER("NR"=55580)

    Note
    -----
       - dynamic sampling used FOR this statement (level=2)


    Statistics
    ----------------------------------------------------------
             16  recursive calls
             10  db block gets
             82  consistent gets
              1  physical reads
            884  redo SIZE
            891  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              3  sorts (memory)
              0  sorts (disk)
             13  ROWS processed

    SQL>
    Look at what Oracle is doing -- it's loading a temporary table with sorted data then using that ordered data to build the hierarchical output. The "connect by ... prior" uses the ordered results to obtain the 'prior' value, like the lag() function would do.

    If we delete the data and load a different data set and run the same query we see:

    Code (SQL):
    SQL> WITH ord_recs AS(
      2          SELECT title, nr, parent
      3          FROM contents
      4          ORDER BY title
      5  )
      6  SELECT lpad(' ', level -1)||title||'('||nr||')'
      7  FROM ord_recs
      8  CONNECT BY parent = prior nr
      9  START WITH nr = 55580
    10  /

    LPAD('',LEVEL-1)||TITLE||'('||NR||')'
    ---------------------------------------------------------------------------------------------------------------------------------
    Home(55580)
    FIRST(55581)
      FIRST - 1(55585)
      FIRST - 3(55586)
      FIRST - 2(55587)
       FIRST - 2 - 1(55591)
        Sixth(55594)
    SECOND(55582)
      SECOND - 1(55588)
    Third(55583)
      Third - 1(55589)
       Third - 1 - 1(55592)
      Third - 2(55590)
       Fifth(55593)
    Fourth(55584)

    15 ROWS selected.


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 4012745273

    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |   240 | 37440 |    22  (10)| 00:00:01 |
    |   1 |  TEMP TABLE TRANSFORMATION               |                           |       |       |         |             |
    |   2 |   LOAD AS SELECT                         | SYS_TEMP_0FD9D6613_4554AE |       |       |         |             |
    |   3 |    SORT ORDER BY                         |                           |    15 |  2340 |    19   (6)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL                    | CONTENTS                  |    15 |  2340 |    18   (0)| 00:00:01 |
    |*  5 |   CONNECT BY NO FILTERING WITH START-WITH|                           |       |       |         |             |
    |   6 |    VIEW                                  |                           |    15 |  2340 |     2   (0)| 00:00:01 |
    |   7 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6613_4554AE |    15 |  2340 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       5 - access("PARENT"=PRIOR "NR")
           FILTER("NR"=55580)

    Note
    -----
       - dynamic sampling used FOR this statement (level=2)


    Statistics
    ----------------------------------------------------------
              2  recursive calls
             10  db block gets
             26  consistent gets
              1  physical reads
            884  redo SIZE
            934  bytes sent via SQL*Net TO client
            519  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              3  sorts (memory)
              0  sorts (disk)
             15  ROWS processed

    SQL>
    Notice that it's the same plan as before. For any "prior" operation to work the data needs to be sorted on the reference value, in this case the NR column. Ordering the data makes it possible to generate a hierarchical 'tree' that displays the relationship between the parent records with respect to the NR column. Notice that when a new branch of the tree appears the ordering begins again starting with the NR value for the new level 0 branch (these are the lines that start at the 'margin', the left-most character position). All child rows are indented according to the level (0 == parent, 1==child, 2==child of child, 3== child of child of child ...).

    There are practical uses for such queries, for example constructing a manufacturing Bill Of Materials (where level 0 is the main assembly, level 1 are the sub-assemblies and levels below that are parts for each sub-assembly). For example:

    Code (SQL):
    Bicycle

        Wheel

            Rim

            Spoke

            Tube

            Tire

        Frame

    ...
    I think you get the idea.
     
    Last edited: Aug 21, 2015
    mukulverma2408 likes this.