鉴别不使用的索引

有的索引根本用不着，但当在对表修改时，它也是会自动修改，这样会降低数据库的速度。下面来做个简单试验来监视索引是否使用。

首先创建个表，

SQL> create table t (id int,易做图 char(1),name char(10));

Table created.

然后输入数据，

SQL> begin

2 for i in 1..100

3 loop

4 insert into t values(i,'M','sun');

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

对表t创建一个索引，

SQL> create index t_idx1 on t(id);

Index created.

可以来查看一下，

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

T_IDX1 INDEX

T TABLE

在监控索引之前，先来介绍个动态性能视图,

V$OBJECT_USAGE

V$OBJECT_USAGE displays statistics about index usage gathered from the database. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored

and displayed in this view.

V$OBJECT_USAGE显示有关于数据库中索引使用情况的统计。能够利用这个视图来监控索引使用。该视图能够监控和显示最近一次使用到的所有索引。

Column Datatype Description

INDEX_NAME VARCHAR2(30) Index name in sys.obj$.name(索引的名字)

TABLE_NAMEa VARCHAR2(30) Table name in sys.obj$.name(索引的基表)

MONITORING VARCHAR2(3) YES|NO(是否监控，yes 是|no 否)

USED VARCHAR2(3) YES|NO(是否使用，yes 是|no 否)

START_MONITORING VARCHAR2(19) Start monitoring time insys.object_stats.start_monitoring(索引监控开始时间)

END_MONITORING VARCHAR2(19) End monitoring time insys.object_stats.end_monitoring(索引监控结束时间

打开并开始监控索引的使用，

SQL> alter index t_idx1 monitoring usage;

Index altered.

来检查一下是否开始监控，

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

T_IDX1 T YES NO 04/18/2013 00:23:06

在上面显示MON是yes表示开始监视，use为no表示没有使用，还用监视开始的时间和结束时间。

在这里做一下可以对索引的使用，在来看看动态性能视图的变化，

SQL> set autot on exp

SQL> select * from t where id=88;

ID S NAME

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

88 M sun

Execution Plan

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

Plan hash value: 4055207394

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

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

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=88)

从查看执行计划中可以看到使用了索引，

SQL> set autot off

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

T_IDX1 T YES YES 04/18/2013 00:23:06

可以看到USE改变成了YES，表明使用过了。

关闭监控索引，

SQL> alter index t_idx1 nomonitoring usage;

Index altered.

再来看一下动态性能视图，

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

T_IDX1 T NO YES 04/18/2013 00:23:06 04/18/2013 00:41:38

这时候可以看出，MON改变成了NO，还标出了监控的时间段。