1) Local partitioned index:
- Index is partitioned as the same manner as table was partitioned.
- Each partition of a local index corresponds to one and only one partition of the underlying table.
- The index is partitioned with a different partitioned key than the table is partitioned.
- This one is identical to an index on a non-partitioned table. The index structure is not partitioned.
Miscellaneous Information about Creating Indexes on Partitioned Tables
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.
Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.
Using Partitioned Indexes in OLTP Applications
Here are a few guidelines for OLTP applications:
-
Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.
-
Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.
-
Hash-partitioned global indexes offer better performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
Using Partitioned Indexes in Data Warehousing and DSS Applications
Here are a few guidelines for data warehousing and DSS applications:
-
Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.
-
Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.
