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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-983119/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-983119/