пятница, 13 июля 2018 г.

Delete through index and fullscan

From JL article

It’s not a well-known phenomenon, but Oracle uses a completely
different strategy for deleting through an index range/full scan from
the strategy it uses when deleting through a tablescan or index fast
full scan. For the tablescan/index fast full scan Oracle deletes a row
from the table then updates each index in turn before moving on to
delete the next row. For the index range scan / full scan Oracle
deletes a table row but records the rowid and the key values for each
of the indexes that will need to be updated – then carries on to the
next row without updating any indexes. When the table delete is
complete Oracle sorts all the data that it has accumulated by index,
by key, and uses bulk updates to do delayed maintenance on the
indexes. Since bulk updates result in one undo record and one redo
change vector per block updated (rather than one per row updated) the
number of redo entries can drop dramatically with a corresponding drop
in the redo and undo size.