There are a few basic considerations that need to be taken into account while writing a delete query; access to the data needs to be optimized, which means the access path the optimizer chooses should be efficient:
- Full table scan: Use primary index column, PPI, if available, when doing a delete of a million rows.
- Index access: Use index, if available; this helps the optimizer identify the ROWIDs. The number of rows qualified depends on the number of rows selected based on the index.