| How to optimize COST of quiery containg ORDER BY [message #23678] |
Thu, 26 December 2002 03:13  |
Srinath
Messages: 40
Registered: May 2002
|
Member |
|
|
Hi all,
I have 3 tables A(l,m,n),B(h,i,j,k),C(x,y,z).
The following query gives me a COST of whopping "880597". If I remove the ORDER BY the COST reduces to "2779".
SELECT B.*,C.x
FROM B,C
WHERE EXISTS (SELECT 'x' FROM A
WHERE
A.l = B.h
AND
A.m ='Y'
AND A.n = 'Y')
AND C.y = B.i
ORDER BY B.h,B.i,B.j ASC;
I have indexes on
A- one for l and other for (l,m,n)
B- one for h ,one for i, one for (h,i) and other for (h,i,j)
C- one for x and other for y
ANY METHOD FOR OPTIMIZING THE QUERY? I donot have access to any server files. I can create indexes etc. from front end.
Any inputs highly appriciated.
Regards,
Srinath
|
|
|
|
| Re: How to optimize COST of quiery containg ORDER BY [message #23679 is a reply to message #23678] |
Thu, 26 December 2002 09:46  |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
How about trying the opposite, like where not exists..this could also be most efficient.
Order by clauses uses an index only if they meet a couple of requirements.
All columns that make up order by clause must be contained within a single index in the same sequence. Another one is all of the columns that make up order by clause must be as not null within the table definition.
Annie
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
