| Multiple Outer Joins with Order By [message #21663] |
Wed, 21 August 2002 18:02  |
Girish
Messages: 16
Registered: September 1998
|
Junior Member |
|
|
I have 5 tables:
A: 500K records (id, colA)
B: 500K records (id, a_ptr, colB)
C: 10K records (id, b_ptr, colC)
D: 20K records (id, c_ptr, colD)
E: 50K records (id, d_ptr, colE)
And my query is as follows:
SELECT A.colA, B.colB
FROM A, B, C, D, E
WHERE B.a_ptr = A.id (+)
AND C.b_ptr = B.id (+)
AND D.c_ptr = C.id (+)
AND E.d_ptr = D.id (+)
ORDER BY A.colA
This query takes almost 15 secs to run.
I have created the required indexes on the relevant columns here and the init.ora parameters are as follows:
OPTIMIZER_MODE = FIRST_ROWS
SORT_AREA_SIZE = 100 M
Can any of you give me any ideas as to how I could improve this query?
|
|
|
|
| Re: Multiple Outer Joins with Order By [message #21700 is a reply to message #21663] |
Fri, 23 August 2002 07:42  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Try this out, by putting the ORDER BY clause outside the join like
SELECT * (
SELECT A.colA COLA, B.colB
FROM A, B, C, D, E
WHERE B.a_ptr = A.id (+)
AND C.b_ptr = B.id (+)
AND D.c_ptr = C.id (+)
AND E.d_ptr = D.id (+) )
ORDER BY COLA
The above code is yours except I put it in a virtual table and aliasing the A.colA to COLA. Check it out and let me know how long is it taking now?
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
