Oracle takes a rather simplistic view of the cost of deletes, which in most cases is fine. Small deletes, of say 1000 rows or less, really have no appreciable additional costs; larger deletes however can have additional overhead that isn’t taken into account by the optimizer when plans are generated. Let’s look at an example using Oracle 12.1.0.x, provided by Jonathann Lewis, to illustrate this.
Depending on the available execution path the cost of a delete can equal the cost of a tablescan, the cost of an index fast full scan, or the cost of an index range scan. The choice of which path to take is based, basically, on the cost of a ‘select rowid from … where …’ query. A table was created with 10 million rows of data with a primary key and three additional indexes; about half of the data was deleted. The execution path and apparent cost of this delete are shown below:
SQL>
SQL> delete from t1
2 where date_open < add_months(sysdate, -60);
5020333 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 3099179474
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 5019K| 129M| 13318 (1)| 00:00:04 |
| 1 | DELETE | T1 | | | | |
|* 2 | INDEX RANGE SCAN| T1_DT_OPEN | 5019K| 129M| 13318 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATE_OPEN"<ADD_MONTHS(SYSDATE@!,-60))
Statistics
----------------------------------------------------------
798 recursive calls
6185328 db block gets
13493 consistent gets
290899 physical reads
2354882072 redo size
878 bytes sent via SQL*Net to client
887 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
4 sorts (disk)
5020333 rows processed
SQL>
Look first at the execution plan; the cost of the delete is exactly the same as that reported for the index range scan executed to find the data. Again, for small deletes this doesn’t appear to be a problem, but looking at the statistics for the delete it can be seen that the redo generation is quite large, and for the number of rows processed the index maintenance would also be quite expensive. Remember that four indexes will be maintained by this delete operation, yet none of that cost appears in the execution plan. So what is Oracle doing during such a delete? For an index range scan Oracle deletes a row, then records the rowid and key values for bulk processing the index maintenance after the delete is finished. Not so with an index fast full scan or tablescan, where Oracle processes all of the indexes at the time the row is deleted from the table. The former situation results in a lower redo and undo cost since the redo and undo are generated for each block processed, not each row. Still, the index maintenance carries a rather large cost apparently unreported by Oracle.
The processing paths taken for the various execution paths can vary in execution time as well, with the index range scan taking the least amount of time when measured against an index fast full scan. For the deletes processed in this example the index fast full scan delete took approximately three times as long to complete. Looking at the consistent gets versus the db block gets it’s obvious that a lot of physical block reads were executed, due to Oracle moving around the table to find and delete the specified data, making it difficult to keep data buffered. This occurs for either of the index scan paths. The index fast full scan path, that processes the indexes at the time each row is deleted, causes even more physical read activity from the table, increasing execution time and as a result, the cost of performing such a delete.
Oracle chooses the path based on cost, and in Oracle 12.1.0.x one of those possible paths utilizes an index fast full scan. The cost is a driving factor, and when an index fast full scan is the ‘cheapest’ such a path can generate more work than either of the remaining paths (index range scan or table scan). Knowing this can help in hinting such deletes to favor either an index range scan or a table scan, to reduce the work such a delete can generate.
