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/