Invisible Indexes in oracle11g

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，收集信息就来带来额外的资源消耗