Choosing Between Local Indexes and Global Indexes

B-tree indexes on partitioned tables can be global or local. With Oracle8i and earlier
releases, Oracle recommended that global indexes not be used in data warehouse
environments because a partition DDL statement (for example, ALTER TABLE ... DROP
PARTITION) would invalidate the entire index, and rebuilding the index is expensive.
In Oracle Database 10g, global indexes can be maintained without Oracle marking
them as unusable after DDL. This enhancement makes global indexes more effective
for data warehouse environments.
However, local indexes will be more common than global indexes. Global indexes
should be used when there is a specific requirement which cannot be met by local
indexes (for example, a unique index on a non-partitioning key, or a performance
requirement).

Bitmap indexes on partitioned tables are always local.

Posted in: Index