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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
