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


Oracle LazyDBA home page