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:
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 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
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.
Oracle Database Administrator's Guide for information about guidelines for managing tables