Oracle Partitioned Index FAQs with examples

1. What is a Partitioned Index in Oracle?

A Partitioned Index is an index that is divided into smaller, manageable pieces called partitions.

Each partition contains a subset of the index data, which improves performance, manageability, and maintenance for very large tables.

Example:

CREATE INDEX idx_sales_date
ON sales(sale_date)
LOCAL;

2. Why do we use Partitioned Indexes?

Partitioned indexes are used to:

• Improve query performance on large tables
• Reduce index maintenance time
• Allow easier index management
• Improve availability
• Support partition pruning

3. What are the types of Partitioned Indexes in Oracle?

Oracle supports two main types:

• Local Partitioned Index
• Global Partitioned Index

4. What is a Local Partitioned Index?

A Local Index is an index where each index partition corresponds to a table partition.

The index partitions are automatically aligned with the table partitions.

Example:

Create a partitioned table:

CREATE TABLE sales (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER
)
PARTITION BY RANGE(sale_date)
(
  PARTITION sales_2024 VALUES LESS THAN
    (TO_DATE('01-JAN-2025','DD-MON-YYYY')),
  PARTITION sales_2025 VALUES LESS THAN
    (TO_DATE('01-JAN-2026','DD-MON-YYYY'))
);

Create a local index:

CREATE INDEX idx_sales_date
ON sales(sale_date)
LOCAL;

5. What are the advantages of Local Indexes?

Advantages:

• Easier maintenance
• Faster partition operations
• Independent partition management
• Supports partition pruning
• Less index maintenance during partition changes

6. When should you use Local Indexes?

Use local indexes when:

• The table is partitioned
• Data is managed by partitions
• Partition operations are frequent

Examples:

• Monthly sales tables
• Historical transaction tables
• Log tables

7. What is a Global Partitioned Index?

A Global Index is an index that is partitioned independently from the table partitions.

The index partitions do not have to match the table partitions.

Example:

CREATE INDEX idx_sales_amount
ON sales(amount)
GLOBAL PARTITION BY RANGE(amount)
(
  PARTITION p1 VALUES LESS THAN (10000),
  PARTITION p2 VALUES LESS THAN (50000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

8. What are the advantages of Global Indexes?

Advantages:

• Can index non-partition key columns
• Useful for queries across multiple partitions
• Provides flexible indexing strategies
• Good for OLTP applications

9. What is the difference between Local and Global Partitioned Indexes?

Local Index	Global Index
Index partitions match table partitions	Index partitions are independent
Easier maintenance	More complex maintenance
Best for partition management	Best for cross-partition queries
Supports partition pruning	Does not depend on table partitions

10. How do you create a Local Partitioned Index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name)
LOCAL;

Example:

CREATE INDEX idx_customer_id
ON customers(customer_id)
LOCAL;

11. How do you create a Global Partitioned Index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name)
GLOBAL PARTITION BY RANGE(column_name)
(
  PARTITION partition1 VALUES LESS THAN(value),
  PARTITION partition2 VALUES LESS THAN(value)
);

Example:

CREATE INDEX idx_amount_global
ON sales(amount)
GLOBAL PARTITION BY RANGE(amount)
(
  PARTITION p_low VALUES LESS THAN (10000),
  PARTITION p_high VALUES LESS THAN (MAXVALUE)
);

12. What is Partition Pruning?

Partition pruning allows Oracle to access only the required table partitions instead of scanning all partitions.

Example:

SELECT *
FROM sales
WHERE sale_date >= DATE '2025-01-01';

Oracle can access only the relevant partition.

13. Can a Partitioned Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_emp_unique
ON employees(employee_id)
LOCAL;

Note: A local unique index usually must include the partition key column to guarantee uniqueness across the entire table.

14. How do you rebuild a Partitioned Index?

Rebuild an entire index:

ALTER INDEX idx_sales_date
REBUILD;

Rebuild a specific partition:

ALTER INDEX idx_sales_date
REBUILD PARTITION sales_2025;

15. How do you view Partitioned Indexes?

View indexes:

SELECT index_name,
       table_name,
       partitioned
FROM user_indexes;

View index partitions:

SELECT index_name,
       partition_name,
       status
FROM user_ind_partitions;

16. How do you drop a Partitioned Index?

DROP INDEX idx_sales_date;

17. What are the disadvantages of Partitioned Indexes?

• More complex design
• Requires partitioning knowledge
• Additional administration
• May require more storage
• Global indexes need maintenance after partition operations

18. Interview Example: Monthly Sales Table

Create a partitioned table:

CREATE TABLE sales (
  sale_id   NUMBER,
  sale_month DATE,
  amount    NUMBER
)
PARTITION BY RANGE(sale_month)
(
  PARTITION jan_sales VALUES LESS THAN
    (DATE '2025-02-01'),
  PARTITION feb_sales VALUES LESS THAN
    (DATE '2025-03-01')
);

Create a local partitioned index:

CREATE INDEX idx_sales_month
ON sales(sale_month)
LOCAL;

Query:

SELECT *
FROM sales
WHERE sale_month = DATE '2025-01-15';

Oracle can use the relevant partition and its local index for faster access.

19. Real-World Example

A company stores billions of transaction records partitioned by year.

Table:

TRANSACTIONS
transaction_id
transaction_date
amount

Create a local index:

CREATE INDEX idx_transaction_date
ON transactions(transaction_date)
LOCAL;

Key Point:
Partitioned indexes help manage very large tables efficiently. Local indexes are best for partition-aligned maintenance, while global indexes are useful for broader query access.