One of the internal mechanisms of IOT.

When was the IOT’s PK column(s) updated, what will happen to the secondary index?

In the oracle world, IOT is not frequently used. Actually, I don’t know the reason. Because IOT can provide good peformance in many cases. Of course, IOT has some restrictions. For example, it can’t use direct load and so on. Anyway, this time we reveal one of the mechanisms of IOT.

As you already know, IOT can have a seconary index. And the leaf blocks of the seconary index include index key column, PK column(s) and physical guess (= Data Block Address). So, to find the proper data block, oracle initially uses a physical guess. If the physical guess isn’t correct, use the PK column(s) in secondary index.

If so, when was the IOT’s PK column(s) updated, what will happen to the secondary index? That is a very simple question. Maybe the contents of the secondary index will be changed. Let’s prove that.