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