这是我的 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--
