Hash partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and
IN predicates on the index partitioning key can efficiently use hash partitioned global indexes.
The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the statement in Example 4-5 creates a hash partitioned global index:
Example 4-5 Creating a hash partitioned global index
CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARTITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4);