Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Brain Teaser Challenge

RE: SQL Brain Teaser Challenge

From: Orr, Steve <sorr_at_rightnow.com>
Date: Thu, 07 Nov 2002 07:49:22 -0800
Message-ID: <F001.004FDD24.20021107074922@fatcity.com> 





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_at_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_at_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_at_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_at_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_at_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_at_dv01 SQL>




Interesting, no?




Jared












"Madhavan Amruthur" <mad5698_at_fastmail.fm>
Sent by: root_at_fatcity.com


 11/06/2002 08:23 AM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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



--



http://fastmail.fm - The holy hand grenade of email services


-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 



Author: Madhavan Amruthur


  INET: mad5698_at_fastmail.fm


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).





-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 



Author: 


  INET: Jared.Still_at_radisys.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).


-- 



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).
Received on Thu Nov 07 2002 - 09:49:22 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US