Oracle "connect by" - multiple hierarchies

Question

We're on Oracle 11g R1. Here's the code:

CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
INSERT INTO T1 VALUES (1,NULL,NULL);
INSERT INTO T1 VALUES (3,1,NULL);
INSERT INTO T1 VALUES (2,1,3);
INSERT INTO T1 VALUES (4,2,NULL);
INSERT INTO T1 VALUES (5,2,4);
INSERT INTO T1 VALUES (10,NULL,1);
INSERT INTO T1 VALUES (12,10,NULL);
INSERT INTO T1 VALUES (11,10,12);

What I would like the result to be is:

The key here is that besides the usual PRIOR ID = PARENT_ID hierarchy, there is another hierarchy based on PRIOR ID = LEFT_SIBLING_ID. The children are sorted in the order of PRIOR ID = LEFT_SIBLING_ID. That is why the 3 is followed by 2, and the 12 by 11, etc. That order is important.

I've drawn a blank on how to do this.

Edit:

More rows to clearly illustrate the ordering problem:

CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
INSERT INTO T1 VALUES (1,NULL,10);
INSERT INTO T1 VALUES (3,1,NULL);
INSERT INTO T1 VALUES (2,1,3);
INSERT INTO T1 VALUES (4,2,NULL);
INSERT INTO T1 VALUES (5,2,4);
INSERT INTO T1 VALUES (10,NULL,NULL);
INSERT INTO T1 VALUES (12,10,NULL);
INSERT INTO T1 VALUES (7,10,12);
INSERT INTO T1 VALUES (11,10,7);
INSERT INTO T1 VALUES (6,1,2);
INSERT INTO T1 VALUES (13,1,6);
COMMIT;

The result of the query:

select substr('----------', 1, lvl*2-2) || to_char(id) id_tree
from
 (select SYS_CONNECT_BY_PATH(to_char(id,'009'), ':') sort_path, 
        left_sibling_id, id, parent_id, level lvl
 from t1 
 start with parent_id is null
 connect by prior id = parent_id) q
start with left_sibling_id is null
connect by prior id = left_sibling_id 
and coalesce(parent_id,id) = coalesce(parent_id,id)
order by case lvl when 1 then sort_path 
                  else substr(sort_path,1,length(sort_path)-4) end,
         level;

ID_TREE                                            
-------------------------------------------------- 
1                                                  
--3                                                
--2                                                
--6                                                
--13                                               
----4                                              
----5                                              
10                                                 
--12                                               
--7                                                
--11                                               

 11 rows selected

Though the siblings are ordered properly (except the top level), they are no longer immediately under their parent.

There are other ways to order siblings. Do you have to specifically use this one? — Branko Dimitrijevic, Jul 10 '13 at 11:44
You could use a "sort_order" column and then use `ORDER SIBLINGS BY sort_order` instead of the reference to the "left sibling". — , Jul 10 '13 at 11:50
I can't change the table, so I have to specifically use this one. The reason this way was chosen way back when was that it was thought to speed up the addition of new ids. In this implementation, only one insert (the new id) and one update (changing the LEFT_SIBLING_ID of one existing record) is needed. If there were a "sort_order" column, all siblings after the new one would need their "sort_order" column updated. That was the idea then, at least. — SKY, Jul 10 '13 at 12:02
Does it specifically have to be a `connect by`, or can you use a recursive CTE (which should be available in Oracle 11g) ? — , Jul 10 '13 at 12:19
Hmmm. I'm trying to see if I can easily generate a "sort_order" column on the fly. — SKY, Jul 10 '13 at 12:44

SKY · Answer 1 · 2013-07-10T19:36:07.613

Finally!

I created a 'sort order' column called SIBLING_LEVEL below by doing a CONNECT BY on LEFT_SIBLING_ID. Then I joined that to the original table. Then on the result of that join did a straightforward CONNECT BY ORDER SIBLING BY. Seems a bit brute force to me, but it's all I could come up with.

SELECT ROWNUM, LPAD(' ', (LEVEL*2) - 1, '-') || ID AS HIERARCHY, PARENT_ID,
LEFT_SIBLING_ID, LEVEL AS PARENT_CHILD_LEVEL
from
(
SELECT A.ID, A.PARENT_ID, A.LEFT_SIBLING_ID, B.SIBLING_LEVEL
FROM
T1 A
,
(
SELECT ID, SUBSTR('----------', 1, LVL*2-2) || TO_CHAR(ID) ID_TREE,
LEVEL AS SIBLING_LEVEL
FROM
(SELECT SYS_CONNECT_BY_PATH(TO_CHAR(ID,'009'), ':') SORT_PATH, 
    LEFT_SIBLING_ID, ID, PARENT_ID, LEVEL LVL
 FROM T1 
 START WITH PARENT_ID IS NULL
 CONNECT BY PRIOR ID = PARENT_ID) Q
START WITH LEFT_SIBLING_ID IS NULL
CONNECT BY PRIOR ID = LEFT_SIBLING_ID) B
WHERE A.ID = B.ID
) C
START WITH C.PARENT_ID IS NULL
CONNECT BY PRIOR C.ID = C.PARENT_ID
ORDER SIBLINGS BY SIBLING_LEVEL;

http://sqlfiddle.com/#!4/fcd68/5/0

Oracle "connect by" - multiple hierarchies

1 Answers1