Tuesday, May 24, 2011

skip_unusable_indexes (Part 2)

This example shows the effect of the skip_unusable_indexes parameter in an Oracle 10 database. First create a test table, give it an index and make it unusable:
SQL> conn /
SQL> set linesize 50
SQL> create table andrews_table
  2  as select * from dba_users
  3  /
Table created.
SQL> create index andrews_index
  2  on andrews_table(username)
  3  /
Index created.
SQL> alter index andrews_index unusable
  2  /
Index altered.
Choosing to skip an unusable index may produce a less efficient execution plan (i.e. less efficient than it would be if you decided to rebuild the index and start using it again). So Oracle tries to warn you by writing a message in the alert log when the index becomes unusable:
Mon May 23 18:04:23 2011
Index ORACLE.ANDREWS_INDEX or some [sub]partitions of the index have been marked unusable
If you leave skip_unusable_indexes set to true, you can access the table:
SQL> select count(*) from andrews_table
  2  /
However, if you set it to false, you get an error message. This would have happened in Oracle 9 as it had no skip_unusable_indexes parameter:
SQL> alter session
  2  set skip_unusable_indexes = false
  3  /
Session altered.
SQL> select count(*) from andrews_table
  2  /
select count(*) from andrews_table
ERROR at line 1:
ORA-01502: index 'ORACLE.ANDREWS_INDEX' or
partition of such index is in unusable state

No comments: