Guidelines for Partitioning Indexes

When deciding how to partition indexes on a table, consider the mix of applications that must access the table. There is a trade-off between performance and availability and manageability. Here are some 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 shown in the figure in Local Nonprefixed Indexes 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 shown in the figure in Local Prefixed Indexes 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 effect of regularly scheduled drop partition operations.

  • Unique indexes on columns other than the partitioning columns must be global because unique local nonprefixed indexes whose keys do not contain the partitioning key are not supported.

  • Unusable indexes do not consume space.

    See Also:

    Oracle Database Administrator's Guide for information about guidelines for managing tables