2011-01-28

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.

2011-01-27

Partitioned Outer Join

Today was the day I had to fill some sparse data. I actually used partition by right outer join. The documentation example describes the problem well. Nothing much else to say about that.

2011-01-24

GetClientIdentifier

Having a connection pool that connects to a schema with one username. The end users are authenticated to your application. It is possible to get the end user identifier information visible to database level v$session client_identifier column. DBMS_SESSION.SET_IDENTIFIER may be used or with JDBC setEndToEndMetrics method of the oracle.jdbc.OracleConnection.

After setting client identifier information to a session it is possible to set database tracing to a client identifier. But how about your own code, how is it possible to read the client identifier information set in the session? There is no DBMS_SESSION.GET_IDENTIFIER method. One might use select to v$session view but there might be not grants to do so.

Another possibility might be to user DBMS_APPLICATION_INFO.SET_CLIENT_INFO. DBMS_APPLICATION_INFO.READ_CLIENT_INFO is available. Setting client info is not available through JDBC EndToEndMetrics. Client info changes are possible to be set so the client identifier changes also. Set ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE'; This way the tracing by the end user becomes available.

The client identifier is available through SYS_CONTEXT so the GET_IDENTIFIER may be achieved using
select SYS_CONTEXT('userenv', 'CLIENT_IDENTIFIER') from dual;

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.