RE: SQL Brain Teaser Challenge

WOW. This actually works quite well!!!

All this business is for generating an HTML tree navigation object using SQL, Perl-CGI, and javascript (from www.treeview.net) and it's critical that everything appear in the right order or the javascript will fail miserably. I hate to be dependent on indexes for sorts. If an index goes south or another is added that changes the results the whole app could come crashing down. But it's not the first time I'd be stuck with a fragile solution that works and I don't like it anyway.

Thanks,
Steve

-----Original Message-----
Sent: Tuesday, November 05, 2002 2:33 PM To: 'ORACLE-L_at_fatcity.com'
Cc: Orr, Steve

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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Orr, Steve [mailto:sorr_at_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: Orr, Steve
  INET: sorr_at_rightnow.com

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: ListGuru_at_fatcity.com (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).