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