Oracle, Connect By rownum

Question

I tried to find some informations about connect by "engine". I found this post: Confusion with Oracle CONNECT BY

User krokodilko answered and says:

The analyze of the last query:

select level from dual connect by rownum<10;
I leave to you as a homework assignment.

So i tried to do exactly as described to query

Select rownum from dual connect by rownum < 3

And here's my "work":

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

create table step2 as
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR" on rownum <=3;
SELECT * FROM step2;
create table step3 as
select 3 "LEVEL" from dual
join step2 "PRIOR" on rownum <=3;
SELECT * FROM step3;
create table step4 as
select 4 "LEVEL" from dual
join step3 "PRIOR" on rownum <=3;
SELECT * FROM step4;

But last SELECT still returns rows. Am I misunderstood something? Every time i Select LEVEL + 1 "LEVEL" it has rownum = 1 so it's always true. So am i failed steps?

Upperclassman

Use Recursive CTEs (Common Table Expressions) to walk graphs and hierarchies, instead of [the almost obsolete] CONNECT BY. It's part of the SQL standard, unlike CONNECT BY. – Imbroglio

@TheImpaler - there are a couple of things wrong with your comment. First, hierarchical CONNECT BY queries and recursive CTE queries work differently: CONNECT BY works on individual rows, while recursive CTE works on sets. This has various implications, especially to the definition and behavior of cycles. Second, CONNECT BY is much faster than recursive queries (the cost of recursive queries being much more flexible), so - when a CONNECT BY solution does exist - it may be preferred. Third, CONNECT BY offers CONNECT_BY_ISLEAF; that can be cloned in recursive CTE, but not easily. – Trost

@mathguy I love to be corrected :D I'll read on it. Thanks. – Imbroglio

Answer 1

The explanation in Krokodilko's answer is simply wrong. You may disregard the "Correct Answer" mark and the numerous upvotes, it's still wrong. It is interesting that he left as an exercise exactly the case that proves the explanation is wrong.

A CONNECT BY query doesn't work "as if" new tables (or new output rowsets of SELECT statements, anyway) are generated at each step. This is the mistake in the argument.

Rather, there is only one rowset generated overall (across all steps). It is true that new rows are added based on the rows generated at the previous step; but the rowset itself is one, and growing, not separate rowsets.

This is particularly relevant with regard to ROWNUM. ROWNUM is assigned to rows in a single "result" rowset, starting with 1. In a CONNECT BY query, there is only one rowset, and ROWNUM goes from 1 to n in an increasing sequence.

If Krokodilko's answer were correct, then ROWNUM would restart at 1 at each step. This is clearly not the case: let's try it on a "standard" hierarchical query.

select     empno, ename, mgr, level, rownum
from       scott.emp
start with mgr is null
connect by prior empno = mgr
;

     EMPNO ENAME             MGR      LEVEL     ROWNUM
---------- ---------- ---------- ---------- ----------
      7839 KING                           1          1
      7566 JONES            7839          2          2
      7788 SCOTT            7566          3          3
      7876 ADAMS            7788          4          4
      7902 FORD             7566          3          5
      7369 SMITH            7902          4          6
      7698 BLAKE            7839          2          7
      7499 ALLEN            7698          3          8
      7521 WARD             7698          3          9
      7654 MARTIN           7698          3         10
      7844 TURNER           7698          3         11
      7900 JAMES            7698          3         12
      7782 CLARK            7839          2         13
      7934 MILLER           7782          3         14

Trost