How is a table UPDATE handled by a RDBMS?

Question

Suppose I have a table and an index on it

original simple table A
------------------------
rowid  |  id   name
123    |  1    A
124    |  4    G
125    |  2    R
126    |  3    P

index on A.id
-------------
id  rowid
1   123
2   125
3   126
4   124

At this point, I execute this DML statement

UPDATE A SET id = 5 WHERE id = 4

What exactly happens when this statement is executed?

a)

BEGIN
go to index
search for `id == 4` (B tree index generally)
find that `rowid = 124`
go to that location
update id in the table
come back (? I am not sure)
update the index
END

b)

BEGIN
go to index
search for `id == 4` (B tree index generally)
update the id value in index
find that `rowid = 124`
go to that location
update id in the table
END

c) Something else entirely happens

As this might be dependent on the database itself, how does it happen in Oracle?

Answer 1

To see the execution plan for your particular update statement:

1. Turn on tracing for your session
2. Run your PL/SQL
3. run tkprof on the trace file

Answer 2

From: http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/

"If Oracle uses a (B-tree) index 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"

If you did a detailed trace, the wait events show the file/block details for IO. From there it should be possible to determine the object (using DBA_EXTENTS) and so the order in which things are accessed.

That said, it is pretty academic and shouldn't affect how you code things.