How do we migrate to local indexing in Oracle

We are dealing with an Oracle table that can have 2k million records. The table is partitioned. It is a high ingest table.
We have the required indexing to find the data. The problem arises when we are truncating partitions and creating data as per requirement. The queries were becoming slower even when there was indexing.

Checking the status:

In Oracle, you can check the status of the index which will tell you whether the indexing is valid or not, which means it is usable or not. There is a way to check the status by writing SQL query.

select INDEX_NAME, STATUS, TABLE_OWNER, TABLE_NAME, UNIQUENESS 
from USER_INDEXES WHERE table_name = 'TABLE_NAME'

Output:

The status column says valid, which means it is usable. 😆

We discovered, the status as unusable. So there is always chance that truncation of a partition can trigger a need to rebuild the index.

Rebuilding an index can be a costly operation, depending on the size of the index, and the complexity of the index. It can take more CPU, memory, and disk space, also sometimes it needs additional disk space as temporary spaces are involved for operations. Also sometimes, it is time-consuming.

But if the index is unusable, obviously we need to rebuild the index, I think there is no other alternative.

What is a Local Index in Oracle?

In this case, we have decided to move to the local index from the global index. When working with large datasets in Oracle, partitioning tables can significantly improve query performance and manageability.
A local index is an index that is automatically partitioned in alignment with the table partitions. Unlike a global index, which spans all partitions, a local index contains separate index partitions corresponding to each table partition.

Key Characteristics:

• Each partition of a table has its corresponding index partition.
• If a table partition is dropped or truncated, its corresponding local index partition is also removed or truncated.
• Queries that target specific partitions can use the corresponding local index, improving performance.

Creating a local index

Our table is partitioned as I mentioned before.

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) LOCAL;

• LOCAL ensures that the index is partitioned in the same way as the table.
• The index will automatically have partitions p1, p2, and p3 matching the table’s partitions.

Additionally, you can use specific table space. TABLESPACE SPACE_NAME

When to Use Local Indexes?

Local indexes are beneficial in scenarios such as:

• Partition maintenance: Dropping or truncating a partition does not affect other partitions’ indexes, unlike global indexes.
• Partition pruning: Queries targeting specific partitions can efficiently use local indexes.
• Efficient parallel processing: Local indexes allow parallel queries to work on specific partitions independently.

Checking the status:

For local indexing also you can check the status. If you execute the query I mentioned it will give you some out with the index names. Let me show you:

Here it is showing as N/A,😮 😮 😮 sometimes developers get confused that the local index became unusable. But that is not the case. N/A means this does not apply to the local index. For the global index, the status should be coming as VALID/ INVALID.

The correct way to do this is:

select partition_name, status from user_ind_partitions 
where index_name='INDEX_NAME'

Output:

So it shows the status of each of the partitions, and it clearly shows it is usable 😍 😍, in most cases, the local index does not get corrupted even if we are doing truncation of partitions frequently. Which is part of the partition maintenance.

Rebuild local index

ALTER INDEX INDEX_NAME REBUILD PARTITION p1;

OR

ALTER TABLE TABLE_NAME
   MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;

Since there are multiple indexes on a partition, initially the dev team was looking for the unusable index by querying, that is why I like the second one as you don't have to search for the unusable indexes.

Conclusion

Local indexes are an essential tool when working with partitioned tables in Oracle. They improve performance, simplify maintenance, and enable better query optimization.
If I missed anything, let me know in the comments.