Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query

RE: Query

From: Malik, Fawzia <Fawzia.Malik_at_open-talk.co.uk>
Date: Wed, 03 Oct 2001 05:35:44 -0700
Message-ID: <F001.003A0457.20011003053017@fatcity.com>

Thankyou so much this actually worked...but why??

-----Original Message-----
Sent: 03 October 2001 13:50
To: Multiple recipients of list ORACLE-L

I would try the following
DELETE /*+INDEX(a MORD_PK) */ FROM .......

Ivo

-----Original Message-----
Sent: Wednesday, October 03, 2001 02:35 PM To: Multiple recipients of list ORACLE-L

Following on from this, instead of using the query below, I created a temporary table op_orders_arc which contained all the data I wanted deleted from the main table (op_orders). I then run the following, but the explain plan shows it doesn't use the index I have in the hint and it still does a full table scan. Any suggestions please..>??

explain plan set statement_id='ords7' for DELETE /*+ INDEX (olsr10.OP_ORDERS olsr10.MORD_PK) */ FROM OLSR10.OP_ORDERS a
WHERE exists
 (select 1 from olsr10.op_orders_arc b
  where b.id = a.id
  and b.xmas_id = a.xmas_id)
;

Explain plan:

       0                 17270
  DELETE STATEMENT (choose)     Cost (17270,351857,10203853)

         1          0          1

    DELETE OLSR10 OP_ORDERS
         2          1          1
      FILTER

         3          2          1
        PARTITION RANGE    (all)  Pt id: 3 Pt Range: 1 - 6

         4          3          1               1

          TABLE ACCESS (analyzed)  OLSR10 OP_ORDERS (full)  Pt id: 3 Pt
Range: 1
         5          2          2
        INDEX (analyzed) UNIQUE OLSR10 OPORD_IND (unique scan)  Cost (2,1,8)




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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Libal, Ivo
  INET: ivo.libal_at_knapp-systems.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). This message is confidential and is intended for the addressee only; unless clearly stated that this disclaimer should not apply, this e-mail is not intended to create legally binding commitments on behalf of any company in the British Interactive Broadcasting Holding Limited group, nor do its contents reflect the corporate views or policies of any such company. Any unauthorised disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.
Received on Wed Oct 03 2001 - 07:35:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US