And surprise, surprise... per Metalink, OWS is unwilling to support the
undocumented parameter approach.
-----Original Message-----
Sent: Thursday, November 07, 2002 9:14 AM
To: Multiple recipients of list ORACLE-L
To add another point, somebody else (the thread is too long for this lazy
git to go back and search who! ;P) mentioned that the access plans *could*
change if #3 were the solution you go for - however this can be overcome
with the use of "stored outlines"..
my 2 pence :)
Mark
-----Original Message-----
Sent: 07 November 2002 15:49
To: Multiple recipients of list ORACLE-L
Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway,
thanks for the info. There was lots of great discussion on this and I
appreciate the collective brain power of the list. Proposed solutions: 1)
upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes
on the columns to be sorted; 4) use an undocumented parameter; 5)
Metalink... No help yet.
Steve Orr
Bozeman, Montana
-----Original Message-----
Sent: Wednesday, November 06, 2002 12:31 PM
To: Multiple recipients of list ORACLE-L
Importance: High
I'm seeing some very strange results using _new_connect_by_enabled = true
Login to the database, run these 2 commands:
alter session set "_new_connect_by_enabled" = true;
1 SELECT *
2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid
5* order by parentid, nodeorder
11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
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
10 3 1 nested folder2.2.1
11 10 2 nested folder2.2.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
11 rows selected.
11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL>
These are the expected results. Now I comment out the 'FROM' clause, and
add a
new FROM and ORDER by:
SELECT *
-- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
from treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
order by parentid, nodeorder;
Here are the results:
SELECT *
2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 from treenode
4 START WITH parentid=0
5 CONNECT BY PRIOR ID = parentid
6* order by parentid, nodeorder
11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
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
10 3 1 nested folder2.2.1
11 10 2 nested folder2.2.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
11 rows selected.
11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL>
Hey look! The output is working just the way we would like,
without the inline view.
Now, remove the commented out line:
SELECT *
from treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
order by parentid, nodeorder;
And the resulting output:
1 SELECT *
2 from treenode
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid
5* order by parentid, nodeorder
11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
ID PARENTID NODEORDER DESCRIPTION
---------- ---------- ---------- --------------------
1 0 0 top folder
9 1 0 1st subfolder
2 1 1 2nd subfolder
7 1 2 3rd subfolder
4 2 1 folder 2 item 1
3 2 2 folder 2 item 2
6 2 3 folder 2 item 3
10 3 1 nested folder2.2.1
5 7 0 folder 3 item 1
8 7 1 folder 3 item 2
11 10 2 nested folder2.2.2
11 rows selected.
11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL>
Interesting, no?
Jared
"Madhavan Amruthur" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/06/2002 08:23 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: SQL Brain Teaser Challenge
Hi Steve,
You can get the below query to work in 8.1.7 (not sure about previous
versions) by setting the undoc parameter _new_connect_by_enabled = true
and can be set for a session
SELECT *
FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
You can also use sys_connect_by_path feature in 9i which gives you the
entire hierarchy path (can be used in 8i with the above undoc parameter)
For eg:
select sys_connect_by_path(parent_id,'/')
from treenode
start with parent_id = 0
connect by prior id = parent_id
will give you a output like
0/1/9
0/1/2
0/1/2/4
...etc
As always setting an undoc parameter is not advisable unless instructed
by Oracle support :-)
Hope this helps.
Regards,
Madhavan
http://www.dpapps.com
--
Madhavan Amruthur
DecisionPoint Applications
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
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).
