Hierarchy Builder SQL (merged 2 cross-posts) [message #280477] |
Tue, 13 November 2007 16:26  |
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   |
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 #280488 is a reply to message #280481] |
Tue, 13 November 2007 19:49   |
rleishman
Messages: 3728 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
|
|
|
|
|
|
|
|
|