Home » RDBMS Server » Performance Tuning » correlated subquery taking a long time to execute
correlated subquery taking a long time to execute [message #151371] Wed, 14 December 2005 08:30 Go to next message
vgs2005
Messages: 123
Registered: April 2005
Senior Member
hi,

I have a query that runs for a very long time to execute. It has a correlated query with itself. The inner query takes more than 200000 records - but the outer query actually most of the time just picks up 2 records.

How do I fix this??
select distinct    
       v.dioi,
       v.doc,
       v.don,
       v.dlevel
  from hierarchyA v
 where v.dlevel = 2 
   and EXISTS (Select 1
                 from hierarchy t   
                where t.dioid = v.ioid
                  and t.dlevel = 1);


Will creating a temporary storage (like collection, or materialized view or view or cursor) for the inner query help?

Please help. thanks..
Re: correlated subquery taking a long time to execute [message #151373 is a reply to message #151371] Wed, 14 December 2005 08:43 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
I assume hierarchyA is a view? What is its definition?

Do you have any indexes on this table? Have statistics been computed recently?

Does the query perform any faster without the DISTINCT?

What version of the database are you running?

How many rows are there in the table altogether?
Re: correlated subquery taking a long time to execute [message #151690 is a reply to message #151373] Fri, 16 December 2005 03:32 Go to previous messageGo to next message
vgs2005
Messages: 123
Registered: April 2005
Senior Member
Here is the corrected SQL:
select distinct    
       v.ioid,
       v.doc,
       v.don,
       v.dlevel
  from hierarchy v
 where v.dlevel = 2 
   and EXISTS (Select 1
                 from hierarchy t   
                where t.dioid = v.ioid
                  and t.dlevel = 1);


1. The query is self-referencing.

2. I have index dioid. Statistics are computed.

3. I actually need the 'distinct' here..

4. Oracle is 9.2.0.5

5. There are 200,500 rows in the table.

Actually what happens is that this table is truncated at the beginning of the processing. And then repopulated. The SQL above is used to get the child records which will then again be inserted into the table.



Re: correlated subquery taking a long time to execute [message #151761 is a reply to message #151690] Mon, 19 December 2005 02:05 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could you post the current EXPLAIN PLAN

In SQL*Plus,
set autotrace trace explain

then run your SQL.
_____________
Ross Leishman
Re: correlated subquery taking a long time to execute [message #151872 is a reply to message #151371] Mon, 19 December 2005 09:44 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Is there an index on dlevel, or is it a part of an index? Is dioid and ioid the same thing? If not, is there an index on each, or on both?

And what does it maean when you say a long time? There are only 200,000 rows, what exactly is a long time? You say you delete records often out of this table? Is there a lot of fragmentation or wasted space below the HWM? Are there statistics gathered after the deletions? Oh you truncate, not delete...

Also, you may consider rewriting the query to a different form based on your knowledge of the data. In this case, rather than an exists you could possibly use a couple inline views, or an in, because you can use the ids with a certain dlevel to limit your rows processed in each step. You said there are only two results to the query? If there are only a handful of records with a dlevel of 2, for instance, then get those first in an inner query then go look for matching records with level 1.

But all that may not even be needed based on your explain plan and your answer to the above questions.

And oracle has the connect by construct for dealing with hierarchies, where you don't really have to keep a level as a column in your table because it comes out in your query.
Previous Topic: Query using bind variable is slower??
Next Topic: Solaris vs. Windows performance difference
Goto Forum:
  


Current Time: Fri Dec 09 12:17:04 CST 2022