ORDER SIBLINGS BY CONNECT_BY_ROOT

In this post I am dealing with a sorting problem of a recursive query and giving a guideline how to implement such ordering.

We are patching an Oracle database to 11.2.0.2 and with one of our test case hit an error

ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition

The problem query does not have connect_by_root in START WITH or CONNECT BY. But it is in order by "order siblings by connect_by_root". So the reported error is somewhat misleading.

What does this order siblings by connect_by_root is trying to accomplish. The hierarchical result is ordered first by some column from a root node of the hierarchy and after that with some columns at the same level of the hierarchy.

In a thread in www.sql.ru there may be found discussion about the same problem. With 10.2.0 ORA-00600: internal error code, arguments: [qkacon:FJswrwo] is reported. It is mentioned that giving a hint /*+ NO_CONNECT_BY_COST_BASED */ bypasses the ORA-00600 problem, but a new one is described. connect_by_root is returning nulls if the same query has siblings word in order by. So our query has problem and the newly introduced error in 11.2.0.2 is actually revealing that to us.

11.2 introduced an alternative way to write hierarchical queries. Here I introduce the problematic queries with a data set having two roots. And in the end a way to implement the requirement using recursive common table expression.

drop table emp purge;

CREATE TABLE EMP
(
EMPNO     NUMBER(4),
ENAME     VARCHAR2(10 BYTE),
MGR       NUMBER(4)
)
;

insert into emp(empno,mgr,ename) values (11,23,'SMITH');
insert into emp(empno,mgr,ename) values (12,16,'ALLEN');
insert into emp(empno,mgr,ename) values (13,16,'WARD');
insert into emp(empno,mgr,ename) values (14,19,'JONES');
insert into emp(empno,mgr,ename) values (15,16,'MARTIN');
insert into emp(empno,mgr,ename) values (16,19,'BLAKE');
insert into emp(empno,mgr,ename) values (17,19,'CLARK');
insert into emp(empno,mgr,ename) values (18,null,'SCOTT');
insert into emp(empno,mgr,ename) values (19,null,'KING');
insert into emp(empno,mgr,ename) values (20,16,'TURNER');
insert into emp(empno,mgr,ename) values (21,18,'ADAMS');
insert into emp(empno,mgr,ename) values (22,16,'JAMES');
insert into emp(empno,mgr,ename) values (23,14,'FORD');
insert into emp(empno,mgr,ename) values (24,17,'MILLER');

update emp set mgr = null where ename = 'SCOTT';

commit;


select em.*, rpad('-',level,'-')||empno , level
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
;
18 SCOTT  -18 1
21 ADAMS 18 --21 2
19 KING  -19 1
14 JONES 19 --14 2
23 FORD 14 ---23 3
11 SMITH 23 ----11 4
16 BLAKE 19 --16 2
12 ALLEN 16 ---12 3
13 WARD 16 ---13 3
15 MARTIN 16 ---15 3
20 TURNER 16 ---20 3
22 JAMES 16 ---22 3
17 CLARK 19 --17 2
24 MILLER 17 ---24 3

Trying to add the described ordering:

select /*+ NO_CONNECT_BY_COST_BASED */em.*, level, connect_by_root ename cbr, rpad('-',level,'-')||empno
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
order siblings by connect_by_root ename, empno
;

18 SCOTT  1 SCOTT -18
21 ADAMS 18 2 SCOTT --21
19 KING  1 KING -19
14 JONES 19 2 KING --14
23 FORD 14 3 KING ---23
11 SMITH 23 4 KING ----11
16 BLAKE 19 2 KING --16
12 ALLEN 16 3 KING ---12
13 WARD 16 3 KING ---13
15 MARTIN 16 3 KING ---15
20 TURNER 16 3 KING ---20
22 JAMES 16 3 KING ---22
17 CLARK 19 2 KING --17
24 MILLER 17 3 KING ---24

Rows from KING root should be ordered before SCOTT. So using the 11.2.0.2 database the problem is noticed and the ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition is thrown.

How to bypass the problem with a recursive common table query:



with cte (empno,mgr,ename,cbr,l) as (
select empno,mgr,ename,ename cbr,1 from emp where mgr is null
union all
select em.empno,em.mgr,em.ename,ct.cbr,ct.l+1 from emp em inner join cte ct on em.mgr = ct.empno
)
SEARCH DEPTH FIRST BY cbr,empno SET rn
select te.*, rpad('-',l,'-')||empno
from cte te
;

19 KING KING 1 1 -19
14 19 JONES KING 2 2 --14
23 14 FORD KING 3 3 ---23
11 23 SMITH KING 4 4 ----11
16 19 BLAKE KING 2 5 --16
12 16 ALLEN KING 3 6 ---12
13 16 WARD KING 3 7 ---13
15 16 MARTIN KING 3 8 ---15
20 16 TURNER KING 3 9 ---20
22 16 JAMES KING 3 10 ---22
17 19 CLARK KING 2 11 --17
24 17 MILLER KING 3 12 ---24
18 SCOTT SCOTT 1 13 -18
21 18 ADAMS SCOTT 2 14 --21


Problem solved, nice feeling. Also a good taste in my mouth. Thanks to Ilkka and H. and The Yamazaki Single Malt Whisky aged 12 years Japanese whisky. Now to have some cake and buy tickets to Hakametsä Tappara ice hockey game.