Indexing Partitioned Tables in Oracle

When you index a table, you can partition the index just as you partition the table itself, and this process works because indexes are separate database objects. Partitioning the index generates the same benefits as a partitioned table, improved performance, reduced maintenance time, and increased availability.
Many applications use a concept called equipartitioning to increase the total value of partitioned tables and indexes. With equipartitioning, you have the same partitions for an index as you have for its table, the same number, partitioning columns, and partition bounds. However, you can have different physical storage attributes even though you have the same partitions. This allows you to store the index and table in different tablespaces.
A partitioned table can have both partitioned and non-partitioned indexes on it.

The syntax for creating a partitioned index is very similar to the syntax for creating a partitioned table. You append partitioning keywords to the end of a standard CREATE INDEX statement. However, the syntax is different for each type of partitioned index.

Partitioned indexes are more complicated than partitioned tables because there are four different types:

1. Local prefixed
2. Local non-prefixed
3. Global prefixed
4. Global non-prefixed

You will learn more about these different kinds of partitioned indexes in the next two lessons. The next lesson discusses local partitioned indexes.