You get different results because the two functions are calculated differently!
- Rownum increments for each row in your results
- Level increments for each row of children in the hierarchy.
To process a connect by, Oracle Database:
- Finds all the root rows. This is those matching the start with clause, or all rows if you don't specify this clause.
- It then finds the children of each row. Each child matches the connect by condition for one of the root rows.
So for "connect by rownum", Oracle finds all the rows in the table. It then generates N children for the first one it finds.
With "connect by level", it gets all rows in the table. It then creates N levels of children for each.
The difference is more obvious when you select both level and rownum in the same query:
create table a
( id char
) ;
insert into a values ('a') ;
insert into a values ('b') ;
insert into a values ('c') ;
select id ,
connect_by_root (id),
level l,
rownum r
from a
connect by level <=2;
ID CONNECT_BY_ROOT(ID) L R
a a 1 1
a a 2 2
b a 2 3
c a 2 4
b b 1 5
a b 2 6
b b 2 7
c b 2 8
c c 1 9
a c 2 10
b c 2 11
c c 2 12
select id ,
connect_by_root (id),
level l,
rownum r
from a
connect by rownum <=2;
ID CONNECT_BY_ROOT(ID) L R
a a 1 1
a a 2 2
b b 1 3
c c 1 4
There's no "start with" clause. So all rows are roots in both queries (level = 1). Rownum increases for each output row. The first child of A causes the rownum <= 2 limit to be hit. So you get no extra rows.
With level, all the first children of the root are level = 2. So every row is also a child of every root in your results.