CNDBA社区
一．Invisible Indexes 说明
从版本11g 开始，可以创建不可见的索引。优化程序会忽略不可见的索引，除非在会话或系统级别上将 OPTIMIZER_USE_INVISIBLE_INDEXES 初始化参数显式设置为TRUE。此参数的默认值是FALSE。
使索引不可见是使索引不可用或删除索引的一种替代办法。使用不可见的索引，可完成以下操作：
(1) 在删除索引之前测试对索引的删除。
(2) 对应用程序的特定操作或模块使用临时索引结构，这样就不会影响整个应用程序。
注意：
与不可用的索引不同，不可见的索引在使用DML 语句期间仍会得到维护。
当索引不可见时，优化程序生成的计划不会使用该索引。如果未发现性能下降，则可以删除该索引。还可以创建最初不可见的索引，执行测试，然后确定是否使该索引可见。
可以查询*_INDEXES 数据字典视图的VISIBILITY 列来确定该索引是VISIBLE 还是INVISIBLE。
SQL> select visibility from dba_indexes where index_name='IDX_ID';
VISIBILIT
---------
VISIBLE
--创建不可见索引：
CREATE INDEX index_name ONtable_name(column_name) INVISIBLE;
--修改索引是否可见：
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
二．示例
--创建表，索引，并收集统计信息：
SQL> create table dave(id number);
Table created.
SQL> begin
2 for I in 1 .. 10000 loop
3 insert into DAVE values(I);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index idx_id on dave(id)invisible;
Index created.
SQL> execdbms_stats.gather_table_stats(ownname =>'&owner',tabname=>'&tablename',estimate_percent => &est_per ,method_opt =>'forall columns size 1',degree=>°ree,cascade => true);
Enter value for owner: sys
Enter value for tablename: dave
Enter value for est_per: 50
Enter value for degree: 2
PL/SQL procedure successfully completed.
SQL>
--带where 条件查询：
SQL> set autot traceonly exp
SQL> select * from dave where id=168;
Execution Plan
----------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DAVE | 1| 4 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=168)
--这里使用了全表扫描，根据唯一性，这里应该走索引的。
--修改 OPTIMIZER_USE_INVISIBLE_INDEXES 参数，再次查询：
SQL> ALTER SESSION SETOPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.
SQL> select * from dave where id=168;
Execution Plan
----------------------------------------------------------
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID | 1| 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("ID"=168)
--这次使用了索引。
--关闭 OPTIMIZER_USE_INVISIBLE_INDEXES参数，使用hint测试
SQL> ALTER SESSION SETOPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
Session altered.
SQL> select /*+index(dave idx_id)*/ *from dave where id=168;
Execution Plan
----------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DAVE | 1| 4 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=168)
--对于invisible的index，使用hint 也没有用。
--将索引改成visible，在测试：
SQL> alter index idx_id visible;
Index altered.
SQL> select * from dave where id=168;
Execution Plan
----------------------------------------------------------
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID | 1| 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("ID"=168)
--这次又正常了。
---------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Skype: tianlesoftware
QQ: tianlesoftware@gmail.com
Email: tianlesoftware@gmail.com
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle 11g
