SKIP_UNUSABLE_INDEXES Initialization

From : metalink[@more@]
Subject: SKIP_UNUSABLE_INDEXES Initialization Parameter in Oracle Database 10g
Doc ID: Note:281500.1Type: HOWTO
Last Revision Date: 05-MAR-2008Status: 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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66634/viewspace-1013030/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/66634/viewspace-1013030/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值