Oracle 11g INVISIBLE index

这是我的 Oracle 11g 系列的文章之一.

在 Oracle 11g 以前的版本，在一个产品环境评估一个有待添加的索引是个麻烦事情。稍有不慎影响该表 SQL 执行计划走错，就可能带来灾难性的影响。这个问题可能也是 Oracle 比较重视的，在 11g 上新推出了 INVISIBLE 索引。这个索引创建后，默认对于 CBO 是不可见的。也就是说不会影响(抛开 DDL 时候的 SQL 重新解析)现有的 SQL 执行计划。如果要在 Session 级别激活这个索引，需要设置初始化参数: OPTIMIZER_USE_INVISIBLE_INDEXES .

创建 INVISIBLE 索引

注意新的关键字

SQL> CREATE INDEX emp_ename ON emp(ename)
  2        TABLESPACE users
  3        INVISIBLE;
Index created

USER_INDEXES 视图的新列 VISIBILITY

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;
INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    INVISIBLE

观察执行计划的影响

SQL> select count(*) from emp where ename='ADAMS';

COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     7 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

告诉优化器使用 INVISIBLE 索引

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;

Session altered.

SQL> select count(*) from emp where ename='ADAMS';

COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 1569421590

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_ENAME |     1 |     7 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

值得注意的是 VISIBILITY 索引在 Rebuild 后会变成可见索引：

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;
INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    INVISIBLE

SQL> alter index EMP_ENAME rebuild;
=Index altered.

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;

INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    VISIBLE
SQL> 

这算是个 Bug 么? 还是本身的特性 ?

对于 INVISIBLE 索引，可以修改属性为 VISIBLE。正常的索引也可以修改为 INVISIBLE 状态。如果修改后仍然要使用这个索引，在语句中使用 HINT 即可。我就不一一演示了。

很多人都知道 Oracle 在 OEM 中早就实现了一个类似的功能：Virtual index(虚拟索引)。但是差别也是比较明显的。虚拟索引，真的是虚拟的，并不占用实际的磁盘空间。而 Invisible 索引需要占用磁盘空间。虚拟索引不可以 Alter ，而 Invisible 可以象正常索引那样进行一些维护工作。Invisible 索引的实现思路有些类似 Quest 以前的优化建议，也是直接会在数据库里面创建建议的索引，但是却不能屏蔽对 CBO 的影响。另外一个需要考虑的是 Virtual index 是 OEM 优化包 的功能，需要额外的 License (当然也可以用命令行创建)。

Invisible 对 DBA 来说，对 DBA 调优的时候多了一个更好的可选方法。

–EOF–