Suppose I have a table and index:
create table t(x int, y varchar, primary key(x));
and that the only updates are "update t set y = ... where x = ?".
I understand that updating a row of t generates a new row version, and
that different transactions may see different versions of the same
row.
How does versioning work for the index?
- The update above does not update the index key. Does the index get
updated at all?
- If not, then how can an index lookup return the correct version of
selected rows?
This aspect of versioning has never been clear to me. Now there's a cost
issue involved, as I need to update every row in a large table, never
updating the index key. Will this run faster if I drop the index?
(Yes, I can run the experiment, but I'd like to understand the
fundamentals better.)
Jack Orenstein