Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL for hierarchy (Oracle 10G)
PL/SQL for hierarchy [message #279087] Tue, 06 November 2007 20:35 Go to next message
Messages: 5
Registered: August 2007
Junior Member

i have a procedure that is written for generating the hierarchy.
let me brief you about the procedure flow:

1. includes all the child groups
2. includes all the roll up groups
3. initialize the hierarchy for top group by child group
4. initialize the hierarchy for top group's child group by child group
5. say A is top group then we THEN we loop though and update he following order A--B , A--C, A--D , B--C, B--D, C--D
6. IF K, L is rollup groups of A then we update all child group of A as following: K--B = KA || AB, K-C = KA || AC,K-D == KA || AD

Most of the things work well and consumes less of time and CPU usage with loads of data. but step 7 takes more than 40 min to execute with data of 3000000 records. For generating the hierarchy we are using the START WITH..... CONNECT BY PRIOR
Can anyone suggest me how can i optimize the procedure so that it takes less of CPU usage and processing time. This is somewhat urgent as my client is on my ass for its solution. I tried to make the things to granular level thinking that it will resolve but its of no use.

Your help is highly appreciated.

Thanks in advance.

Re: PL/SQL for hierarchy [message #279134 is a reply to message #279087] Wed, 07 November 2007 01:43 Go to previous messageGo to next message
Messages: 7062
Registered: December 2001
Senior Member
message report

Reported By: mikiharjai
On: Wed, 07 November 2007 08:40
In: SQL & PL/SQL PL/SQL Experts PL/SQL for hierarchy
Reason: noone has posted anything on this yet. If you can help me it would be of greaat help. i fu require to see the procedure 'll post u that too. your help is highly appreciable. thanks, Miki
Don't report messages to get attention. Omly moderators get to see message reports. Now you'll have to wait.

Re: PL/SQL for hierarchy [message #279143 is a reply to message #279134] Wed, 07 November 2007 02:05 Go to previous message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What "fu" means?
If you want help post a test case (create table and insert statements) and explain on this test case what you want to do and what you want to get.
Also post your Oracle version.
And post it formatted as explained in OraFAQ Forum Guide.

Previous Topic: searching across two tables
Goto Forum:

Current Time: Sat Aug 19 04:07:33 CDT 2017

Total time taken to generate the page: 0.06482 seconds