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?

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.

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.