HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
ETL and the ORA-01502: index *** or partition of such index is in unusable state error, ORA-14063
ORACLE STUFFS
02-Nov-2016
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:

See: Rebuild unusable indexes in specified scope


Queries

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:

select count(1) from MASTER_PIECE M join DETAIL_PIECE D on D.A=M.A where M.A=1000 Plan hash value: 3459433560 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 76 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | HASH JOIN | | 1 | 8 | 76 (3)| 00:00:01 | |* 3 | TABLE ACCESS FULL| MASTER_PIECE | 1 | 4 | 38 (3)| 00:00:01 | |* 4 | TABLE ACCESS FULL| DETAIL_PIECE | 1 | 4 | 38 (3)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."A"="M"."A") 3 - filter("M"."A"=1000) 4 - filter("D"."A"=1000)

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

    alter session set SKIP_UNUSABLE_INDEXES=TRUE;

    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 / PL/SQL procedure successfully completed.

    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.

    SQL> Begin 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => USER 4 ,tabname => 'DETAIL_BUNCH' 5 ,partname => 'P1' 6 ,force => true 7 ,cascade => true 8 ,no_invalidate =>false 9 ); 10 End; 11 / PL/SQL procedure successfully completed.


    Constraints

    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

    Ludek Bob Jankovsky
    All Right Reserved © 2007, Designed by Bob Jankovsky