Oracle 11g INVISIBLE index

oracle11g_logo.gif

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


  • victor666666

    估计主要是调sql用的,要不然不需要设置session级别的可见性,
    要是全局使用的话,通过改变index 的visible属性就能控制这个索引是否被使用。
    那个rebuild后为visible估计是bug,哈哈,哪能一操作就改变索引的属性了呢

  • xzh2000

    让unvisible index变更visible index,这个地方oracle处理得不是很好,如果表很大,再rebuild一次,即增加了系统风险,也会使dba浪费了很多的时间,如果有个visible选项就好啦,因为unvisible index它物理上已经存在,那个让它visible应该是更新一下数据字典的事吧?…

  • Fenng

    Oracle 应该提供 move table include index online 之类的功能

  • http://zhu1.blogspot.com 木匠

    Why my comments failed so many times?

  • Fenng

    留言有的时候的确容易失败

  • http://www.rs-money.com runescape money

    虽然不是很明白…不过还是要看下 谢谢哈