11gr1起提供了Invisible Indexes,作用就是通过alter index xx invisible，对CBO隐身，我突然想到之前的一个情况，就是开发的在应用中滥用hint index，当时为了让hint index 无效，当时是选择index rename，现在看来如果是11g可以用invisible
有时可能会认为invisible的index 就变为类似无效状态，决不会再查询维护，其实不是的，平时修改表记录是索引还是会维护，通过优化器参数optimizer_use_invisible_indexes =true可以再次使用invisible index。在用dbms_stats包收集信息时index 的信息还是在变化
下面演示这两点
create table test as select rownum id,rpad('x',rownum,'x') name from dual connect by rownum<=10000;
create index idx_test_id on test(id);
SQL> select num_rows,last_analyzed,status,index_name,VISIBILITY from user_indexes where table_name='TEST';
NUM_ROWS LAST_ANALYZED STATUS INDEX_NAME VISIBILIT
---------- ------------------- -------- ------------------------------ ---------
10000 2012-12-11 12:25:05 VALID IDX_TEST_ID VISIBLE
SQL> select * from test where id=10;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 2015 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
alter index idx_test_id invisible;
SQL> select * from test where id=10;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 210K| 2055 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| TEST | 107 | 210K| 2055 (1)| 00:00:25 |
--------------------------------------------------------------------------
alter session set optimizer_use_invisible_indexes=true;
SQL> select * from test where id=10;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3211 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 3211 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
delete test where id <20
exec dbms_stats.gather_table_stats(user,'TEST');
SQL> select num_rows,last_analyzed,status,index_name,VISIBILITY from user_indexes where table_name='TEST';
NUM_ROWS LAST_ANALYZED STATUS INDEX_NAME VISIBILIT
---------- ------------------- -------- ------------------------------ ---------
9981 2012-12-11 13:04:52 VALID IDX_TEST_ID INVISIBLE
可以看到index statistics 有更新，但对于不想更新的统计信息big indexes，收集信息就来带来额外的资源消耗