SKIP_UNUSABLE_INDEXES Initialization

From : metalink[@more@]

Subject:	SKIP_UNUSABLE_INDEXES Initialization Parameter in Oracle Database 10g
	Doc ID:	Note:281500.1	Type:	HOWTO
	Last Revision Date:	05-MAR-2008	Status:	PUBLISHED

In this Document
Goal
Solution
References

---

Applies to:

Oracle Server - Enterprise Edition - Version:
Information in this document applies to any platform.

Goal

This article discusses about the initialization parameter SKIP_UNUSABLE_INDEXES in Oracle Database 10g

Setting this parameter to TRUE disables error reporting of indexes and index partitions marked UNUSABLE.

The SKIP_UNUSABLE_INDEXES, which in earlier releases was a session parameter,is now an initialization parameter with a default value of TRUE.

In earlier releases default value of this parameter is false.

Solution

Following are the steps to check the same.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> connect scott/tiger

SQL> create table EMP_RANGE(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 SAL NUMBER(7,2)
5 )
6 partition by range (empno)
7 (
8 partition P1 values less than (10),
9 partition P2 values less than (20),
10 partition P3 values less than (30),
11 partition P4 values less than (MAXVALUE));

Table created.

SQL> create index indx_emp on emp_range (empno);

SQL> begin
2 for x in 1..100
3 loop
4 insert into emp_range values (x,'dsadas',x*12);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> alter table emp_range truncate partition p2;
Table truncated.

SQL> select index_name,status from user_indexes where table_name='EMP_RANGE';

INDEX_NAME STATUS
------------------------------ --------
INDX_EMP UNUSABLE


SQL> select * from emp_range where empno=45;
select * from emp_range where empno=45
*
ERROR at line 1:
ORA-01502: index 'SCOTT.INDX_EMP' or partition of such index is in unusable
state

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> select * from emp_range where empno=45;

EMPNO ENAME SAL
---------- ---------- ----------
45 dsadas 540



The error doesn't occur in Oracle Database 10g when executing the same query if the index is marked UNUSABLE because default value of SKIP_UNUSABLE_INDEXES is true


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> connect scott/tiger

/* create table,index as mentioned in previous steps */

SQL> select index_name,status from user_indexes where table_name='EMP_RANGE';

INDEX_NAME STATUS
-------------------- --------
INDX_EMP UNUSABLE



SQL> set autotrace on explain

/* Optimizer chooses FULL TABLE SCAN */


SQL> select * from emp_range where empno=45;

EMPNO ENAME SAL
-------- ---------- ----------
45 dsadas 540


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=33)
1 0 PARTITION RANGE (SINGLE) (Cost=3 Card=1 Bytes=33)
2 1 TABLE ACCESS (FULL) OF 'EMP_RANGE' (TABLE) (Cost=3 Card=
1 Bytes=33)

References

Oracle Database Administrator's Guide 10g Release 1 (10.1)

Keywords

10G; SKIP_UNUSABLE_INDEXES;