Oracle隐藏索引和索引可用性

在我参与过的许多商店系统开发中，我发现在生产系统中创建一个索引并不需要经过详细的论证，甚至连应用程序代码也没有检查就创建了，大多数时候都是靠个人经验和感觉行事的，人们往往只会思考与创建索引有关的列是否会受到影响，完全靠临场反应，到最后数据库中往往有上百个索引创建了但可能从未使用过，或对SQL执行性能有负面影响。作为一名DBA，我们有责任找到并清除这些闲置的以及对性能有负面影响的索引。但我们从哪里开始呢?其实Oracle已经为我们提供了解决之道。

　　有两种基本的情况：

　　1、 我们必须确定索引是否被使用，如果索引没有使用，只需要删除它就可以了。

　　2、 如果索引被使用了，或认为索引将会被使用，对于这种索引，要确定索引对数据库性能的影响稍微有点难度。

　　对于第一种情况(判断索引是否被使用)，我们可以对数据库索引进行监视，关键是要监视足够长的时间，可以监视一小时，一天，一周或一个业务季度，这要取决于表上的索引是与什么相关的。

　　那该如何监视一个索引呢?其实简单得很，只需要使用ALTER INDEX命令，加上MONITORING USAGE子句就可以了，还是来看一看实例吧：

SQL > ALTER INDEX pk_emp MONITORING USAGE;

　　 Index altered.

　　SQL > ALTER INDEX ix_emp_sal MONITORING USAGE;

　　 Index altered.

　　当你在该表上进行SELECT，UPDATE，DELETE(没有INSERT)时，一旦使用了索引，就会在V$OBJECT_USAGE动态视图中将该索引标记为在使用中：

SQL > select * from emp where empno = 7844 ;

　　EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

　　 -- --- ------ --------- ---- --------- ----- ---- ------

　　 7844 TURNER SALESMAN 7698 08 - SEP - 81 1500 0 30

　　SQL > SELECT v.index_name, v.table_name,

　　v.monitoring, v.used,

　　start_monitoring, end_monitoring

　　 FROM v$object_usage v, user_indexes u

　　 WHERE v.index_name = u.index_name;

　　INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

　　 -- -------- ---------- --- --- ------------------- -------------------

　　PK_EMP EMP YES YES 04 / 28 / 2009 10 : 16 : 51

　　IX_EMP_SAL EMP YES NO 04 / 28 / 2009 10 : 17 : 01

　　就这么简单。显然，如果前面的SELECT语句只是查询EMP表，或许我们应该删除掉IX_EMP_SAL索引。

　　如果索引已经被使用，或将被使用，在采取行动(如删除或创建索引)之前，我们必须放聪明点。为了帮助解决这些问题，Oracle为我们提供了一个新特性，INVISIBLE索引，允许我们将索引隐藏起来，隐藏的索引不能使用，但仍然可以通过INSERT，UPDATE和DELETE进行维护。要使一个索引不可见，可以使用CREATE或ALTER INDEX INVISIBLE命令，这里以上面的IX_EMP_SAL索引为例进行演示：

> SQL > create index ix_emp_sal on emp(sal) INVISIBLE;

　　SQL > alter index ix_emp_sal INVISIBLE;

　　当一个索引被置为INVISIBLE时，应用程序就看不到它了，也不能在任何DML操作中使用它了。优化器也看不到隐藏索引，因此也不会被任何执行计划使用，除非明确指定了一个提示(hint)，会话被设置为使用隐藏索引，或者数据库被设置为可以使用所有的隐藏索引，这正是某些DBA梦寐以求的功能，使用一个新的init.ora参数optimizer_use_invisible_indexes，你可以固定会话，或全系统范围内都可以使用隐藏索引，让你有机会测试新建索引在完全移动到生产环境之前的影响，可以通过设置这个初始化参数使用隐藏索引，或在SQL中增加提示使用隐藏索引，如：　

> SQL > alter system set optimizer_use_invisible_indexetrue;

　　SQL > alter session set optimizer_use_invisible_indexetrue;

　　SQL > select * /* + index (emp ix_ep_sal) */ ename from emp where sal = 1500 ;

　　要使一个索引从不可见状态变为可见状态，使用ALTER INDEX语句+ VISIBLE关键字即可：

> SQL > alter index ix_emp_sal VISIBLE;

　　此外，如果你想找出在你的数据库中哪些索引是隐藏的，可以查询DBA_，USER_或ALL_INDEXES视图中的VISIBILITY列。

> 　SQL > select index_name, visibility

　　 from dba_indexes

　　 where index_name = ' IX_EMP_SAL ' ;

　　INDEX_NAME VISIBILIT

　　 -- ------------ ---------

　　IX_EMP_SAL INVISIBLE

　　在运用INVISIBLE索引功能时要注意由其它方法创建或修改的隐藏索引，如果你以前写有一个SQL查看索引的结构，现在需要修改一下增加检查VISIBILITY列，否则你看到的仅仅是索引的部分信息，当执行了大量的INSERT，UPDATE或DELETE操作时，这可能会变成一个隐藏的恶梦。

　　索引的可用性一向有些黑色艺术，对于删除一个索引是否会对性能产生影响从来都没有明确的判断标准，现在通过监视索引的可用性，并可以修改索引的可见性，DBA完全可以更好地测试和验证索引的可用性了。