In a CONNECT BY query, the hierarchy is built first, and then WHERE predicate is applied. For example - I start with someone in deptno=20
SQL> select level, empno, mgr, deptno
2 from scott.emp
3 start with empno = 7566
4 connect by prior mgr = empno;
LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ----------
1 7566 7839 20
2 7839 10
Now I add a WHERE predicate
SQL> select level, empno, mgr, deptno
2 from scott.emp
3 where deptno = 10
4 start with empno = 7566
5 connect by prior mgr = empno;
LEVEL EMPNO MGR DEPTNO
---------- ---------- ---------- ----------
2 7839 10
Now if deptno=10 had been applied *first*, then we would have got no rows because my starting row was deptno 20, which doesnt match the WHERE predicate so we would have nothing to walk the hierarchy with. So we can apply that to your cases:
select * from scott.t0909_1 where id=1 connect by rownum<=3;
- start with the first row we find *based on the hierarchy rule* which is "hey, match anything"
- it matches the where clause, so rownum gets bumped up to 1
- our hierarchy has no "real" condition so we loop around again
- we get the first row (id=1)
- it matches the where clause, so rownum gets bumped up to 2
and so on, and rownum will hit 3 and we're done.
Now look at this one:
select * from scott.t0909_1 where id=2 connect by rownum<=3;
- start with the first row we find *based on the hierarchy rule* which is "hey, match anything"
- it doesn't match the where clause, so rownum stays zero
- our hierarchy has no "real" condition so we loop around again
- we get the first row (id=1)
- it doesn't match the where clause, so rownum stays zero
- our hierarchy has no "real" condition so we loop around again
- we get the first row (id=1)
- it doesn't match the where clause, so rownum stays zero
- our hierarchy has no "real" condition so we loop around again
- we get the first row (id=1)
etc
etc
etc
eventually we get so deep in the hierarchy we run out of memory