11g新特性_索引的可见与不可见

来源：互联网发布：淘宝网手提包包编辑：程序博客网时间：2024/05/17 22:54

当你想评估一个索引对你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 ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE11.2.0.4.0ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - ProductionSQL> select * from t;ID---------- 21122 133SQL> select count(id) from t; COUNT(ID)---------- 5Execution 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列创建唯一索引

<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)---------- 5Execution 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 altered<p>SQL> select index_name,status,visibility from dba_indexes where table_name='T'; INDEX_NAME                     STATUS   VISIBILITY------------------------------ -------- ----------IDX_T_ID                       VALID     INVISIBLE<p>SQL> select count(id) from t;COUNT(ID)----------  5</p><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 |   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

转载请标注源文链接,否则追究法律责任!

0 0