今天刷题的时候看到一道有关 INVISIBLE index 的题目，没想到居然选错了。随即在网上找相关知识点牢记一下。
不可见索引概念
不可见索引(Invisible Index)是ORACLE 11g引入的新特性。不可见索引是会被优化器忽略的不可见索引，除非在会话或系统级别上将OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE。此参数的默认值是FALSE。
如果是虚拟索引是为了合理、科学新增索引而设计的，那么不可见索引就是为了合理、科学的删除索引而设计的。为什么这样说呢？ 因为DBA在维护索引时，我们经常会找出无用或低效的索引，并删除这些索引，在生产环境下，删除索引还是有一定风险的，即使ORACLE提供了监控索引使用情况的技术。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。
11G之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。在ORACLE 11g里提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。我们可以将无用或低效的索引设置为不可见索引，当观察一段时间后，发现其对系统性能并无任何影响，那么就可以彻底删除索引了。
实验测试
创建测试表TEST，在表上新建不可见索引IDX_TEST_ID，不可见索引可以从字段VISIBILITY这个字段来区别，如下所示：
SQL> create table test as select * from dba_objects;
SQL> create index idx_test_id on test(object_id) invisible;
SQL> select index_name, status,visibility from dba_indexes where index_name=upper('idx_test_id');
INDEX_NAME STATUS VISIBILITY
------------ -------- ----------
IDX_TEST_ID VALID INVISIBLE
#收集下统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'C##zhangyan',TABNAME=>'TEST',CASCADE => TRUE);
SQL> set autotrace traceonly;
SQL> select * from test where object_id=12;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 398 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 132 | 398 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12)
Statistics
----------------------------------------------------------
2 recursive calls
6 db block gets
1433 consistent gets
0 physical reads
1372 redo size
2686 bytes sent via SQL*Net to client
399 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
##强制走索引
SQL> select /*+ index(text, idx_test_id) */ * from test where object_id=12;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 398 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 132 | 398 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - index(text, idx_test_id)
Statistics
----------------------------------------------------------
3 recursive calls
6 db block gets
1433 consistent gets
0 physical reads
1396 redo size
2686 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed可以看到，收集完统计信息后，就算 hint 强制走索引也不生效
设置参数optimizer_use_invisible_indexes为true后，此时优化器就会走索引范围扫描了。
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> select * from test where object_id=12;
Execution Plan
----------------------------------------------------------
Plan hash value: 2500689602
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 132 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12)
Statistics
----------------------------------------------------------
2 recursive calls
6 db block gets
5 consistent gets
0 physical reads
1428 redo size
2686 bytes sent via SQL*Net to client
399 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
评论