oracle 隐藏索引

oracle 隐藏索引

隐藏（不可见）索引（invisible indexes）是oracle 11g 的一项新特性，该特性允许索引以隐藏的方式创建或者创建以后标记为隐藏。很神奇吧，oracle 也喜欢玩潜水。隐藏索引对优化器是不可见的，除非显式的在实例级或者会话级设置optimizer_use_invisible_indexes 初始化参数为true。隐藏索引的用处在于，可以当作索引删除或者设置为不可用的一种代替方式。

更重要的意义在意测试索引对查询性能的影响。

SQL> conn hr/oracle

Connected.

SQL> drop table emp purge;

Table dropped.

SQL> create table emp as select * from employees;

Table created.--创建测试用的表。

SQL> create index emp_idx on emp(employee_id);

Index created.

SQL> set autotrace on

SQL> select employee_id,first_name,last_name

  2  from emp where employee_id = 100;

Execution Plan

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

Plan hash value: 1472992808

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         |     1 |    39 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    39 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_IDX |     1 |       |     1   (0)| 00:00:01 |

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

SQL> alter index emp_idx invisible;--已创建的index alter 为隐藏。

Index altered.

SQL> select employee_id,first_name,last_name

  2  from emp where employee_id = 100;

Execution Plan

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

Plan hash value: 3956160932

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |

当把index 标记为invisible 以后得到的执行计划是对emp 表进行全表扫描。因为这时候CBO忽略了index emp_idx 的存在。现在把optimizer_use_invisible_indexes 设置为true 让CBO能够使用标记invisible 的索引。

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> select employee_id,first_name,last_name

  2  from emp where employee_id = 100;

Execution Plan

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

Plan hash value: 1472992808

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         |     1 |    39 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    39 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_IDX |     1 |       |     1   (0)| 00:00:01 |

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

标记为invisible 的索引在执行DML的时候，任然是需要维护的。

SQL> drop index emp_idx;

Index dropped.

SQL> create unique index emp_idx on emp (employee_id) invisible;--index创建的时候指定为隐藏。

Index created.

SQL> insert into emp select * from emp where employee_id = 100;

insert into emp select * from emp where employee_id = 100

*

ERROR at line 1:

ORA-00001: unique constraint (HR.EMP_IDX) violated

通过执行DML语句我们可以知道标记为invisible 的index 任然是在维护的。

索引是否是隐藏的可以通过*_indexes数据字典视图来查看

SQL> select index_name,visibility from user_indexes

  2  where index_name = 'EMP_IDX';

INDEX_NAME                     VISIBILIT

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

EMP_IDX                        INVISIBLE

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

转载于:http://blog.itpub.net/26110315/viewspace-739596/