How does Oracle evaluate a hierarchical query with "connect by rownum <= n"

user_5993027 0
Viewed 61
With A as (

   SELECT 'A' ID FROM DUAL

   UNION ALL 

   SELECT 'B' FROM DUAL

   UNION ALL

   SELECT 'C' FROM DUAL
  )

  SELECT ID 
       , LEVEL AS LVL 
       , ROWNUM 
       , SYS_CONNECT_BY_PATH(LEVEL,'->') AS LV_PATH
       , SYS_CONNECT_BY_PATH(ID,'->') AS ID_PATH
    FROM A
  CONNECT BY ROWNUM <= 3

Result
  ID      LVL   ROWNUM     LV_PATH     ID_PATH
   A       1      1         ->1          ->A
   A       2      2         ->1->2       ->A->A
   A       3      3         ->1->2->3    ->A->A->A
   B       1      4         ->1          ->B
   C       1      5         ->1          ->B

[why B and C has 1 level !?]

in this query's result , B and C has only 1 value( level? ) but A has 3 value and level (and ID_PATH result consist only A)

i understand that use connect by level is make hierarchy structure so i know it will make n + n^n column

but i dont know why connect by rownum show only these values ...

please help me T T

oracle
1 Answers

Your hierarchical query connects one row in the hierarchy to another row according to the criterion rownum <= 3.

Firstly, it's important to understand that rownum is a pseudocolumn which is generated when and only if the query decides that a row will be generated; it always starts at 1 for the first record emitted, and only increments as each subsequent record is emitted (that's why you never get results when something like where rownum > 1 is executed).

Secondly, it's important to understand that in Oracle, a hierarchical query is built using a depth-first search.

Your starting rowset is:

1. 'A'
2. 'B'
3. 'C'

The first row generated by the query is:

1. 'A' rownum=1 level=1

The query now goes back and probes the original rowset for any immediate "children" of this row. It finds the first record 'A'. The condition to determine whether this row is a child of our first row is rownum <= 3. Since rownum for the row to be generated is 2, the condition is satisfied; so this row is emitted:

2. 'A' -> 'A' rownum=2 level=2

The query now probes the rowset again for any immediate "children" of this row. It finds the first record 'A'. The condition rownum <= 3 is again checked; since rownum will now be 3, the condition is satisfied; so this row is emitted:

3. 'A' -> 'A' -> 'A' rownum=3 level=3

The query now probes the rowset again for any immediate "children" of this new row. However, rownum is going to be 4 for the new row so it fails to find any "children".

Therefore it goes back to row 2 generated previously and continues searching the rowset for any other children; again, rownum is going to be 4 so it still fails to find any more children. It does the same for row 1 and fails to find any other children.

Finally, it has got back to the root of the tree and it moves forward to the next record in the rowset:

4. 'B' rownum=4 level=1

It does the same thing there, searching the rowset for any children of this record, and fails due to the condition.

It finally gets to the last record in the rowset:

5. 'C' rownum=5 level=1

It also fails to find any children of this record, and the query has finished.

user_1032950
Related