解锁不可见索引新特性，处理ORA-01555故障

关注“数据和云”，精彩不容错过

何国亮

云和恩墨交付部技术顾问，获得 Oracle 11g OCM 认证。有超过 6 年超大型数据库专业服务经验，曾为通信运营商、银行、保险、政府、制造业等行业客户的业务关键型系统提供了运维、升级、性能优化、项目实施与管理、容灾建设等咨询与技术实施服务。在超大规模数据库（VLDB）、业务连续性与高可用、升级迁移、性能优化与管理等方面有丰富的实战经验。

摘要

从 Oracle 11g 开始引入了不可见索引（invisible index）新特性。本文将简述不可见索引的相关特性，并作相关测试。最后分享一个使用不可见索引解决 ORA-01555 的故障。希望对大家有帮助。

1.内容概述

Oracle 11g 较之前的版本，推出了很多新功能，其中一项就是不可见索引（invisible index）。本文将简单的研究一下不可见索引以及分享一个使用不可见索引处理 ORA-01555 故障案例。

2.不可见索引简介

从 Oracle 11g 开始，可以创建不可见索引（invisible index）。默认情况下，优化器会忽略 invisible index，不使用 invisible index，即使添加了相关索引 hint，也不会使用 invisible index。

初始化参数 optimizer_use_invisible_indexes 决定优化器是否使用 invisible index，其默认值为 false，即默认不使用 invisible index。但如果在 session 级别或者 system 级别上将 optimizer_use_invisible_indexes 初始化参数设置为 true，那么就可以使用 invisible index。

与不可用索引 (unusable index) 不同，invisible index 在使用 DML 语句期间仍会得到维护。

Oracle 引入不可见索引是有用途的，使索引不可见是使索引不可用或者删除索引的一种替代办法。

在删除索引之前,将索引修改为不可见，观察是否会产生影响，以便判断索引是否可以删除。

当索引不可见时，优化器生成的执行计划不会使用该索引。删除索引时，可以先将索引修改为 invisible，如果未发生性能下降问题，则可以删除该索引。在表上新建索引时，可以先创建一个最初不可见的索引，然后执行测试，看索引的效率怎么样，最后确定是否使该索引可见，是否使用该索引。

可以查看 dba_indexes、all_indexes、user_indexes 视图的 visibility 字段来确定该索引是可见索引还是不可见索引，visible 表示可见，invisible 表示不可见。

3.不可见索引测试

下面做一些简单的测试。

3.1

创建不可见索引

先创建 tab 表，然后在表上创建了一个 invisible 索引。

SQL> create table tab as select * from user_objects;

 

Table created.

 

SQL> create index tab_idx1 on tab(object_name) invisible; 

 

Index created.

 

SQL> col INDEX_NAME for a25

SQL> col TABLE_OWNER for a20

SQL> col TABLE_NAME for a20

SQL> col VISIBILITY for a25

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY,STATUS from user_indexes where TABLE_NAME='TAB';

INDEX_NAME TABLE_OWNER TABLE_NAME VISIBILITY  STATUS

---------- ----------- ---------- ----------- --------

TAB_IDX1   SCOTT       TAB        INVISIBLE   VALID

3.2

测试优化器是否会忽略不可见索引

SQL> set autotrace traceonly

SQL> select * from tab where object_name='EMP';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1995730731

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_NAME"='EMP')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

Statistics

----------------------------------------------------------

         17  recursive calls

       &n