Especially in huge ETL processes we often increase efficiency of the process by setting some indexes on the target table UNUSABLE.
That way we speed-up active operations such as inserts and merges into the table with unusable indexes, but the ORA-01502 error can back-stab us in some phases of the process.
We should be worried about reading operations what we can slow down (sometimes even freeze) by lack of the unusable index, but most of ETL operations are provided in an exclusive time period. By the other hand many concurrent ETL processes and even most overall analytical queries do not need indexes for its work.
However, time after time it happens, the error mentioned above appears in the process.
One possible reason is that these indexes are UNUSABLE unintentionally.
In that case you have to assure your indexes are in USABLE state, for example using following script:
Most queries wont be affected by unusable indexes. You can see it on following example plan.
We will use it both with usable and unusable indexes and we will check how the execution plan would be affected.
Select count(1) from MASTER_PIECE M join DETAIL_PIECE D on D.A=M.A
where M.A=1000
Plan hash value: 1287128965
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 39 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 8 | 39 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | MASTER_PIECE | 1 | 4 | 38 (3)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | MASTER_DETAIL_FK | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("M"."A"=1000)
5 - access("D"."A"=1000)
As you can notice, the MASTER_DETAIL_FK index has been used while executing the query.
Now we will make the index unusable:
Alter index MASTER_DETAIL_FK unusable;
The execution plan will be affected following way:
You can notice the plan has changed and now the index had not been used.
Even after we tried to enforce a nested loop plan by a hint, it did not slip to use the index.
The problem appeared when we tried to force index usage by a hint
/*+INDEX(d)*/
select /*+index(D)*/ count(1) from MASTER_PIECE M join DETAIL_PIECE D on D.A=M.A where M.A=1000;
ERROR at line 1:
ORA-01502: index 'EXT94813.MASTER_DETAIL_FK' or partition of such index is in unusable state
So the /*+INDEX ... was the only situation of queries causing investigated error. That is important to know using such a type of hints in queries.
The error appeared despite the SKIP_UNUSABLE_INDEXES had been set to TRUE or FALSE!
Active operations - MERGE
By documentation you can work actively with unusable indexes when SKIP_UNUSABLE_INDEXES=TRUE. Otherwise, it would cause the above error.
The situation with SKIP_UNUSABLE_INDEXES=FALSE is way more volatile:
alter session set SKIP_UNUSABLE_INDEXES=FALSE;
MERGE INTO DETAIL_PIECE T
USING DETAIL_PIECE S
ON (T.A=S.A)
WHEN MATCHED THEN UPDATE SET T.B=S.B;
99999 rows merged.
By the documentation we had changed data with no tolerance set for unusable indexes and no error appeared.
The error wont appear when:
No modification appears on these indexed columns.
No changes on index are necessary.
In our case the modified column is indexed, but I update it to the same value. So despite I generate a change in the table and redo logs, but as nothing really changed, the error did not appear.
That can explain why the error either appears or does not in various similar cases, what makes it harder with testing.
MERGE INTO DETAIL_PIECE T
USING DETAIL_PIECE S
ON (T.A=S.A)
WHEN MATCHED THEN UPDATE SET T.B=S.B+1;
ERROR at line 1:
ORA-01502: index 'EXT94813.MASTER_DETAIL_X' or partition of such index is in unusable state
In this case the indexed column has been really changed and that caused the error.
The situation can be solved by SKIP_UNUSABLE_INDEXES=TRUE parameter.
alter session set SKIP_UNUSABLE_INDEXES=TRUE;
MERGE INTO DETAIL_PIECE T
USING DETAIL_PIECE S
ON (T.A=S.A)
WHEN MATCHED THEN UPDATE SET T.B=S.B+1;
99999 rows merged.
Analyze table
Another place where unusable indexes cause the error is gathering of statistics.
When you gather statistics on a table cascade way, it does not tolerate any unusable indexes however the SKIP_UNUSABLE_INDEXES is set. You either have to all indexes usable while computing statistics or suppress the CASCADE option of the computation.
SQL> analyze table DETAIL_BUNCH compute statistics;
analyze table DETAIL_BUNCH compute statistics
*
ERROR at line 1:
ORA-01502: index 'EXT94813.MASTER_DETAIL_BUNCH_FK' or partition of such index is in unusable state
Using DBMS_STATS on non_partitioned table results in the same error, but it can be suppressed with SKIP_UNUSABLE_INDEXES parameter.
alter session set SKIP_UNUSABLE_INDEXES=FALSE;
SQL> Begin
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => USER
4 ,tabname => 'DETAIL_PIECE'
5 ,partname => null
6 ,force => true
7 ,cascade => true
8 ,no_invalidate =>false
9 );
10 End;
11 /
Begin
*
ERROR at line 1:
ORA-01502: index 'EXT94813.MASTER_DETAIL_FK' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 34707
ORA-06512: at line 2
Anyway, the cascade option of DBMS_STATS copes well with the problem on local indexes of partitioned tables even with the cascade option.
The SKIP_UNUSABLE_INDEXES changes nothing about that behaviour.
Another important chapter is setting UNUSABLE indexes supporting constraints. While enabling constraints with UNUSABLE index what is supposed to support it, the ORA-14063: Unusable index exists on unique/primary constraint key error appears.
alter table DETAIL_PIECE add constraint MASTER_DETAIL_FK unique (A) using index MASTER_DETAIL_FK;
alter table DETAIL_PIECE modify constraint MASTER_DETAIL_FK disable;
alter index MASTER_DETAIL_FK unusable;
alter table DETAIL_PIECE modify constraint MASTER_DETAIL_FK enable;
SQL> alter table DETAIL_PIECE modify constraint MASTER_DETAIL_FK enable;
alter table DETAIL_PIECE modify constraint MASTER_DETAIL_FK enable
*
ERROR at line 1:
ORA-14063: Unusable index exists on unique/primary constraint key
Worse, if you have an UNUSABLE index under enabled constraint, any active operation such as INSERT causes the error too despite on the SKIP_UNUSABLE_INDEXES setup.
alter session set SKIP_UNUSABLE_INDEXES=TRUE;
alter table DETAIL_PIECE modify constraint MASTER_DETAIL_FK enable;
alter index MASTER_DETAIL_FK unusable;
insert into DETAIL_PIECE values(-1,-1);
SQL> insert into DETAIL_PIECE values(-1,-1);
insert into DETAIL_PIECE values(-1,-1)
*
ERROR at line 1:
ORA-01502: index 'EXT94813.MASTER_DETAIL_FK' or partition of such index is in unusable state