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.