Ordinarily, you can update columns with the benefit of an indexed scan.
When you want to update the key column or columns on which an index is based,
the index scan is only be used in some circumstances. In
general, the optimizer chooses an indexed update if
there is an EQUAL predicate, as in the following examples:
UPDATE T1 SET c1 = c1 + 10 WHERE c1 = 20
UPDATE T1 SET c1 = 20 WHERE c1 = 10
|
However, the optimizer chooses a table scan in updating
the key column if the EQUAL predicate
is combined with an OR factor:
UPDATE T1 SET c1 = c1 + 10 WHERE c1 = 10 OR c1 = 20
|
The optimizer also chooses a table scan in updating
the key column
if there is anything other than EQUAL (for example, GREATER THAN)
in the predicate involving the index column:
UPDATE T1 SET C1 = C1 + 10 WHERE C1 >= 20
|
