Tuning Updates

Consider the following update statement:

rem
rem     Script:         index_update_01.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2006
rem

update
        /*+ index(t1 t1_n1) */
        t1
set
        n1 = 0,
        n2 = n2 + 1,
        status = 'X'
where
        n1 > 100
and     status = 'A'
;

Did you realise that the access method that Oracle uses for an update can make a difference to the type of work done by your system ? And if the type of work is different, this could make a big difference to the amount of work done by your system ?

In recent tests on 8i, 9i, and 10g, the work done by an update depends on whether the update operates through a tablescan or an index. [Edit: the same is also true of a delete – see note 15 below]

If Oracle uses a (B-tree) index in ascending order (index_desc() doesn’t co-operate) to find the data to be updated, it postpones any (B-tree) index updates needed until the end of the update, then sorts the index keys (with their rowids) for the before and after values before applying bulk updates to the indexes. This bulk update strategy may reduce undo and redo generation – along with logical I/O and latch activity – but will expend extra resources on sorting.  For example, if your update affects three indexes, and you update 100 rows, you will see sorts = 3, rows sorted = 600 (3 * 2 * 100) in your session statistics (v$mystat).

If Oracle uses a tablescan to acquire the data to be updated, then it updates each index entry as it goes. This eliminates the need for sorting but doesn’t get any benefits of the undo and redo optimisations that pack multiple changes into a single record. I’ve also noticed from 10g onwards (and possibly it’s a bug since it doesn’t seem to happen in 8i or 9i) that a large tablescan update can generate a surprising number of redundant block cleanout records for the table blocks.

In general it seems that an indexed mechanism is likely to be more efficient than the tablescan – even when a tablescan would be more efficient for the equivalent query. This may be particularly effective if you can pick an index with a low clustering_factor (or, to expressing that in terms of the underlying data, if as you walk the index in order you find that the data is well clustered in the same order).

As always, though, you ought to test the impact a change in access method might have on your more interesting updates. Depending on your bottleneck, either option might be the more appropriate for your particular circumstances, notwithstanding any abstract argument about which is theoretically “more efficient”.

Footnote:

I’ve included specific references to B-tree indexes in the above. I haven’t yet checked what might happen with bitmap indexes – but normally you wouldn’t expect to be doing this type of update with bitmap indexes enabled anyway.

Footnote 2:

See comments 12 – 15, the same index-driven mechanism applies to delete statements.

Footnote 3:

If you have any B-tree indexes supporting foreign key constraints, or non-unique indexes supporting unique/pk constraint, the delayed maintenance optimisation will not apply to them (though it can still be used on any other B-tree indexes on the table).