14.4 Using Index-Organized Tables forPerformance 使用索引组织的表

14.4 Using Index-Organized Tables for Performance

原文：

An index-organized table differs from an ordinarytable in that the data for the table is held in its associated index. Changesto the table data, such as adding new rows, updating rows, or deleting rows,result only in updating the index. Because data rows are stored in the index,index-organized tables provide faster key-based access to table data forqueries that involve exact match or range search or both.

 

A parent/child relationship is an example of asituation that may warrant an index-organized table. For example, a memberstable has a child table containing phone numbers. Phone numbers for a memberare changed and added over time. In a heap-organized table, rows are insertedin data blocks where they fit. However, when you query the members table, youalways retrieve the phone numbers from the child table. To make the retrievalmore efficient, you can store the phone numbers in an index-organized table sothat phone records for a given member are inserted near each other in the datablocks.

 

In some circumstances, an index-organized table mayprovide a performance advantage over a heap-organized table. For example, if aquery requires fewer blocks in the cache, then the database uses the buffercache more efficiently. If fewer distinct blocks are needed for a query, then asingle physical I/O may retrieve all necessary data, requiring a smaller amountof I/O for each query.

 

Global hash-partitioned indexes are supported forindex-organized tables and can provide performance benefits in a multiuser OLTPenvironment. Index-organized tables are useful when you must store relatedpieces of data together or physically store data in a specific order.

 

 

译文：

索引组织的表与普通表的不同之处在于表的数据保存在相关的索引中。对表中数据的修改，比如增加新的行，更新已有行，或者删除行这些操作终究都只是更新索引的操作。因为数据行被存储在索引中，索引组织的表在查询时能提供更快的基于key的数据访问，无论是精确匹配的查询还是范围查询皆是如此。

 

一种父子关系的情况可能就需要索引组织的表。比如，一个成员表有一个子表，子表中包含成员的电话号码。随着时间的推移，成员的电话号码会改变和增加新的号码。在一个堆组织的表中，数据行被添加在它们可以被放进去的数据块中。但是，当查询成员表时，总是要从子表中获得电话号码。为了是获得电话号码的操作更加有效，可以将电话号码存储在索引组织的表中，这样一个特定成员的若干个电话号码会被存储在相邻的数据块中。

 

在某些情况下，索引组织的表会比堆组织的表提供更好的性能。比如，一个查询如果需要的缓存块少一些，那么数据块就能够更有效的利用缓冲块。如果一个查询需要的数据块少一些，可能一次I/O就能或得到所有需要的数据，这样会减少查询需要的I/O次数。

 

索引组织的表也支持全局哈希分区索引，这可以在多用户的OLTP环境中提供性能优势。当你必须把相关的数据都存储在一起或者需要数据物理上有序存储的时候，你可能需要索引组织的表。