Secondary Indexes on Index-Organized Tables (231)

Secondary index support on index-organized tables provides efficient access to
index-organized table using columns that are not the primary key nor a prefix of the
primary key.

Oracle constructs secondary indexes on index-organized tables using logical row
identifiers (logical rowids) that are based on the table's primary key. A logical rowid
includes a physical guess, which identifies the block location of the row. Oracle can
use these physical guesses to probe directly into the leaf block of the index-organized
table, bypassing the primary key search. Because rows in index-organized tables do
not have permanent physical addresses, the physical guesses can become stale when
rows are moved to new blocks.

For an ordinary table, access by a secondary index involves a scan of the secondary
index and an additional I/O to fetch the data block containing the row. For
index-organized tables, access by a secondary index varies, depending on the use and
accuracy of physical guesses:
■ Without physical guesses, access involves two index scans: a secondary index scan
followed by a scan of the primary key index.
■ With accurate physical guesses, access involves a secondary index scan and an
additional I/O to fetch the data block containing the row.
■ With inaccurate physical guesses, access involves a secondary index scan and an
I/O to fetch the wrong data block (as indicated by the physical guess), followed by
a scan of the primary key index.

索引組織表中的(二級索引)?
1. Oracle 為索引組織表建立二級索引時使用的是邏輯 rorwid，邏輯 rowid
是根據索引組織表的主鍵生成的。
Oracle 能夠根據邏輯 rowid 進行物理推測，以確定索引項在索引塊中的物理位置。
因此 Oracle
能夠繞過主鍵搜尋，透過物理推測直接訪問索引組織表的葉塊。由於索引組織表的資料行沒有固定的實體地址，
當索引項被移動到新的索引塊後，物理推測的結果會出現錯誤，此時 Oracle 仍需要執行主鍵搜尋。
2. 對一個常規表來說，透過間接索引訪問表資料意味著先掃描間接索引再獲取包含所需資料行的資料塊
3.
對於索引組織表來說，透過間接索引訪問表資料的步驟依據是否使用物理推測，及物理推測的準確度而有所不同
* 如不使用物理推測，資料訪問需要兩次索引掃描：首先掃描間接索引，再依據其結果掃描主鍵索引
* 如使用物理推測且推測結果準確，資料訪問需要首先掃描間接索引，再進行 I/O
操作獲取包含所需資料行的資料塊
* 如使用物理推測且推測結果不準確，資料訪問需要首先掃描間接索引，並執行 I/O
操作獲取了錯誤的資料塊，之後再進行主鍵索引掃描。

[@more@]