How to Resolve ORA-01502: index or partition of such index is in usable state.

  • by

ORA-01502

ORA-01502 means that the index that the optimizer want to use is in UNUSABLE state, you can either rebuild it or work around it. Let's see a case that can reproduce the error.

SQL> TRUNCATE TABLE PAY_HIST;

Table truncated.

SQL> ALTER INDEX PAY_HIST_PK UNUSABLE;

Index altered.

SQL> INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK;
INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK
*
ERROR at line 1:
ORA-01502: index 'FINAPP.PAY_HIST_PK' or partition of such index is in unusable state

In this case, we truncated the main table, deliberately made the index unusable and then restore it from a backup table. Eventually, we saw ORA-01502.

This is because the optimizer want to use the index for your INSERT INTO SELECT statement, but it found the index is broken (UNUSABLE).

Solutions

1. Rebuild Index

The formal way to solve ORA-01502 is to rebuild the index and make the index VALID, no more UNUSABLE.

SQL> ALTER INDEX PAY_HIST_PK REBUILD;

Index altered.

Or this:

SQL> ALTER INDEX PAY_HIST_PK REBUILD ONLINE;

Index altered.

But the drawback is that it slows down the data loading very severely, especially the table is big. So this is not what we want.

2. SKIP_UNUSABLE_INDEXES

The second solution is to make the unusable index to be skipped by setting SKIP_UNUSABLE_INDEXES to TRUE before doing bulk data loading.

SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

Session altered.

Usually, SKIP_UNUSABLE_INDEXES works, but not for unique indexes.

SQL> INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK;
INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK
*
ERROR at line 1:
ORA-01502: index 'FINAPP.PAY_HIST_PK' or partition of such index is in unusable state

So we need to work around unique indexes.

3. Drop Index to Workaround ORA-01502

Ultimately, we can drop the blocking index from the table, then create it after data loading. For a normal index, we can just drop it. But in this case, dropping a primary key index needs more steps.

Drop Primary Key Index

First of all, we disable the primary key constraint.

SQL> ALTER TABLE FINAPP.PAY_HIST DISABLE CONSTRAINT SYS_C0082906;

Table altered.

Then drop the unique index.

SQL> DROP INDEX FINAPP.PAY_HIST_PK;

Index dropped.

Data Loading

Now, we can insert into the table.

SQL> INSERT /*+ APPEND */ INTO PAY_HIST SELECT * FROM PAY_HIST_BAK;

3128041760 rows created.

Create Primary Key Index

We need to get the primary key back. First, create the original index.

SQL> CREATE UNIQUE INDEX FINAPP.PAY_HIST_PK ON FINAPP.PAY_HIST (PAY_ID, PAY_CUST_ID, ISSUE_DATE) ONLINE;

Index created.

Of course, creating the unique index takes some times to complete, but it's worth it overall.

The last step, we enable the primary key constraint.

SQL> ALTER TABLE FINAPP.PAY_HIST ENABLE CONSTRAINT SYS_C0082906;

Table altered.

Leave a Reply

Your email address will not be published. Required fields are marked *