Benefits of Index-Organized Tables (229)

Index-organized tables provide faster access to table rows by the primary key or any
key that is a valid prefix of the primary key. Presence of nonkey columns of a row in
the B-tree leaf block itself avoids an additional block access. Also, because rows are
stored in primary key order, range access by the primary key (or a valid prefix)
involves minimum block accesses.

In order to allow even faster access to frequently accessed columns, you can use a row
overflow segment (as described later) to push out infrequently accessed nonkey
columns from the B-tree leaf block to an optional (heap-organized) overflow segment.
This allows limiting the size and content of the portion of a row that is actually stored
in the B-tree leaf block, which may lead to a higher number of rows in each leaf block
and a smaller B-tree.

Unlike a configuration of heap-organized table with a primary key index where
primary key columns are stored both in the table and in the index, there is no such
duplication here because primary key column values are stored only in the B-tree
index.

Because rows are stored in primary key order, a significant amount of additional
storage space savings can be obtained through the use of key compression.

Use of primary-key based logical rowids, as opposed to physical rowids, in secondary
indexes on index-organized tables allows high availability. This is because, due to the
logical nature of the rowids, secondary indexes do not become unusable even after a
table reorganization operation that causes movement of the base table rows. At the
same time, through the use of physical guess in the logical rowid, it is possible to get
secondary index based index-organized table access performance that is comparable to
performance for secondary index based access to an ordinary table.

索引组织表的优点
1. 减少了访问数据的磁盘 ，由于数据是按主键排序的，对索引表主键范围扫描时可访问更少的块
2. 可将不常访问的非主键列从B树叶子块挤至常规行溢出段 ，这样可以减少平衡树叶块中为每行存储的数据，
从而使每个叶块可以容纳更多行，并使平衡树更小。
3. 索引组织表只存储重复的键值于B树索引中 ，而不会还存储在表中
4. 利于使用键压缩来节约存储空间
5. 由于secondary索引基于逻辑 rowid，即便索引基表的重组操作导致其中的数据行发生移动，间接索引也不会失效

[@more@]

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

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