RE: Query

From: Libal, Ivo <ivo.libal_at_knapp-systems.com>
Date: Wed, 03 Oct 2001 06:34:32 -0700
Message-ID: <F001.003A0530.20011003055521@fatcity.com>

If you use alias for table (like select a.id from auto a; ) then you have to reference the table in a hint by this alias too (like select /*+index(a a_ind)*/ a.id from auto a; where a_ind is a name of valid index on the table) .
Ivo

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

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

6 Cost (17270,351857,10203853)

         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).
--

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
--------------------------------------------------------------------