How to optimize the mentioned SQL Query

From: Tuhin Kumar <tkumar_at_ipolicynet.com>
Date: 21 Feb 2003 06:28:52 -0800
Message-ID: <e4ad76f5.0302210628.1913a942@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 Received on Fri Feb 21 2003 - 08:28:52 CST