Index Partitioning

The rules for partitioning indexes are similar to those for tables:

• An index can be partitioned unless:

  • The index is a cluster index.

  • The index is defined on a clustered table.

• You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:

  • A partitioned table can have partitioned or nonpartitioned indexes.

  • A nonpartitioned table can have partitioned or nonpartitioned indexes.

• Bitmap indexes on nonpartitioned tables cannot be partitioned.

• A bitmap index on a partitioned table must be a local index.

However, partitioned indexes are more complicated than partitioned tables because there are three types of partitioned indexes:

• Local prefixed

• Local nonprefixed

• Global prefixed

Oracle Database supports all three types. However, there are some restrictions. For example, a key cannot be an expression when creating a local unique index on a partitioned table.

This section discusses the following topics:

• Local Partitioned Indexes

• Global Partitioned Indexes

• Summary of Partitioned Index Types

• The Importance of Nonprefixed Indexes

• Performance Implications of Prefixed and Nonprefixed Indexes

• Advanced Index Compression With Partitioned Indexes

• Guidelines for Partitioning Indexes

• Physical Attributes of Index Partitions