首页 > 代码库 > 11g新特性_索引的可见与不可见
11g新特性_索引的可见与不可见
当你想评估一个索引对你SQL查询语句的影响时，恰巧你的数据库是11g时候，可以快速将索引设置成VISIBLE或INVISIBLE。值得称赞的时，当索引被设置成INVISIBLE时候，DML语句发生时候照常会维护索引，也就是说处于INVISIBLE状态下的索引并不失效，只是优化器不选择索引路径而已。
下面通过实验来验证一下：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> select * from t; ID ---------- 2 11 22 1 33 SQL> select count(id) from t; COUNT(ID) ---------- 5 Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | <strong>TABLE ACCESS FULL</strong>| T | 5 | 15 | 2 (0)| 00:00:01 |--可以看到此时是全表扫描
在T表ID列创建唯一索引
<p>SQL> create unique index idx_t_id on t(id) invisible;</p><p>Index created.</p>SQL> select count(id) from t; COUNT(ID) ---------- 5 Execution Plan ---------------------------------------------------------- Plan hash value: 4168287108 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | <strong>INDEX FULL SCAN</strong>| IDX_T_ID | 5 | 15 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- --发生索引全扫描
将索引设置成INVISIBLE
SQL> alter index idx_t_id invisible; Index altered<p>SQL> select index_name,status,visibility from dba_indexes where table_name='T'; INDEX_NAME STATUS VISIBILITY------------------------------ -------- ----------IDX_T_ID <span style="color:#ff0000;"><strong>VALID</strong></span> <strong>INVISIBLE</strong></p><p>SQL> select count(id) from t;</p><p> COUNT(ID)---------- 5</p><p>Execution Plan----------------------------------------------------------Plan hash value: 2966233522</p><p>---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | || 2 | <strong>TABLE ACCESS FULL</strong>| T | 5 | 15 | 2 (0)| 00:00:01 | --此时执行计划里又是走全表扫描路径---------------------------------------------------------------------------</p><p>SQL> alter index idx_t_id visible; Index altered</p>
索引的不可见可以避免了索引重建，尤其是大表的索引，这个新特性更有利于数据库的优化
-------------------------------------------------------------------------------------------------
本文来自于我的技术博客 http://blog.csdn.net/robo23
转载请标注源文链接,否则追究法律责任!
11g新特性_索引的可见与不可见
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。