Confusion with Oracle CONNECT BY

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 mSTART with x = 1CONNECT 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 ASSELECT 2 "LEVEL", mytable.X from mytableJOIN 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 ASSELECT 3 "LEVEL", mytable.X from mytableJOIN 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 ASSELECT 4 "LEVEL", mytable.X from mytableJOIN 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 ASSELECT 5 "LEVEL", mytable.X from mytableJOIN 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 step1UNION ALLSELECT * FROM step2UNION ALLSELECT * FROM step3UNION ALLSELECT * FROM step4UNION ALLSELECT * 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 ASSELECT 1 "LEVEL" FROM dual;select * from step1;     LEVEL----------         1 

Step 2

CREATE TABLE step2 ASSELECT 2 "LEVEL" from dualJOIN step1 "PRIOR"ON rownum > 5select * from step2;no rows selected

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

Last step

SELECT * FROM step1UNION ALLSELECT * 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.

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.