RE: Query

From: Ramasubramanian, Shankar (Cognizant) <RShankar1_at_CHN.COGNIZANT.COM>
Date: Wed, 03 Oct 2001 06:11:41 -0700
Message-ID: <F001.003A04D0.20011003054533@fatcity.com>

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

Received on Wed Oct 03 2001 - 08:11:41 CDT