| nested select statements [message #64992] |
Thu, 25 March 2004 08:02  |
David
Messages: 110
Registered: November 1998
|
Senior Member |
|
|
Hello,
I have a query with several nested select statements that takes about 4 seconds to run.
If I run the nested statements seperately and manually include the results in my top level select, the time to execute is much lower - less than 1 second for all of the combined select statements.
How can I improve the performance of the top level statement?
Here is my exact query :
SELECT bm.classNameA2A2, bm.idA2A2 FROM BaselineMember bm, GDLSCorePart version WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983) AND version.idA2A2 = bm.idA3B5 AND bm.idA3A5 IN (SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2) AND NOT EXISTS (SELECT brm.idA2A2 FROM BaselineRemoveMember brm WHERE brm.idA3B5 = bm.idA2A2 AND brm.idA3A5 IN (SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2) );
which takes 4 seconds.
If I run the nested query :
SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2;
this takes .01 seconds to run and returns the values :
41300399,41300390
Now if I include this resultsset into the original query :
SELECT bm.classNameA2A2, bm.idA2A2 FROM BaselineMember bm, GDLSCorePart version WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983) AND version.idA2A2 = bm.idA3B5 AND bm.idA3A5 IN (41300399,41300390) AND NOT EXISTS (SELECT brm.idA2A2 FROM BaselineRemoveMember brm WHERE brm.idA3B5 = bm.idA2A2 AND brm.idA3A5 IN (41300399,41300390));
.391 seconds
The query takes only .4 seconds.
Is this a sequence problem?
Sorry if I am missing somethign obvious.. I am new to this.
|
|
|
|
| Re: nested select statements [message #65011 is a reply to message #64992] |
Mon, 29 March 2004 11:58  |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
The EXPLAIN PLAN result of your query would help more, but before you post it, try this:
SELECT bm.classNameA2A2, bm.idA2A2
FROM BaselineMember bm, GDLSCorePart version
WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983)
AND version.idA2A2 = bm.idA3B5
AND bm.idA3A5 IN (SELECT idA2A2
FROM WTProductConfiguration
START WITH idA2A2 = 41300399
CONNECT BY PRIOR idA3C2IterationInfo = idA2A2)
AND NOT EXISTS (SELECT <font color=red>/*+ PUSH_SUBQ */</font>
brm.idA2A2
FROM BaselineRemoveMember brm
WHERE brm.idA3B5 = bm.idA2A2
AND brm.idA3A5 IN (SELECT idA2A2
FROM WTProductConfiguration
START WITH idA2A2 = 41300399
CONNECT BY PRIOR idA3C2IterationInfo = idA2A2));
The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest
possible place in the execution plan. Normally, subqueries that are not merged are
executed as the last step in the execution plan. If the subquery is relatively
inexpensive and reduces the number of rows significantly, then it improves
performance to evaluate the subquery earlier.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
