11G OCM Use partitioned indexes

References

Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1)

• Chapter 2, "Partitioning Concepts"
  • Basics of Partitioning
  • Overview of Partitioned Indexes
    • Local Partitioned Indexes
    • Global Partitioned Indexes
      • Global Range Partitioned Indexes
      • Global Hash Partitioned Indexes
      • Maintenance of Global Partitioned Indexes
    • Global Non-Partitioned Indexes
    • Miscellaneous Information about Creating Indexes on Partitioned Tables
    • Partitioned Indexes on Composite Partitions

Overview

The objective from the 11G OCM Upgrade Exam is:

Use partitioned indexes

This is a very broad objective. In order to reduce the scope of my study, I could read this objective in light of the following two (2) objectives from an earlier module in the Exam objectives:

• Configure and use parallel execution for queries
• Create partitioned tables (including reference and interval partitioning)

Reading Notes

Overview of Partitioned Indexes

From Overview of Partitioned Indexes,

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.
2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.
4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

Local Partitioned Indexes

From Local Partitioned Indexes,

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

Global Partitioned Indexes

From Global Partitioned Indexes,

Oracle offers two types of global partitioned indexes: range partitioned and hash partitioned.

Global Range Partitioned Indexes

From Global Range Partitioned Indexes,

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

Global Hash Partitioned Indexes

From Global Hash Partitioned Indexes,

Global hash partitioned indexes improve 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.

Maintenance of Global Partitioned Indexes

From Maintenance of Global Partitioned Indexes,

These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

• The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
• The index doesn't have to be rebuilt after the operation.

Global Non-Partitioned Indexes

From Global Non-Partitioned Indexes,

Global non-partitioned indexes behave just like a non-partitioned index.

Miscellaneous Information about Creating Indexes on Partitioned Tables

From 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.

Partitioned Indexes on Composite Partitions

From Partitioned Indexes on Composite Partitions,

Here are a few points to remember when using partitioned indexes on composite partitions:

• Subpartitioned indexes are always local and stored with the table subpartition by default.
• Tablespaces can be specified at either index or index subpartition levels.