Oracle ROWNUM pseudocolumn is a tricky thing and the many people put into the following trap:
SQL> SELECT ename, rownum FROM emp WHERE rownum <= 5 ORDER BY ename;
We have a mess of rownums what is natural behaviour of Oracle (considering when rownum
is assigned to the row and when ORDER BY is executed).
But for ORDER SIBLINGS BY the behavoiur seems differ:
SQL> select ename, rownum from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 /
SQL> SELECT ename, rownum FROM emp WHERE rownum <= 5 ORDER BY ename;
ENAME ROWNUM
------ ------
ALLEN 2
JONES 4
MARTIN 5
SMITH 1
WARD 3
We have a mess of rownums what is natural behaviour of Oracle (considering when rownum
is assigned to the row and when ORDER BY is executed).
But for ORDER SIBLINGS BY the behavoiur seems differ:
SQL> select ename, rownum from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 /
ENAME ROWNUM
------ ------
KING 1
JONES 2
SCOTT 3
ADAMS 4
FORD 5
SMITH 6
BLAKE 7
ALLEN 8
WARD 9
MARTIN 10
TURNER 11
JAMES 12
CLARK 13
MILLER 14
Now we have natural result - hierarchy order corresponds to rownum sequence.
But if we include ORDER SIBLINGS BY ename, we will not get a mess in rownum as
we could expect:
SQL> select ename, rownum from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 order siblings by ename;
ENAME ROWNUM
------- ------
KING 1
BLAKE 2
ALLEN 3
JAMES 4
MARTIN 5
TURNER 6
WARD 7
CLARK 8
MILLER 9
JONES 10
FORD 11
SMITH 12
SCOTT 13
ADAMS 14
It looks like if Oracle retrieves child rows for each parent node, sorts them in accordance
with ORDER SIBLINGS BY clause and after that assignes rownums.
Unfortunately I couldn't find the details of this behaviour in Oracle documentation and Metalink.
No comments:
Post a Comment