Home » RDBMS Server » Performance Tuning » Parallel sub-queries
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?
Previous Topic: Optimizer
Next Topic: High overhead on DBA_SEGMENTS
Goto Forum:
  


Current Time: Sun Jan 17 13:52:51 CST 2021