ORA-01502: index or partition of such index is in unusable state
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 operation wants to update dependent indexes when you INSERT INTO some data, 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 state VALID, no more UNUSABLE state.
SQL> ALTER INDEX PAY_HIST_PK REBUILD;
Index altered.
Or rebuild it online:
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 with UNUSABLE state, because the operation needs to check any uniqueness violation.
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 such index from the table, then create it after data loading. For a normal unique 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 such unique index of a big table may take 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.