Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Robert .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: June 10, 2004 - 4:48 pm UTC

Version:

Viewed 1000+ times

You Asked

Tom,

How do you use Oracle's "connect by" clause and order the
resulting output in alphabetical order without losing the
hierarchical structure?

The more I look at this, the more perplexing it becomes. Please HELP!



and Tom said...



This is tricky since the Order by happens AFTER the CONNECT BY and hence will destroy the result sets 'hierarchy'. You typically never use connect by AND order by.

One solution that always works (in Oracle8i and up) is demonstrated at </code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>in the section on "Writing to the database state in a function called from SQL". It does not use a connect by but achieves the same hiearchy and is guaranteed to be ordered.

Another method by should work in most cases (you'll have to verify it works on your data, with your query and if you 'ad-hoc' the query by adding predicates dynamically at run time, you'll need to be very careful with this solution) is to use an index to help the sort order. We'll also use an index HINT in the event the columns we are connecting by are indexed in other ways as well.

To demonstrate this technique -- we will use the scott/tiger table EMP.


ops$tkyte@8i> create table emp as select * from scott.emp;
ops$tkyte@8i> alter table emp add constraint emp_pk primary key(empno);
ops$tkyte@8i> create index emp_idx1 on emp(mgr,job);


So, we have the emp table with 2 indexes now, one on empno and one on (mgr,job).

The connect by query we will run will use "connect by prior empno = mgr". That means for each row we start with -- the database will effectively do "select * from emp where mgr = :PRIOR_EMPNO" to find the subordinate records. It will be looking for an index that has the MGR column on the leading edge. Since we have one on (mgr,job), and the database finds that index -- the data will come out in a hirearchy ordered by JOB in each level. We can see this with:

ops$tkyte@8i> set autotrace on explain

ops$tkyte@8i> select lpad( '*', level*2 ) || ename ename,
2 job
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 /

ENAME JOB
-------------------- ---------
*KING PRESIDENT
*JONES MANAGER
*SCOTT ANALYST
*ADAMS CLERK
*FORD ANALYST
*SMITH CLERK
*BLAKE MANAGER
*JAMES CLERK
*ALLEN SALESMAN
*WARD SALESMAN
*MARTIN SALESMAN
*TURNER SALESMAN
*CLARK MANAGER
*MILLER CLERK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
5 4 INDEX (RANGE SCAN) OF 'EMP_IDX1' (NON-UNIQUE)


See how the optimizer found and used the EMP_IDX1 to retrieve the data and the data is in fact sorted by JOB at each level. Suppose we wanted to be sorted by ENAME at each level. We should create an index then on MGR,ENAME and use a hint to tell Oracle we want to use that index in the connect by. It would look like this:


ops$tkyte@8i> create index emp_connect_by_idx on emp(mgr,ename)
2 /

ops$tkyte@8i> select /*+ INDEX(emp emp_connect_by_idx) */
2 lpad( '*', level*2 ) || ename ename,
3 job
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr
7 /

ENAME JOB
-------------------- ---------
*KING PRESIDENT
*BLAKE MANAGER
*ALLEN SALESMAN
*JAMES CLERK
*MARTIN SALESMAN
*TURNER SALESMAN
*WARD SALESMAN
*CLARK MANAGER
*MILLER CLERK
*JONES MANAGER
*FORD ANALYST
*SMITH CLERK
*SCOTT ANALYST
*ADAMS CLERK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=39)
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=65)
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'EMP_CONNECT_BY_IDX' (NON-UNIQUE) (Cost=2 Card=1)


We verified the query plan is using our index and in fact the data is sorted now by ENAME at each level, not by job. If you need 2 different sorts -- you can still do that, we can get our original "sort by job" by hinting the query to use that index:

ops$tkyte@8i> select /*+ INDEX(emp emp_idx1) */
2 lpad( '*', level*2 ) || ename ename,
3 job
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr
7 /

ENAME JOB
-------------------- ---------
*KING PRESIDENT
*JONES MANAGER
*SCOTT ANALYST
*ADAMS CLERK
*FORD ANALYST
*SMITH CLERK
*BLAKE MANAGER
*JAMES CLERK
*ALLEN SALESMAN
*WARD SALESMAN
*MARTIN SALESMAN
*TURNER SALESMAN
*CLARK MANAGER
*MILLER CLERK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=39)
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=65)
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'EMP_IDX1' (NON-UNIQUE) (Cost=2 Card=1)

So, it is sorted by JOB once again.


You should consider using QUERY Plan stability with Oracle8i, release 8.1 once you get the right plan in place. This will make it such that once you get the plan that uses the index you want -- it will be stored in the database and you can request at run time to use "plan X" or "plan Y" for a given query. This will protect you from changes in the plan over time as different statistics are collected, versions are upgraded and so on. You must be careful to have the correct indexes in place, else the data will *not* be ordered and the fact the data is not ordered is not considered an error (you'll not be notified that the index you want is not in place)


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More reading on the same problem.

Andrew, August 13, 2001 - 12:16 pm UTC

A reader, October 17, 2001 - 4:32 am UTC

Verygood explaination...

Thanks.

Helena Markova, July 31, 2002 - 8:17 am UTC

Thank you for explanation.

"ordering a connect by"

Raja, May 22, 2003 - 11:47 am UTC

Hi Tom,
I have a table MENU_ITEMS with the following data for example.....

ID ITEM_NAME PARENT_ID ORDER_NUM
----- ------------------------- --------- ---------
1 Client 1
2 Client Info 1 1
3 Client Profile Info 2 1
4 Client Accounts Info 2 2
5 Add New Client 1 2
6 Create Client Profile 5 1
7 Create Client Accounts 5 2
8 Edit Client 1 3
9 Edit Client Profile 8 1
10 Edit Client Accounts 8 2
11 Remove Client 1 4
12 Remove Client Profile 11 1
13 Remove Client Accounts 11 2
14 Reports 2

The front-end requires a record set in the parent-child heirarchy ordered by the order_num column. I tried to do it as below....

select id, item_name, parent_id, order_num
from menu_items
start with parent_id is null
connect by parent_id = prior id
order by order_num ;

However, this is what I get....

ID ITEM_NAME PARENT_ID ORDER_NUM
----- ------------------------- --------- ---------
1 Client 1
2 Client Info 1 1
3 Client Profile Info 2 1
6 Create Client Profile 5 1
9 Edit Client Profile 8 1
12 Remove Client Profile 11 1
5 Add New Client 1 2
4 Client Accounts Info 2 2
7 Create Client Accounts 5 2
10 Edit Client Accounts 8 2
13 Remove Client Accounts 11 2
14 Reports 2
8 Edit Client 1 3
11 Remove Client 1 4

Could you please help me with any ideas to get around this problem. We use Oracle9i database.

Thanks in advance!

Tom Kyte
May 23, 2003 - 8:21 am UTC

search for

"order siblings by"

on this site.

"ordering a connect by"

Raja, May 23, 2003 - 9:52 am UTC

Thanks a lot! it works!!

Connect by ????

M.Srinivas, March 20, 2004 - 12:43 pm UTC

Hi Tom,
What does this "Connect by clause " do?Does it sort data
or compare data rowwise?I am not clear with this.Could you
Please explain ?
Please do write a followup.
Bye!


Tom Kyte
March 21, 2004 - 9:43 am UTC

better yet, i'll teach you to fish.

we document this stuff:
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937 <code>


rule-based optimizer

Michael Haas, April 16, 2004 - 5:07 am UTC

I tried the solution discribed here and it didn't work, probably because the rule based optimizer was used or due to other reasons.

BUT: Based on the proposed idea of presorting the emp table I tried the following:

select /*+ rule */
lpad( '*', level*2 ) || ename ename,
job
from (select * from emp order by mgr, ename)
start with mgr is null
connect by prior empno = mgr;

and it worked.
(tried with Oracle9i)


FanTastic!

Rick Bliss, June 10, 2004 - 4:48 pm UTC

This worked great! i'm building a menu i thought i would never get sorted properly!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.