| Parallel sub-queries [message #65049] |
Tue, 06 April 2004 08:42 |
David Peters
Messages: 6
Registered: April 2004
|
Junior Member |
|
|
I have a query with two sub-queries in it :
SELECT A FROM B
WHERE C IN (
SELECT X FROM Y
)
AND NOT EXISTS (
SELECT D FROM E
)
If I execute the statements individually, they are almost instantaneous:
SELECT X FROM Y - .01 seconds
SELECT D FROM E - .01 seconds
And I insert the resultset into the top level query :
SELECT A FROM B
WHERE C IN (
[[1,2,3,4]]
)
AND NOT EXISTS (
[[4,5,6,7]]
)
This is almost instantaneous as well - .02 seconds
However, the original query takes 4 seconds to execute. Why? By my calculations it should take about half a second.
I have tried putting the /*+ PUSH_SUBQ */ hint in the beginning of the sub-selects, but that doesnt help. (Shouldnt it automatically perform the inner loops first anyway?) The explain plan shows that all of the indexes are analyzed and being used, and that NESTED LOOPS is being used.
Could the additional time be a result of oracle trying to put the result sets back in the original query?
Are there any tuning parameters surrounding parallel subqueries?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
