Partitioned Index FAQs with Examples

A Partitioned Index is an index that is divided into smaller, manageable pieces called partitions. These partitions may correspond to the table partitions or be independent of them. Partitioned indexes improve performance, simplify maintenance, and increase availability for large partitioned tables.

There are two main types:

• Local Partitioned Index
• Global Partitioned Index

3. What is a Partitioned Index?

Answer:

A partitioned index divides an index into multiple partitions to improve query performance and simplify maintenance.

Example:

CREATE INDEX idx_sales_date
ON sales(sale_date)
LOCAL;

4. What is a Local Partitioned Index?

A Local Index has one index partition for each table partition.

Sales Table Partition	Local Index Partition
P2023	IDX_P2023
P2024	IDX_P2024
P2025	IDX_P2025

Each table partition has its own corresponding index partition.

3. How do you create a Local Index?

CREATE INDEX idx_sales_date
ON sales(sale_date)
LOCAL;

Oracle automatically creates one index partition for each table partition.

4. What is a Global Partitioned Index?

A Global Index is independent of the table's partitioning.

Example:

CREATE INDEX idx_sales_amount
ON sales(amount)
GLOBAL;

A global index can span multiple table partitions.

5. When should you use a Local Index?

Use a Local Index when:

• Queries frequently access a single partition.
• Partition maintenance is common.
• You want easier index maintenance.

Example:

SELECT *
FROM sales
WHERE sale_date = DATE '2024-06-15';

Oracle can prune both the table partition and the corresponding local index partition.

6. When should you use a Global Index?

Use a Global Index when:

• Queries span multiple partitions.
• Searches are based on non-partition key columns.
• Unique constraints do not include the partition key.

Example:

SELECT *
FROM sales
WHERE sale_id = 50001;

7. Difference between Local and Global Indexes

Local Index	Global Index
Aligned with table partitions	Independent of table partitions
One index partition per table partition	Can span many table partitions
Easier maintenance	More maintenance after partition DDL
Supports partition pruning	Usually no partition alignment
Preferred for partitioned tables	Preferred for cross-partition queries

8. What is a Prefixed Local Index?

The partition key is the leading column of the index.

Example:

CREATE INDEX idx_sales
ON sales(sale_date, sale_id)
LOCAL;

Here, sale_date is the partition key and the first indexed column.

9. What is a Non-Prefixed Local Index?

The partition key is not the first indexed column.

CREATE INDEX idx_sales
ON sales(amount, sale_date)
LOCAL;

The index is still local, but the leading column is amount.

10. Can a Local Index be Unique?

Yes, only if the partition key is included in the unique key.

Example:

PRIMARY KEY (sale_id, sale_date)

Since sale_date is the partition key, Oracle can enforce uniqueness within each partition.

11. Why can't a Local Unique Index omit the partition key?

Example:

PRIMARY KEY (sale_id)

Suppose:

Partition	sale_id
P2023	100
P2024	100

Oracle would have to search all partitions to enforce uniqueness.

Including the partition key solves this problem.

12. Can a Global Index be Unique?

Yes.

CREATE UNIQUE INDEX idx_sales
ON sales(sale_id)
GLOBAL;

Oracle enforces uniqueness across the entire table.

13. What happens to Local Indexes after dropping a partition?

Example:

ALTER TABLE sales
DROP PARTITION p2023;

Oracle automatically removes the corresponding local index partition. No rebuild is required.

14. What happens to Global Indexes after dropping a partition?

Example:

ALTER TABLE sales
DROP PARTITION p2023;

The global index may become unusable unless you specify:

ALTER TABLE sales
DROP PARTITION p2023
UPDATE GLOBAL INDEXES;

15. How do you rebuild a Local Index partition?

ALTER INDEX idx_sales_date
REBUILD PARTITION p2024;

Only the specified index partition is rebuilt.

16. How do you rebuild a Global Index?

ALTER INDEX idx_sales_amount
REBUILD;

This rebuilds the entire global index.

17. How do you check index partitions?

SELECT
    index_name,
    partition_name,
    status
FROM user_ind_partitions;

This shows the status of each index partition.

18. What is Partition Pruning with Indexes?

Example:

SELECT *
FROM sales
WHERE sale_date = DATE '2024-08-15';

Oracle can:

• Prune the table partitions.
• Prune the matching local index partition.

This reduces I/O and improves performance.

19. What is Index Partition Pruning?

Suppose:

Table Partitions
P2023
P2024
P2025

Query:

SELECT *
FROM sales
WHERE sale_date = DATE '2024-06-01';

Oracle scans only:

• Table Partition: P2024
• Index Partition: P2024

Instead of scanning all partitions.

20. Common Interview Questions

• What is a partitioned index?
• Difference between Local and Global indexes?
• What is a prefixed local index?
• What is a non-prefixed local index?
• Why must a Local Unique Index include the partition key?
• What happens to local indexes after dropping a partition?
• What happens to global indexes after dropping a partition?
• How do you rebuild an index partition?
• What is index partition pruning?
• Which index type is better for partitioned tables?

Real-World Example

Suppose you have a partitioned sales table:

CREATE TABLE sales
(
    sale_id    NUMBER,
    sale_date  DATE,
    amount     NUMBER
)
PARTITION BY RANGE (sale_date)
(
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

Create a Local Index

CREATE INDEX idx_sales_date
ON sales(sale_date)
LOCAL;

Oracle creates one index partition for each table partition.

Create a Global Index

CREATE INDEX idx_sales_amount
ON sales(amount)
GLOBAL;

This index spans all table partitions.

Query Example

SELECT *
FROM sales
WHERE sale_date = DATE '2024-05-10';

Oracle can use:

• Partition pruning to access only the P2024 table partition.
• Index partition pruning to scan only the corresponding local index partition.

Key Point:
This combination significantly improves performance on large partitioned tables.