Confusion with Oracle CONNECT BY

Question

The query:

select level from dual connect by rownum<10;

gives numbers from 1 to 9.

Another query:

SELECT LEVEL FROM DUAL CONNECT BY rownum>5;

outputs: 1

I have used CONNECT BY in hierarchical data like manager/employee. But I fail to interpret the results of the above two queries.

Edit: I am not trying to achieve anything special with query#2. I just want to know how oracle interprets the query. Does any part act as parent and child due to the use of CONNECT BY? Why is the result 1? What is happening behind the scenes?

Revetment

Answer 1

How a CONNECT BY query is executed and evaluated - step by step (by example).

Say we have the following table and a connect by query:

select * from mytable;

         X
----------
         1 
         2 
         3 
         4 

SELECT level, m.* 
FROM mytable m
START with x = 1
CONNECT BY PRIOR x +1 = x  OR  PRIOR x + 2 = x 
ORDER BY level;

Step 1:

Select rows from table mytable that meet a START WITH condition, assign LEVEL = 1 to the returned result set:

 CREATE TABLE step1 AS
 SELECT 1 "LEVEL", X from mytable
 WHERE x = 1;

 SELECT * FROM step1;

         LEVEL          X
    ---------- ----------
             1          1

Step 2

Increase level by 1:

LEVEL = LEVEL + 1

Join the result set returned in previous step with mytable using CONNECT BY conditions as the join conditions.

In this clause PRIOR column-name refers to the resultset returned by previous step, and simple column-name refers to the mytable table:

CREATE TABLE step2 AS
SELECT 2 "LEVEL", mytable.X from mytable
JOIN step1 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step2;

     LEVEL          X
---------- ----------
         2          2 
         2          3

STEP x+1

Repeat #2 until last operation returns an empty result set.

Step 3

CREATE TABLE step3 AS
SELECT 3 "LEVEL", mytable.X from mytable
JOIN step2 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step3;

     LEVEL          X
---------- ----------
         3          3 
         3          4 
         3          4

Step 4

CREATE TABLE step4 AS
SELECT 4 "LEVEL", mytable.X from mytable
JOIN step3 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step4;

     LEVEL          X
---------- ----------
         4          4 

Step 5

CREATE TABLE step5 AS
SELECT 5 "LEVEL", mytable.X from mytable
JOIN step4 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step5;

no rows selected

Step 5 returned no rows, so now we finalize the query

Last step

UNION ALL results of all steps and return it as the final result:

SELECT * FROM step1
UNION ALL
SELECT * FROM step2
UNION ALL
SELECT * FROM step3
UNION ALL
SELECT * FROM step4
UNION ALL

SELECT * FROM step5;

     LEVEL          X
---------- ----------
         1          1 
         2          2 
         2          3 
         3          3 
         3          4 
         3          4 
         4          4 

Now let's apply the above procedure to your query:

SELECT * FROM dual;

DUMMY
-----
X 

SELECT LEVEL FROM DUAL CONNECT BY rownum>5;

Step 1

Since the query does not contain the START WITH clause, Oracle selects all records from the source table:

CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;

select * from step1;

     LEVEL
----------
         1 

Step 2

CREATE TABLE step2 AS
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR"
ON rownum > 5

select * from step2;

no rows selected

Since the last step returned no rows, we are going to finalize our query.

Last step

SELECT * FROM step1
UNION ALL

SELECT * FROM step2;

     LEVEL
----------
         1

The analyze of the last query:

select level from dual connect by rownum<10;

I leave to you as a homework assignment.

Rohrer

+1 Thanks for the detailed explanation. Your explanation is far better than any documentation available in oracle's site. It is crystal clear to me now :-) – Revetment

While this answer is correct for the most part, it is wrong specifically with regard to how ROWNUM is assigned in a CONNECT BY query. In a CONNECT BY query the result set is unique, and ROWNUM is assigned continuously from LEVEL = 1 to the higher levels; it does not restart at 1 for each level. Details in a later thread, where someone actually tried to do the so-called "homework assignment" left at the end of this Answer. https://mcmap.net/q/1322533/-oracle-connect-by-rownum/… – Mitch

Answer 2

This is nothing to do with CONNECT BY but an artifact of your misuse of ROWNUM.

To quote from the documentation:

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

The ROWNUM is a factor of the resultset rather than the query. Though these are linked they are not quite the same; it is not possible for the 6^th result to exist if the first does not.

This is also explained in the documentation:

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
  FROM employees
  WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Signesignet

I know that rownum is concerned with resultset. Here, using level instead of rownum gives the same results. What I do not understand is WHY the result of second query is 1. – Revetment

I am not trying to achieve anything with the 2nd query. I just fail to understand the "parent" and "child" in the query. How does oracle interpret the query? – Revetment

The results of the second query is 1 for the reasons quoted from the documentation in my answer. In the query the "parent" is the previous row as you're selecting from a one row table. The child is always the current row. – Signesignet

What are "previous" and "current" rows? I believe select level from dual only gives one row? – Revetment

It does @Robik but the CONNECT BY is a hierarchical query; as you're selecting from DUAL you have an initial child the connect by then creates a child on this. The previous row is in the first instance dual and the current is level + 1., etc. – Signesignet

Thanks for the help. kordirko's explanation made it crystal clear to me about how oracle executes CONNECT BY behind the scenes – Revetment