How does index rebuilding happen after a table is updated?

Question

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

updated simple table A
----------------------

rowid   id  name
123      1    A
124      5    G
125      2    R
126      7    P

Assuming that the index did not update itself when the table was being updated, how will the index update happen now?

Will it be rebuild from scratch? Or will it somehow use the old index?

Answer 1

The index would have to be marked unusable / disabled to not update when the row was updated, when it is re-enabled / marked usable, it would be built from scratch since at that point the contents of the index are useless, but the schema of the index can be re-used.

This is a different scenario to your other question, which in the other situation it was rebuilding an index that was still enabled.

Answer 2

If you want to force an Index rebuild you can use what I have below:

ALTER INDEX my_index_name REBUILD WITH ONLINE = ON

But Andrew is right, it should update automatically.

I would recommend reading this article, it's one of the best i've seen on indexes.

Here is an excerpt from the article regarding index updates:

Anytime that a row is changed, those changes must be made not only in the base table (clustered index or heap) but also in any index that contains the columns that were affected by the change. This applies to insert, update and delete operations.

Answer 3

You are asking a lot of questions about Oracle indexes. Undoubtedly the best resource for understanding these objects is Richard Foote's blog. Seriously, he blogs about nothing else (give or take the occasional post on David Bowie, but we all have our foibles). Check it out.

In the current context you should read his presentation: Oracle B-Tree Index Internals:Rebuilding The Truth.

Answer 4

Your question is DBMS dependent.

In most situations, the rowid is a surrogate for the address of the row, in database space. Some DBMSes store the actual address in the index, and not the rowid, even if all you can see is a rowid. If a DBMS stores a rowid in an index, some mechanism still has to translate that into an address before direct retrieval of the row can be done.

In almost any DBMS, the update to the table and the update to the index will be done in the context of the same transaction. That means that it doesn't matter to you whether the index gets updated before the table, or the table gets updated before the index.

For the only DBMS whose internals I know, index entries are updated on a row by row basis, immediately after the update of the row itself. And the index entries contain row addresses, not row ids.

I never had to worry about this in practice, because by the end of the transaction it's all been done anyway.

In none of these cases would I call it "index rebuilding". It's really index update. "Index rebuilding" suggests a massive rewrite.