Guidelines for Partitioning Indexes

When deciding how to partition indexes on a table, consider the mix of applications
that need to access the table. There is a trade-off between performance on the one hand
and availability and manageability on the other. Here are some of the guidelines you
should consider:
■ For OLTP applications:
– Global indexes and local prefixed indexes provide better performance than
local nonprefixed indexes because they minimize the number of index
partition probes.
– Local indexes support more availability when there are partition or
subpartition maintenance operations on the table. Local nonprefixed indexes
are very useful for historical databases.
■ For DSS applications, local nonprefixed indexes can improve performance because
many index partitions can be scanned in parallel by range queries on the index
key.
For example, a query using the predicate "acctno between 40 and 45" on the table
checks of Figure 5–4 on page 5-30 causes parallel scans of all the partitions of the
nonprefixed index ix3. On the other hand, a query using the predicate deptno
BETWEEN 40 AND 45 on the table deptno of Figure 5–5 on page 5-31 cannot be
parallelized because it accesses a single partition of the prefixed index ix1.
■ For historical tables, indexes should be local if possible. This limits the impact of regularly scheduled drop partition operations.
■ Unique indexes on columns other than the partitioning columns must be global
because unique local nonprefixed indexes whose key does not contain the
partitioning key are not supported.

Posted in: Index,Partitioning