Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy Builder SQL (merged 2 cross-posts)
Hierarchy Builder SQL (merged 2 cross-posts) [message #280477] Tue, 13 November 2007 16:26 Go to next message
mikiharjai
Messages: 5
Registered: August 2007
Junior Member
Hi,

We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.

Example:
ENum-----------------Mgr
Julie
Andrew---------------Julie
Mark-----------------Andrew
Matt-----------------Andrew
Wyatt----------------Julie
Jenny----------------Wyatt

SELECT enum, mgr FROM <table> START WITH enum IS NULL
CONNECT BY PRIOR enum = mgr;

Output:
-------
Julie
---Andrew
------Mark
------Matt
---Wyatt
------Jenny
---Joel

How do I get the same output without using CONNECT BY PRIOR command. I don't mind creating a new table that will keep all the possible combinations between enum and mgr columns. we are using this sql in a procedure and this query is required for 300000 records. I understand that it is gud for 30-40K records. so looking for some alternate.

Please provide your solution. I really appreciate it.


Thanks in advance,

Miki
Hierarchy Builder SQL [message #280481 is a reply to message #280477] Tue, 13 November 2007 17:26 Go to previous messageGo to next message
mikiharjai
Messages: 5
Registered: August 2007
Junior Member
Hi,

We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.

Example:
ENum-----------------Mgr
Julie
Andrew---------------Julie
Mark-----------------Andrew
Matt-----------------Andrew
Wyatt----------------Julie
Jenny----------------Wyatt

SELECT enum, mgr FROM <table> START WITH enum IS NULL
CONNECT BY PRIOR enum = mgr;

Output:
-------
Julie
---Andrew
------Mark
------Matt
---Wyatt
------Jenny
---Joel

How do I get the same output without using CONNECT BY PRIOR command. I don't mind creating a new table that will keep all the possible combinations between enum and mgr columns. we are using this sql in a procedure and this query is required for 300000 records. I understand that it is gud for 30-40K records. so looking for some alternate.

Please provide your solution. I really appreciate it.


Thanks in advance,

Miki
Re: Hierarchy Builder SQL [message #280483 is a reply to message #280477] Tue, 13 November 2007 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/92498/74940/
Do NOT cross/multipost.
Read & FOLLOW the posting guidelines as stated in the #1 STICKY post found at top of this forum.
Re: Hierarchy Builder SQL [message #280488 is a reply to message #280481] Tue, 13 November 2007 19:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What version of Oracle are you using.

10g introduced some MAJOR improvements to CONNECT BY, not just in functionality but also in performance.

Before you go spending $$ downgrading your datamodel, you might fist consider a free upgrade to 10g.

If you are using 10g, post your query and the Explain Plan here.

Ross Leishman
Re: Hierarchy Builder SQL [message #280548 is a reply to message #280481] Wed, 14 November 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Be sure that the issue comes from "connect by" and not the rest.
As always, execution, statistics, index, table description and so on and so on.

Regards
Michel
Re: Hierarchy Builder SQL [message #280769 is a reply to message #280548] Wed, 14 November 2007 13:16 Go to previous messageGo to next message
mikiharjai
Messages: 5
Registered: August 2007
Junior Member
i have checked all and nothing is an issue. Please advise me something.

thanks,
Miki
Re: Hierarchy Builder SQL (merged 2 cross-posts) [message #280770 is a reply to message #280477] Wed, 14 November 2007 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Please advise me something.
Upgrade to V11.1 this month!
Re: Hierarchy Builder SQL [message #280771 is a reply to message #280488] Wed, 14 November 2007 13:18 Go to previous messageGo to next message
mikiharjai
Messages: 5
Registered: August 2007
Junior Member
I have specified that i'm using 10g R2 but still im facing the performance issue.

Thanks,
Nitin
Re: Hierarchy Builder SQL (merged 2 cross-posts) [message #280774 is a reply to message #280477] Wed, 14 November 2007 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
read & FOLLOW posting guidelines as stated in above URL

How to Identify Performance Problem and Bottleneck
http://www.orafaq.com/forum/t/84315/74940/
Re: Hierarchy Builder SQL [message #280814 is a reply to message #280488] Wed, 14 November 2007 20:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Wed, 14 November 2007 12:49

If you are using 10g, post your query and the Explain Plan here.

Previous Topic: ORA-00904: "COPY_ID": invalid identifier
Next Topic: Help with joins
Goto Forum:
  


Current Time: Mon Dec 05 08:54:59 CST 2016

Total time taken to generate the page: 0.16745 seconds