Global vs Local Partitioned Indexes

Compare the management overhead and query performance of index partitioning strategies.

In this lesson, you will learn how choosing between global and local partitioned indexes impacts the balance between query speed and maintenance overhead.

When we partition a large table, we split its data into smaller, manageable physical segments. However, we also need to decide how to index those segments.

Local Partitioned Indexes

A local index is "aligned" with the table partitions. If your table has 10 partitions, the local index also has 10 individual indexes, one for each partition.

Management Overhead: Very low. When you perform maintenance tasks like dropping or truncating an old partition, the index partition associated with it is automatically dropped. You don't have to rebuild the entire index.
Query Performance: Excellent for queries that include the partition key in their WHERE clause. The database knows exactly which partition to look in, and it only scans the corresponding local index segment.

Global Partitioned Indexes

A global index is a single index structure that spans all partitions of the table. It is not tied to the physical layout of the table partitions.

Management Overhead: High. Because the index is a single global structure, maintenance operations like dropping a partition usually render the index "unusable," forcing you to rebuild it. This can be time-consuming for massive tables.
Query Performance: Superior for queries that do not include the partition key in the WHERE clause. Since the index is global, it can quickly locate a row across the entire table without scanning every individual partition segment.

Choosing Your Strategy

Consider this simple comparison:

-- Local Index: Partition-aware, easy to maintain
CREATE INDEX idx_local_date ON Orders (order_date) LOCAL;

-- Global Index: Not partition-aware, high-performance for non-key lookups
CREATE INDEX idx_global_id ON Orders (order_id) GLOBAL;

If your application frequently performs bulk maintenance (like daily partition rotations), Local Indexes save you from constant index rebuilding. If your users frequently search by a column that is not part of your partition scheme, Global Indexes provide the necessary speed by indexing the entire dataset in one structure.

Key Takeaway: Use Local Indexes for effortless maintenance and partition-key queries; use Global Indexes when you need to optimize search performance across partitions at the cost of higher maintenance overhead.