Index unusable: Skip_unusable_indexes oracle10g/oracle11g

Why indexes are going to unusable status?
When will the indexes goes for unusable state?
1.Any maintenance activity in partition table – Alter command
1.Truncate the table partition
2.Drop the table partition
3.Split Part/Sub Partition

2.Any maintenance activity in table – Table Reorganization.
1.Move the table.
2.Online redefinition

More details about online redefinition -
http://dbarajabaskar.blogspot.com/2010/04/online-redefintion-in-oracle-table.html

3.Oracle Import – While import the data with skip_unusable_indexes=y.

To improve the performance of import, we can unusable the indexes manually.
After import the data, we rebuild the indexes.

4.SQL LOADER – Using sqlldr to load the data using direct load.

5.User can manually unusable the indexes for performance improvement.

How skip_unusable_indexes works?

In oracle9i release, we don’t have skip_unusable_indexes parameter.
I have faced several issues regarding this.
Sometimes indexes status went to unusable state for above reasons.

While batch cycle were failed due to indexes unusable. Application team notified to DBA group,
DBA team rebuilds the indexes & asked to application team to rerun the batch cycle.

In oracle 10g/ oracle 11g provides skip_unusable_indexes=TRUE (default) feature.
If any index status is unusable in database its skip the unusable indexes and supporting the DML operations.

But index unusable status is affecting the database performance.
While running a query using index scan it will take less time compare as full table scan.

I have tested the below scenario in 11.1.0.7 version.

SQL> create user raja identified by raja;

User created.

SQL> grant connect,resource,dba to raja;

Grant succeeded.

Default parameter value is TRUE.

SQL> show parameter skip_unusable_indexes

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
skip_unusable_indexes boolean TRUE

SQL> create table raja.test as select * from scott.emp;

Table created.

SQL> create index raja.test_idx on raja.test(empno);

Index created.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA TEST_IDX VALID

SQL> select ename,job from raja.test where empno=7844;

ENAME JOB
---------- ---------
TURNER SALESMAN

Gather the statistics

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'RAJA' , tabname => 'TEST',cascade => true, estimate_percent => 100,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 4);

PL/SQL procedure successfully completed.

SQL> explain plan for select ename,job from raja.test where empno=7844;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 1389866015

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7844)

14 rows selected.

Query is going INDEX SCAN & COST is 2.

Now move table to same tablespace itself.

SQL> alter table raja.test move;

Table altered.

Now indexes status is unusable.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA TEST_IDX UNUSABLE

While index is unusable status, we are able to do select/DML operations. Because skip_unusable_indexes=true.

SQL> select ename,job from raja.test where empno=7844;

ENAME JOB
---------- ---------
TURNER SALESMAN

After index unusable, explain plan was changed. Now query is going to full table scan & cost is 3. Unusable indexes impacting the query performance.

SQL> explain plan for select ename,job from raja.test where empno=7844;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPNO"=7844)

13 rows selected.

Now we changed the parameter skip_unusable_indexes=FALSE & tested SELECT/DML operations failed.

SQL> alter system set skip_unusable_indexes=false;

System altered.

SQL> show parameter skip_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean FALSE

SQL> select ename,job from raja.test where empno=7844;
select ename,job from raja.test where empno=7844
*
ERROR at line 1:
ORA-01502: index 'RAJA.TEST_IDX' or partition of such index is in unusable state

SQL> insert into raja.test ( select * from raja.test);
insert into raja.test ( select * from raja.test)
*
ERROR at line 1:
ORA-01502: index 'RAJA.TEST_IDX' or partition of such index is in unusable state

SQL> delete from raja.test where empno=7844;
delete from raja.test where empno=7844
*
ERROR at line 1:
ORA-01502: index 'RAJA.TEST_IDX' or partition of such index is in unusable state

Now we changed the parameter skip_unusable_indexes=TRUE & tested SELECT/DML operations were running fine.

SQL> alter system set skip_unusable_indexes=true;

System altered.

SQL> select ename,job from raja.test where empno=7844;

ENAME JOB
---------- ---------
TURNER SALESMAN

SQL> insert into raja.test (select * from raja.test);

14 rows created.

SQL> commit;

Commit complete.

SQL> delete from raja.test where empno=7844;

2 rows deleted.

SQL> commit;

Commit complete.

*************************************************

Now we changed the parameter skip_unusable_indexes=FALSE .

SQL> alter system set skip_unusable_indexes=false;

System altered.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA TEST_IDX UNUSABLE

Rebuild the index

SQL> alter index raja.test_idx rebuild online;

Index altered.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA TEST_IDX VALID

SQL> insert into raja.test ( select * from raja.test);

SQL> commit;

Commit complete.

SQL> delete from raja.test where empno=7844;

2 rows deleted.

SQL> commit;

Commit complete.

*************************************************

Note:

If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

We only select the table while index status is unusable (for unique only).

Scenario:

We created the table with unique key. Currently parameter value is skip_unusable_indexes=true.

SQL> drop table raja.test;

Table dropped.

SQL> create table raja.test as select * from scott.emp;

Table created.

SQL> alter table raja.test add constraint empno_pk unique (empno);

Table altered.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA EMPNO_PK VALID

SQL> alter table raja.test move;

Table altered.

SQL> select owner, index_name, status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA EMPNO_PK UNUSABLE

SQL> select ename,job from raja.test where empno=7844;

ENAME JOB
---------- ---------
TURNER SALESMAN

SQL> delete from raja.test where empno=7844;
delete from raja.test where empno=7844
*
ERROR at line 1:
ORA-01502: index 'RAJA.EMPNO_PK' or partition of such index is in unusable state

SQL> insert into raja.test ( select * from raja.test);
insert into raja.test ( select * from raja.test)
*
ERROR at line 1:
ORA-01502: index 'RAJA.EMPNO_PK' or partition of such index is in unusable state

SQL> alter index raja.empno_pk rebuild;

Index altered.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
------------------------------ ------------------------------ --------
RAJA EMPNO_PK VALID

SQL> select ename,job from raja.test where empno=7844;

ENAME JOB
---------- ---------
TURNER SALESMAN

SQL> delete from raja.test where empno=7844;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into raja.test ( select * from raja.test);
insert into raja.test ( select * from raja.test)
*
ERROR at line 1:
ORA-00001: unique constraint (RAJA.EMPNO_PK) violated

Which situation user manually unusable the index?

Scenario:

Sometimes user loads the bulk data into table. To improve the performance they change unusable the indexes.

SQL> drop table raja.test;

Table dropped.

SQL> show parameter skip

NAME TYPE VALUE
------------------------------------ ----------- ---------------
skip_unusable_indexes boolean TRUE

SQL> create table raja.test as select * from dba_objects;

Table created.

SQL> create index raja.test_idx on raja.test(object_id);

Index created.

SQL> select owner, segment_name,bytes from dba_segments where owner='RAJA';

OWNER SEGMENT_NAME BYTES
---------- -------------------- ----------
RAJA TEST_IDX 2097152
RAJA TEST 9437184

Truncate the table

SQL> truncate table raja.test;

Table truncated.

SQL> show parameter skip

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
skip_unusable_indexes boolean TRUE

SQL> alter system set skip_unusable_indexes=false;

System altered.

Unusable the index

SQL> alter index raja.test_idx unusable;

Index altered.

SQL> select owner, segment_name,bytes from dba_segments where owner='RAJA';

OWNER SEGMENT_NAME BYTES
---------- -------------------- ----------
RAJA TEST_IDX 65536
RAJA TEST 65536

SQL> select owner, index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
---------- ------------------------------ --------
RAJA TEST_IDX UNUSABLE

SQL> show parameter skip

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
skip_unusable_indexes boolean TRUE

Load the data after index unusable

SQL> insert into raja.test (select * from dba_objects);

69326 rows created.

SQL> commit;

Commit complete.

SQL> select owner, segment_name,bytes from dba_segments where owner='RAJA';

OWNER SEGMENT_NAME BYTES
---------- -------------------- ----------
RAJA TEST_IDX 65536  Index segment is not grow
RAJA TEST 9437184

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
---------- ------------------------------ --------
RAJA TEST_IDX UNUSABLE

SQL> alter index raja.test_idx rebuild;

Index altered.

SQL> select owner,index_name,status from dba_indexes where owner='RAJA';

OWNER INDEX_NAME STATUS
---------- ------------------------------ --------
RAJA TEST_IDX VALID

SQL> select owner, segment_name,bytes from dba_segments where owner='RAJA';

OWNER SEGMENT_NAME BYTES
---------- -------------------- ----------
RAJA TEST 9437184
RAJA TEST_IDX 2097152  after rebuild index size grown

If any index status is going to unusable we don’t know & also it affecting the database performance.

In oracle 10g/11g, I suggest to setup the monitor index unusable script is better.

I Hope this article helped to you. Suggestions are welcome.

Best Regards
RajaBaskar Thangaraj
www.dbarajabaskar.blogspot.com
Mail me @