In the section ???Managing
Partitions??? later in this chapter, we will review the update index clause when you are performing
partition maintenance operations on partitioned indexes.
574 Oracle Database 11g DBA Handbook
Figure 16-2 shows the relationship between a partitioned global index and a partitioned table.
The number of partitions in the table may or may not be the same as the number of partitions in
the index.
Creating Hash-Partitioned Global Indexes As with range-partitioned global indexes, hashpartitioned
global index create statements share a syntax with hash-partitioned table create
statements. Hash-partitioned global indexes can improve performance in situations where a
small number of a nonpartitioned index??™s leaf blocks are experiencing high contention in an
OLTP environment. Queries that use either an equality or IN operator in the WHERE clause
can benefit significantly from a hash-partitioned global index.
NOTE
Hash-partitioned global indexes are new in Oracle 10g.
Building on our example using hash-partitioning for the table CUST, you can create a hashpartitioned
global index on the ZIP_CD column:
create index oe.cust_zip_cd_ix on oe.cust(zip_cd)
global partition by hash(zip_cd)
(partition z1 tablespace idx_1,
partition z2 tablespace idx_2,
partition z3 tablespace idx_3,
partition z4 tablespace idx_4,
partition z5 tablespace idx_5,
partition z6 tablespace idx_6,
partition z7 tablespace idx_7,
partition z8 tablespace idx_8);
FIGURE 16-2 Global partitioned index on a partitioned table
Chapter 16: Managing Large Databases 575
Note that the table CUST is partitioned using the CUST_NUM column, and it places its four
partitions in PRD01 through PRD04; this index partition uses the ZIP_CD column for the hashing
function and stores its eight partitions in IDX_1 through IDX_8.
Pages:
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854