Steve, I'm not sure why as of yet, but I had some success by creating two segmented indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID:
ID PARENTID NODEORDER DESCRIPTION 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 Seems strange, though, and I don't have the time to research it. We had the exact same problem for our BOM structures and ended up writing a recursive PL/SQL procedure and cursors to do it. Not nice. Also, I'm using CBO on 8.1.7.4 and have analyzed the tables and indexes w/o histograms using DBMS_STATS. BTW, the explain plan is a helluva lot better with the indexes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Orr, Steve [mailto:sorr@;rightnow.com] > Sent: Tuesday, November 05, 2002 11:24 AM > To: Multiple recipients of list ORACLE-L > Subject: SQL Brain Teaser Challenge > > > Challenge: present SQL results hierarchically and sort the > nodes. Use sort > column without changing data. Here's the DDL/DML to start: > > create table treenode ( > id number not null > constraint pk_treenode primary key, > parentid number not null, > nodeorder number not null, > description varchar2(20) null); > > insert into treenode values(1,0,0,'top folder'); > insert into treenode values(9,1,0,'1st subfolder'); > insert into treenode values(7,1,2,'3rd subfolder'); > insert into treenode values(2,1,1,'2nd subfolder'); > insert into treenode values(8,7,1,'folder 3 item 2'); > insert into treenode values(6,2,3,'folder 2 item 3'); > insert into treenode values(5,7,0,'folder 3 item 1'); > insert into treenode values(3,2,2,'folder 2 item 2'); > insert into treenode values(4,2,1,'folder 2 item 1'); > ----------------------------------------------------- > Here's the data presented hierachically without the desired sort: > select * from treenode > start with parentid=0 connect by prior id = parentid; > ID PARENTID NODEORDER DESCRIPTION > ---------- ---------- ---------- -------------------- > 1 0 0 top folder > 9 1 0 1st subfolder > 7 1 2 3rd subfolder > 8 7 1 folder 3 item 2 > 5 7 0 folder 3 item 1 > 2 1 1 2nd subfolder > 6 2 3 folder 2 item 3 > 3 2 2 folder 2 item 2 > 4 2 1 folder 2 item 1 > ----------------------------------------------------- > Desired SQL statement results: > ID PARENTID NODEORDER DESCRIPTION > ---------- ---------- ---------- -------------------- > 1 0 0 top folder > 9 1 0 1st subfolder > 2 1 1 2nd subfolder > 4 2 1 folder 2 item 1 > 3 2 2 folder 2 item 2 > 6 2 3 folder 2 item 3 > 7 1 2 3rd subfolder > 5 7 0 folder 3 item 1 > 8 7 1 folder 3 item 2 > ----------------------------------------------------- > > Kudos to anyone who can figure out how to do this via SQL. > > > Steve Orr > Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
