Hi All,
Oracle 11.2 on Linux.
See this query and its plan below
SQL> DELETE
2 FROM TABLEA APE
3 WHERE NOT EXISTS
4 (SELECT 1
5 FROM TABLEB AP
6 WHERE AP.col1 = APE.col1
7 AND AP.col2 = APE.col2
8 AND AP.col3 = APE.col3)
9 AND ROWNUM < 51 ;
50 rows deleted.
Elapsed: 00:12:01.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1740911877
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 50 | 2650 | | 573K (1)| 01:54:40 |
| 1 | DELETE | TABLEA | | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
|* 3 | HASH JOIN RIGHT ANTI | | 80M| 4059M| 1775M| 573K (1)| 01:54:40 |
| 4 | INDEX FAST FULL SCAN| TABLEB_UK | 47M| 1228M| | 96480 (1)| 00:19:18 |
| 5 | TABLE ACCESS FULL | TABLEA | 80M| 1991M| | 243K (1)| 00:48:42 |
---------------------------------------------------------------------------------------------------------
In both tables, TABLEA and TABLEB, there is index on columns col1-col2-col3 as leading columns (TABLEB has few more columns in the index, but after these 3 columns).
Requirement is, I want to delete first 50 records in TABLEA, which does not exist in TABLEB.
I tried with various hints, but Oracle is always doing a full scan on one of the tables and index FFS on other. In some cases, Oracle did full scan on both tables and then deleted 50 records. Stats is up-to-date. Doing a full scan on tables with 80 million and 47 million rows is a bit too much for deleting 50 rows.
How I can make Oracle do
1) Read TABLEA row-by-row
2) for each row, check if it exists in TABLEB
3) If not exists, then delete row from TABLEA, else continue
4) Stop reading TABLEA after we have deleted 50 records
??
Thanks in advance