From RShankar1@CHN.COGNIZANT.COM Wed, 03 Oct 2001 06:11:41 -0700 From: "Ramasubramanian, Shankar (Cognizant)" Date: Wed, 03 Oct 2001 06:11:41 -0700 Subject: RE: Query In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi, If u are having a index on xmas_id then change the query as follows delete from olsr10.op_orders where (xmas_id = 193 or xmas_id = 207 or xmas_id = 245 or xmas_id = 300) and order_total = 0 and status_timestamp < to_date ('01-aug-2001','dd-mon-yyyy') When u use "in" in where condition the index won't be used. If u are not having a index in xmas_id , create it if it is neccessary. Thanks & Regards Shankar -----Original Message----- Sent: Wednesday, October 03, 2001 5:05 PM To: Multiple recipients of list ORACLE-L PLease can some one give me some advice on tuning this query.. delete from olsr10.op_orders where xmas_id in (193,207,245,300) and order_total = 0 and status_timestamp < to_date ('01-aug-2001','dd-mon-yyyy') / The explain plan for this is is quite costly as it does a full table scan: DELETE STATEMENT (choose) Cost (8636,68,2176) 1 0 1 DELETE OLSR10 OP_ORDERS 2 1 1 PARTITION RANGE (inlist) Pt id: 2 Pt Range: KEY(INLIST) - KEY(INLIST) 3 2 1 1 TABLE ACCESS (analyzed) OLSR10 OP_ORDERS (full) Pt id: 2 Pt Range: KEY (INLIST) - KEY(INLIST) Cost (8636,68,2176) Thanks in advance Fawzia This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com