Delete full table uses "index full scan index(unique)" instead of full table scan.
Oracle 11.2.0.3 Enterprise Edition on Oracle Data Appliance.
I have a table with 8 million rows (3.3G) which has two unique indexes on it. There is a job that deletes the whole table and repopulates it.
ie. delete tablename;
When looking at the plan for the delete it insists on doing
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 7471K| 106M| 25128 (1)| 00:05:02 |
| 1 | DELETE | TEST | | | | |
| 2 | INDEX FULL SCAN| TEST_I1 | 7471K| 106M| 25128 (1)| 00:05:02 |
-----------------------------------------------------------------------------------------
The thing is, if you hint the statement to force a full table scan the delete runs much faster.
My question is, why is the index full scan being used?