What types of indexes can be created on a partitioned table?

My customer wanted to introduce partitioning on certain tables. I was asked me to give an overview of the different indexing options available after a table has been partitioned.

In short, there are four types of indexes to be considered for partitioned tables:

1. Local Partitioned Indexes
2. Global Partitioned Indexes
3. Global Non-partitioned Indexes
4. Bitmap Indexes



1. Local Partitioned Indexes

Local partitioned indexes always inherit the partitioning key from the table and are fully aligned with the table partitions. Oracle calls this concept "equipartitioning": "each partition of a local index is associated with exactly one partition of the table"

Local indexes can only be unique if the partitioning key is a part of the index key.

New partitions are added to local indexes only when you add a partition to the underlying table. Likewise, they are dropped only when you drop a partition from the underlying table. In other words, you cannot explicitly add a partition to a local index.

Two types of local indexes

There are two types of local indexes: Prefixed and non-prefixed.

Prefixed means that the table's partition key column is referenced first in the index, while a non-prefixed key does not.

Oracle states that non-prefixed local indexes are useful for historical databases, although I haven't had any expirence yet that would confirm this.

For an example of a unique local prefixed index along with some useful queries, click here
For an example of a local non-prefixed index along With some useful queries, click here

The obvious advantage of using local indexes is managebility and flexibilty. Any operation on a single partition will only affect the relevant index partition, and not the intire index as such.

2. Global Partitioned Indexes

Partitioned indexes whose patitions are not aligned with the table's partitions are called global partitioned indexes. In other Words, there is no relation between a table's partitions and a global index partition.

The keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions.

Global partitioned indexes give the flexibility to choose a partitioning key that is most optimal for performance. Oracle also claims that under some circumstances, having multiple segments for an index can be beneficial for performance.

Two types of global partitioned indexes

There are two types of Global Partitioned Indexes:

* Global Hash Partitioned indexes: improves performance by spreading out contention when the index is monotonically growing on the right side of the index. Using a hashing algorithm, Oracle creates the specified number of hash partitions and spread the index Blocks uniformly accross these.

For an example, see this post

* Global Range Partitioned indexes: adds flexibility: the degree of partitioning and the partitioning key are independent from the table's partitioning method.

For an example, see this post

Both of these types can be defined on any type of partitioned table (list, range, hash etc.).

3. Global Non-partitioned Indexes

These are simply normal indexes. The fact that the underlying table is partitioned does not matter. The index spans all partitions.

Global indexes can be unique.

4. Bitmap Indexes
You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.