Equivalent of Hierarchy SQL in Oracle

[rchenna]

Hi All,

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 dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.

Please provide your solution. I really appreciate it.


Thanks in advance,
Rao


--
Rao V Chenna

[salix]

Hi All,

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 dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.

Please provide your solution. I really appreciate it.


Thanks in advance,
Rao


--
Rao V Chenna

Hi
How many rows do you want select ? In one task use hierarchy query for the 40 - 60 thousand rows without any problems.