views:

56

answers:

2

From this article about index organized tables,

Note that rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index on an ordinary table.

Why does rebuilding of an index on an ordinary table not require reading the base table?

+2  A: 

Quote from http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/indexes004.htm#ADMIN11734

When you rebuild an index, you use an existing index as the data source.

Andrew
@Andrew: But if the index is outdated, it contains wrong information, so how can it be used to update itself without referring to the table?
Amoeba
The index can not be out of date per se, it can have incorrect / inaccurate guesses of block locations, but the actual data it contains has to be accurate, consider the alternative, if indexes were allowed to be enabled but inaccurate, the query engine could never use them.
Andrew
+1  A: 

I believe this is because the index is normally used to rebuild itself, and how physical rowid's are used in the index.

An index on an ordinary table stores physical rowid's, but because an index-organized table is fluid in structure, a secondary index is only able to store a physical rowid guess.

When you rebuild an index on an ordinary table, it doesn't need to reference the source table for physical rowid's, it has them already in the index. But on an index-organized table, the rebuild has to review the physical rowid's again and is thus forced to scan the base table.

Joel Goodwin
@Joel: But if the ordinary table index is outdated, it contains wrong information, so how can it be used to update itself without referring to the table?
Amoeba
Rebuilding an index is more about restructuring it to be more efficient, not that the data is "wrong". In the case of an index-organised table there IS wrong data in the index, the physical rowid guesses which would have certainly drifted off target after a lot of updates to the table.
Joel Goodwin
Oracle's documentation here should read "rebuilding a usable index on an heap-organized table." Rebuilding an index (or partition/subpartition) of an unusable index requires rereading the base table.
Adam Musch