Re: How to optimize the mentioned SQL Query

"Tuhin Kumar" <tkumar_at_ipolicynet.com> wrote in message news:e4ad76f5.0302210628.1913a942_at_posting.google.com...
> Hi,
> I have a query like this:
>
> SELECT XYZ.B1, XYZ.B2 FROM
> (
> SELECT * from A where ( A.C1 IN ( 0,1,2,3,4 ) ) AND ( A.C2 IN
 ( 1,2 ) )
> AND (( A.C3 =15 ) and (A.C4 = 0 or A.C5 = 0)) AND A.C6 >=
TO_DATE('2002-01-01
> 06:30:00', 'YYYY-MM-DD HH24:MI:SS') AND A.CP < 376137 ORDER BY A.CP
DESC) XYZ
> WHERE ROWNUM < 602
>
> WHERE A.CP is the primary key and also the primary indexing is on that
A.CP.
>
> I have around 4 lakhs record in the Table A. The query is taking more
than
> 2 minutes to execute on my m/c.
>
> My basic requirement is to read last 602 records from A with CP less
than
> 376137 plus matching other criteria mentioned in the query. It will be
highly
> grateful if someone provides with a much optimal query to perform the
task.
>
> TIA,
> Tuhin

It will be better to do a explain plan of the query and see what you can do to improve. Couple of points which may change things are 1.don't select everything from A
2. try to use a.c1 < 5 instead of IN

Also for other people - 4 lakhs means 400,000 hth
santysharma Received on Fri Feb 21 2003 - 12:43:45 CST