Monday, May 23, 2011

skip_unusable_indexes (Part 1)


I was looking through my Oracle Database 10g New Features book again and noticed the skip_unusable_indexes parameter on pages 91 and 225. It said that this was available in previous releases of the database but could only be changed at the session level. I assume this means earlier releases of Oracle 10 as it was not available in Oracle 9.2.0.7:








SQL> col version format a15
SQL> l
  1  select version
  2  from product_component_version
  3  where product like
  4* 'Oracle9i Enterprise Edition%'
SQL> /
 
VERSION
---------------
9.2.0.7.0
 
SQL> select name from v$parameter
  2  where name like '%skip%';
 
no rows selected
 
SQL>
 
Either way, it’s certainly available from Oracle 10.2.0.3.0:
 
SQL> col version format a15
SQL> l
  1  select version
  2  from product_component_version
  3  where product like
  4* 'Oracle Database 10g Enterprise Edition%'
SQL> /
 
VERSION
---------------
10.2.0.3.0
 
SQL> col name format a30
SQL> l
  1  select name from v$parameter
  2* where name like '%skip%'
SQL> /
 
NAME
------------------------------
skip_unusable_indexes
 
SQL>
 
The default is TRUE and it can be altered at the system or session level:
 
SQL> col value format a10
SQL> l
  1  select value, isdefault,
  2  issys_modifiable, isses_modifiable
  3  from v$parameter
  4* where name = 'skip_unusable_indexes'
SQL> /
 
VALUE      ISDEFAULT ISSYS_MOD ISSES
---------- --------- --------- -----
TRUE       TRUE      IMMEDIATE TRUE
 
SQL>
 
Let’s just check this out before we go any further:
 
SQL> alter system
  2  set skip_unusable_indexes = false;
 
System altered.
 
SQL> alter system
  2  set skip_unusable_indexes = true;
 
System altered.
 
SQL> alter session
  2  set skip_unusable_indexes = false;
 
Session altered.
 
SQL> alter session
  2  set skip_unusable_indexes = true;
 
Session altered.
 

No comments: