oracle使索引不可见,关于oracle的不可见索引探究

--FDH

一、关于oracle的不可见索引

oracle对于不可见索引的给出的官方定义是：

An invisible indexis maintained by

DML operations and is not used by default by the optimizer. Making an index

invisible is an alternative to making it unusable or dropping it. Invisible

indexes are especially useful for testing the removal of an index before

dropping it or using indexes temporarily without affecting the overall

application.

大致翻译为：

不可见索引在 DML 操作中会被维护，但在默认情况下优化器不会使用它。使索引不可见是使其不可用或删除它的一种替代方法。不可见索引有时特别有用，比如在删除索引前测试移除后果，或临时用一下索引而不会影响整个应用程序。

二、关于oracle不可见索引的实验

2.1创建表和索引：

创建表

T_test_index：

create table T_test_index (id number,name varchar2(40),bz varchar2(50));

添加数据：

begin

for c in 1 .. 10000 loop

insert into T_test_index values (c, 'fdh', '');

end loop;

end;

创建正常索引I_T_TEST_INDEX_ID：

create index I_T_TEST_INDEX_ID on T_test_index(id);

收集统计信息：

begin

dbms_stats.gather_table_stats('scott', 'T_TEST_INDEX', cascade => true);

end;

查看索引的状态和是否可见：

查看谓词带有ID列的执行计划：

2.2将索引设为不可见：

alter index I_T_TEST_INDEX_ID invisible;

查看是否修改成功：

查看谓词带有ID列的执行计划：

果然将索引I_T_TEST_INDEX_ID 设置为不可见之后，优化器不会再考虑索引的扫描。

好了，到这里有一个问题，如果在SQL上面添加强制使用该不可见索引的HNIT，那么优化器是否会选择索引呢？下面我们再SQL语句中添加强制索引HNIT：

很显然优化器还是没有使用索引，依然还是全表扫描。所以当索引不可见时，即使hnit也无法改变使优化器使用该索引。

2.3使用不可见索引的方法：

修改session的参数：

alter session set

optimizer_use_invisible_indexes = true;

修改之后再该session上的运行上述同样的SQL的执行计划变更为：

所以当索引被设为不可见时，并非完全不可用。

可以通过修改参数optimizer_use_invisible_indexes为true来使用(默认为false，system级别和session级别都可以修改)

三、总结

1、当索引变更为不可见的时候，只是对oracle的优化器不可见。

2、不可见索引在DML操作的时候也会被维护。

3、加HNIT对不可见索引无效。

4、可以通过修改system级别和session级别参数来使用不可见索引。