当你想评估一个索引对你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 | TABLE ACCESS FULL| T | 5 | 15 | 2 (0)| 00:00:01 |--可以看到此时是全表扫描
在T表ID列创建唯一索引?
SQL> create unique index idx_t_id on t(id) invisible;
Index created.
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 | INDEX FULL SCAN| IDX_T_ID | 5 | 15 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------- --发生索引全扫描
将索引设置成INVISIBLE
SQL> alter index idx_t_id invisible;
Index alteredSQL> select index_name,status,visibility from dba_indexes where table_name='T';
?
INDEX_NAME???????????????????? STATUS?? VISIBILITY
------------------------------ -------- ----------
IDX_T_ID?????????????????????? VALID??? INVISIBLE
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 |?? TABLE ACCESS FULL| T?? |?5 |??? 15 |?2?? (0)| 00:00:01 | --此时执行计划里又是走全表扫描路径
---------------------------------------------------------------------------
SQL> alter index idx_t_id visible;
?
Index altered
索引的不可见可以避免了索引重建，尤其是大表的索引，这个新特性更有利于数据库的优化?
-------------------------------------------------------------------------------------------------
本文来自于我的技术博客 http://blog.csdn.net/robo23
转载请标注源文链接,否则追究法律责任!