How do you analyze yr tables? i.e. dc_pms.tx_repository - are statistics
up-to-date?
Also change you HINT as (/*+ INDEX(b TX_TRANID_IDX) */
-----Original Message-----
From: richardregan
[mailto:oracledba-ezmlmshield-x95583582.[Email address protected]
Sent: 31 July 2007 11:59 AM
To: LazyDBA Discussion
Subject: Force deletes to use indexes.....(my 1st email!)
Hello, this is my first email.
The following delete statement will not use an index which has been
created on tran_id.
I have tried hints etc which are being ignored!
Any ideas? Or am I being stupid?
DELETE FROM dc_pms.tx_repository a
WHERE a.tran_id IN (
SELECT /*+ INDEX(TX_TRANID_IDX) */
b.tran_id
FROM dc_pms.tx_repository b
WHERE b.tran_id = a.tran_id
AND b.src_date > a.src_date
AND a.lot_type = b.lot_type)
AND a.lot_type = 17
AND a.src_date < 20060630;
Plan
DELETE STATEMENT CHOOSE Cost: 189,476 Bytes: 14,235,926 Cardinality:
245,447
4 DELETE DC_PMS.TX_REPOSITORY
3 HASH JOIN SEMI Cost: 189,476 Bytes: 14,235,926
Cardinality: 245,447
1 TABLE ACCESS FULL DC_PMS.TX_REPOSITORY Cost:
90,923 Bytes: 142,359,405 Cardinality: 4,908,945
2 TABLE ACCESS FULL DC_PMS.TX_REPOSITORY Cost:
90,923 Bytes: 212,813,484 Cardinality: 7,338,396
Richard M Regan
Senior Oracle Database Administrator
Group Application Management
HBOS Group Application Services
HBOS Group Services
DDI - (0113) 2353492
Mobile - 07876790930
Email - [Email address protected]
.
------------------------------------------------------------------------
--------------------------------------------
HBOS plc, Registered in Scotland No. SC218813. Registered Office: The
Mound, Edinburgh EH1 1YZ. HBOS plc is a holding company, subsidiaries of
which are authorised and regulated by the Financial Services Authority.
========================================================================
======
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
“This e-mail is sent on the Terms and Conditions that can be accessed by Clicking on this link http://www.vodacom.co.za/legal/email.jsp "
Oracle LazyDBA home page