| correlated subquery taking a long time to execute [message #151371] |
Wed, 14 December 2005 08:30  |
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  |
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 |
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 #151872 is a reply to message #151371] |
Mon, 19 December 2005 09:44 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
