14.3 Using Partitioned Indexes for Performance 使用分区索引

时间:2022-09-17 23:22:31

原文:

Similar to partitioned tables, partitioned indexesimprove manageability, availability, performance, and scalability. They caneither be partitioned independently (global indexes) or automatically linked toa table's partitioning method (local indexes).

 

Oracle Database supports both range and hashpartitioned global indexes. In a range partitioned global index, each indexpartition contains values defined by a partition bound. In a hash partitionedglobal index, each partition contains values determined by the Oracle Databasehash function.

 

The hash method can improve performance of indexeswhere a small number leaf blocks in the index have high contention in multiuserOLTP environment. In some OLTP applications, index insertions happen only atthe right edge of the index. This situation could occur when the index isdefined on monotonically increasing columns. In such situations, the right edgeof the index becomes a hotspot because of contention for index pages, buffers,latches for update, and additional index maintenance activity, which results inperformance degradation.

 

With hash partitioned global indexes index entries arehashed to different partitions based on partitioning key and the number ofpartitions. This spreads out contention over number of defined partitions,resulting in increased throughput. Hash-partitioned global indexes wouldbenefit TPC-H refresh functions that are executed as massive PDMLs into hugefact tables because contention for buffer latches would be spread out overmultiple partitions.

 

With hash partitioning, an indexentry is mapped to a particular index partition based on the hash valuegenerated by Oracle Database. The syntax to create hash-partitioned globalindex is very similar to hash-partitioned table. Queries involving equality and IN predicateson index partitioning key can efficiently use global hash partitioned index toanswer queries quickly.

 

译文:

与分区表相似,分区索引提高了可管理性,可用性,性能和灵活性。他们既可以独立分区(全局索引),也可以自动连接到表的分区方法(本地索引)。

 

Oracle数据库同时支持范围(range)分区和哈希分区两种全局索引。在范围分区全局索引中,每一个索引分区中包含分区边界所定义的值。在哈希分区全局索引中,每一个分区包含Oracle数据库哈希函数所决定的值。

 

在多用户的OLTP环境中,索引的少数叶子节点出现高度竞争的情况下,哈希方法可以提高索引性能。在一些OLTP应用中,索引的插入操作只集中在所有的右半边。当索引建立在单调递增的列上就会出现这种情况。在这种情况下,索引的右半边会竞争索引页、缓冲区、更新锁等资源而成为热点,进而引起性能的下降。

 

在哈希分区全局索引中,索引项根据分区key和分区数目被分到不同的分区中。这样做把竞争分散到多个分区中,提高了吞吐量。哈希分区全局索引会对TPC-H刷新函数有利,因为大量的缓冲锁(buffer latches)竞争分散到多个分区中了。

 

使用哈希分区时,索引条目根据Oracle生成的哈希值被映射到相应的索引分区。创建哈希分区全局索引的的方法和创建哈希分区表类似。在索引分区key上应用等号或者IN操作符的查询可以使用全局哈希分区索引来提高效率。