Home » SQL & PL/SQL » SQL & PL/SQL » What r the ways of reducing cost & time?
What r the ways of reducing cost & time? [message #248135] Thu, 28 June 2007 02:34 Go to next message
Messages: 25
Registered: June 2007
Junior Member
A PL/SQL procedure is being used to generate a report from Crystal. There are tables having master-detail relationship. I want all master records in the report. Data may not exist in child table. Report generation is taking 20 min. to execute the procedure. How can I reduce time?

I already applied the following tricks:
1. An outer join with master table
2. Explicit cursor for child table (having 1,00,00,000) records
3. Temporary table to insert filtered record from child table to global temporary table.

Is there any other way to reduce cost & time?
Re: What r the ways of reducing cost & time? [message #248142 is a reply to message #248135] Thu, 28 June 2007 02:58 Go to previous message
Michel Cadot
Messages: 65146
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think you need all the children for all the masters.
So wait for user ask for the children of a master key to ask for them.

Previous Topic: How many execution plans Oracle generates for a query?
Next Topic: %Rowtype
Goto Forum:

Current Time: Mon Aug 21 07:15:52 CDT 2017

Total time taken to generate the page: 0.02705 seconds