oracle11g 不可见索引

[size=medium]索引维护是DBA的一项重要工作。当一个系统运行很长一段时间，经过需求变更、结构设计变化后，系统中就可能会存在一些不会被使用的索引，或者使用效率很低的索引。这些索

引的存在，不仅占用系统空间，而且会降低事务效率，增加系统的waits。因此，我们需要找出那些无用或低效索引的索引并删除它们（找出无用索引可以通过索引监控 的方法）

。但是，直接删除索引还是存在一定风险的。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被

删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。
11G之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引

时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。
在11g里，Oracle 提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。我们可以在创建索引时指定invisible属性或者用

alter语句来修改索引为invisible。
在session级别设置参数optimizer_use_invisible_indexes为true，系统就可以使用invisible索引。
alter session set optimizer_use_invisible_indexes=true;

SQL代码

LIBIN@tran>select count(*) from all_objects;

COUNT(*)
----------
55622

LIBIN@tran>create table t_test1 as select * from all_objects;

表已创建。

LIBIN@tran>desc all_objects;
名称
-----------------------------------------------------------------------------------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
NAMESPACE
EDITION_NAME

LIBIN@tran>create index t_test1_idx on t_test1(object_name);

索引已创建。

LIBIN@tran>begin
2 dbms_stats.gather_table_stats(user,'t_test1');
3 end;
4 /

PL/SQL 过程已成功完成。

LIBIN@tran>set autotrace trace only;
SP2-0158: 未知的 SET 选项 "only"
LIBIN@tran>set autotrace traceonly;

LIBIN@tran>select * from t_test1 where object_name like 'LIBIN%';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 2 | 196 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME" LIKE 'LIBIN%')
filter("OBJECT_NAME" LIKE 'LIBIN%')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

LIBIN@tran>


LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX NO VISIBLE

LIBIN@tran>


LIBIN@tran>alter index t_test1_idx invisible;

索引已更改。

LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX NO INVISIBLE

LIBIN@tran>

LIBIN@tran>select * from t_test1 where object_name like 'LIBIN%';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 226 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 2 | 196 | 226 (1)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'LIBIN%')


统计信息
----------------------------------------------------------
253 recursive calls
0 db block gets
869 consistent gets
4 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed

LIBIN@tran>


LIBIN@tran>alter index t_test1_idx visible;

索引已更改。


LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX NO VISIBLE


LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx) */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 2 | 196 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME" LIKE 'LIBIN%')
filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>alter index t_test1_idx invisible;

索引已更改。

LIBIN@tran>select index_name,user_stats,visibility from user_indexes where index_name='T_TEST1_IDX';

INDEX_NAME USER_S VISIBILITY
------------------------------------------------------------ ------ ------------------
T_TEST1_IDX NO INVISIBLE

LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx) */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 1883417357

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 226 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 2 | 196 | 226 (1)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择13行。

LIBIN@tran>alter session set optimizer_use_invisible_indexes=true;

会话已更改。

LIBIN@tran>explain plan for select /*+ index(t_test1 t_test1_idx) */ * from t_test1 where object_name like 'LIBIN%';

已解释。

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 2 | 196 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME" LIKE 'LIBIN%')
filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>select * from t_test1 where object_name like 'LIBIN%';

未选定行

LIBIN@tran>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3466041839

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 2 | 196 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME" LIKE 'LIBIN%')
filter("OBJECT_NAME" LIKE 'LIBIN%')

已选择15行。

LIBIN@tran>[/size]