ORACLE不可见索引(Invisible Indexes)
不可见索引(Invisible Index)是ORACLE 11g引入的新特性。不可见索引是会被优化器忽略的不可见索引，除非在会话或系统级别上将OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE。此参数的默认值是FALSE。如果是虚拟索引是为了合理、科学新增索引而设计的，那么不可见索引就是为了合理、科学的删除索引而设计的。为什么这样说呢？ 因为DBA在维护索引时，我们经常会找出无用或低效的索引，并删除这些索引，在生产环境下，删除索引还是有一定风险的，即使ORACLE提供了监控索引使用情况的技术。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。11G之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。在ORACLE 11g里提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。我们可以将无用或低效的索引设置为不可见索引，当观察一段时间后，发现其对系统性能并无任何影响，那么就可以彻底删除索引了。
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer and the user unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.The default value for this parameter is FALSE.
Using invisible indexes, you can do the following:
1.Test the removal of an index before dropping it.
2.Use temporary index structures for certain operations or modules of an application without affecting the overall application.
Unlike unusable indexes, an invisible index is maintained during DML statements.
不可见索引测试
创建测试表TEST，在表上新建不可见索引IDX_TEST_ID，不可见索引可以从字段VISIBILITY这个字段来区别，如下所示：
SQL> create table test
2 as
3 select * from dba_objects;
Table created.
SQL> create index idx_test_id on test(object_id) invisible;
Index created.
SQL> select index_name, status,visibility from dba_indexes where index_name=upper(‘idx_test_id‘);
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
IDX_TEST_ID VALID INVISIBLE
SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SQL>
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>‘TEST‘,CASCADE => TRUE);
PL/SQL procedure successfully completed.
如下所示，优化器是“看不见”这个索引的，即使使用提示hint强制其走这个索引。优化器还是不会走索引扫描
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 | 97 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12)Statistics
----------------------------------------------------------
424 recursive calls
0 db block gets
1094 consistent gets
0 physical reads
0 redo size
1604 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 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 | 97 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12)Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1036 consistent gets
0 physical reads
0 redo size
1604 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
设置参数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: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 97 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
早期版本中，不可见索引有一些Bug，下面整理了一些这方面的Bug，应用相关的补丁或最新的版本都可避免碰到这些Bug。
1：使用DBMS_METADATA.GET_DDL获取不可见索引的定义不正确，少了关键字invisible
详情参考：DBMS_METADATA.GET_DDL Returns Incorrect DDL For Invisible Index (文档 ID 1965563.1)
2：Bug 9336476 - Optimizer 10053 trace shows wrong status for INVISIBLE index(文档 ID 9336476.8)
3：Bug 16544878 - Drop invisible index affects SQL execution plan (文档 ID 16544878.8)
4：Wrong Result With Invisible Index (文档 ID 1266380.1)
|
UPDATE statement fails with below error when all indexes are marked invisible.
ORA-14406: updated partition key is beyond highest legal partition key
The execution plan of the UPDATE is parallel plan.
5: Bug 9347681 - OERI [12863] with invisible indexing(文档 ID 9347681.8)
参考资料：
http://blog.itpub.net/26736162/viewspace-2124044
ORACLE不可见索引(Invisible Indexes)
标签：where .com rect 分享 测试表 explicit 索引范围扫描 erro first
小编还为您整理了以下内容，可能对您也有帮助：
Oracle index unusable和invisible的区别
Oracle index unusable和invisible的区别
unusable index 是被优化器所忽略，并且不被dml操作维护，如果索引被unusable后，需要重建。
invisible index会被优化器所忽略，但是dml操作仍然会维护索引。在session或者system级别使用参数OPTIMIZER_USE_INVISIBLE_INDEXES=true，那么优化器会考虑使用invisible index。适用于查看索引是否被正在的使用上。
Oracle index unusable和invisible的区别
Oracle index unusable和invisible的区别
unusable index 是被优化器所忽略，并且不被dml操作维护，如果索引被unusable后，需要重建。
invisible index会被优化器所忽略，但是dml操作仍然会维护索引。在session或者system级别使用参数OPTIMIZER_USE_INVISIBLE_INDEXES=true，那么优化器会考虑使用invisible index。适用于查看索引是否被正在的使用上。
oracle不使用索引的原因有哪些
索引失效
1) 没有查询条件，或者查询条件没有建立索引
2) 在查询条件上没有使用引导列
3) 查询的数量是大表的大部分，应该是30％以上。
4) 索引本身失效
5) 查询条件使用函数在索引列上（见12）
6) 对小表查询
7) 提示不使用索引
8) 统计数据不真实
9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),
但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';
11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10;
说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引，
create index test_id_fbi_idx on test(round(id));
然后 select * from test where round(id)=10; 这时函数索引起作用了
1,<>
2,单独的>,<,(有时会用到，有时不会)
3,like "%_" 百分号在前.
4,表没分析.
5,单独引用复合索引里非第一位置的索引列.
6,字符型字段为数字时在where条件里不添加引号.
7,对索引列进行运算.需要建立函数索引.
8,not in ,not exist.
9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
10, 索引失效。
11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上
12,有时都考虑到了 但就是不走索引,drop了从建试试在
13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
14,联合索引 is not null 只要在建立的索引列（不分先后）都会走,
in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,
其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,
或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,
以上两种情况索引都会走。其他情况不会走。
oracle中，创建的索引 index不显示，求解？
Tables下找到具体的表，然后再展开，才能找到 indexes