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.

Mar 2, 2025 - 19:02

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.