Oracle 监控索引特性为我们提供了一个大致判断索引是否被使用的情形。之所以这么说，是因为在Oracle 10g 中收集统计信息时会导致索引被监控，此并非sql语句而产生。而在11g则不会出现类型的情形。其次对于存在子表存在外键的情形，对于主表进行操作时是否会导致索引被监控呢？下面描述的是这个话题。
1、普通监控索引的情形
--演示环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--创建主表
SQL> create table ptb(deptno number constraint ptb_pk primary key,dname varchar2(20));
Table created.
--从scott.dept帐户复制数据
SQL> insert into ptb select deptno,dname from dept;
4 rows created.
SQL> commit;
Commit complete.
--开启索引监控
SQL> alter index ptb_pk monitoring usage;
--为主表收集统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','PTB',cascade=>true);
PL/SQL procedure successfully completed
SQL> select * from v$object_usage where index_name='PTB_PK';
INDEX_NAME TABLE_NAME MON Use START_MONITORING END_MONITORING
------------------------------ ------------------------- --- --- ------------------- -------------------
PTB_PK PTB YES NO 03/22/2013 17:15:37
--注意上面的情形，收集统计信息时，索引被使用没有被监控到，在10g中则会被监控到
--下面开启autotrace
SQL> set autot trace exp;
SQL> select * from ptb where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3991869509
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PTB | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PTB_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL> set autot off;
SQL> select * from v$object_usage where index_name='PTB_PK'; --索引使用被监控到
INDEX_NAME TABLE_NAME MON Use START_MONITORING END_MONITORING
------------------------------ ------------------------- --- --- ------------------- -------------------
PTB_PK PTB YES YES 03/22/2013 17:15:37
2、存在外键时监控索引情形
SQL> create table ctb(id number,name varchar2(30),deptno number);
Table created.
--为子表添加外键约束
SQL> alter table ctb add constraint ctb_fk foreign key(deptno) references ptb(deptno)
2 on delete set null;
Table altered.
--为子表填充数据
SQL> begin
2 for i in 1 .. 1000000
3 loop
4 insert into ctb values(i,'name_'||to_char(i),10);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
--基于外键创建索引
SQL> create index i_ctb_fk_deptno on ctb(deptno) nologging;
Index created.
--收集子表的统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','CTB',cascade=>true);
PL/SQL procedure successfully completed.
SQL> @dba_table_info
Enter Schema (i.e. SCOTT) : SCOTT
Enter Schema (i.e. EMP) : CTB
+----------------------------------------------------------------------------+
| TABLE INFORMATION |
+----------------------------------------------------------------------------+
Owner Table Name Tablespace Last Analyzed # of Rows
--------------- --------------- ---------------------------- -------------------- ------------
SCOTT CTB GOEX_SYSTEM_TBL 22-MAR-2013 17:26:02 1,000,731
+----------------------------------------------------------------------------+
| CONSTRAINTS |
+----------------------------------------------------------------------------+
Constraint
Constraint Name Type Column Name Search Conditio R / Constraint Name Delete Rule Status
------------------ ----------- ------------------ --------------- -------------------- ----------- ---------
CTB_FK Referential DEPTNO SCOTT.PTB_PK SET NULL ENABLED
--上面的统计信息中列出的行数为1,000,731比实际要多
SQL> select count(*) from ctb;
COUNT(*)
----------
1000000
SQL> select * from ctb where rownum<3;
ID NAME DEPTNO
---------- ---------- ----------
1045 name_1045 10
1046 name_1046 10
--开启监控外键索引
SQL> alter index i_ctb_fk_deptno monitoring usage;
Index altered.
SQL> select index_name,monitoring,used from v$object_usage where index_name='I_CTB_FK_DEPTNO';
INDEX_NAME MON USE
------------------------------ --- ---
I_CTB_FK_DEPTNO YES NO
--开启autotrace
SQL> set autot trace stat;
SQL> delete from ptb where deptno=20; 从主表删除一条记录
1 row deleted.
Statistics
----------------------------------------------------------
172 recursive calls
7 db block gets
31 consistent gets --->此时一致读为31
1 physical reads
780 redo size
1114 bytes sent via SQL*Net to client
1184 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> commit;
Commit complete.
-- Author : Robinson
-- Blog : http://blog.csdn.net/robinson_0612
SQL> set autot off;
--下面的查询可以看出外键索引没有被使用到
SQL> select index_name,monitoring,used from v$object_usage where index_name='I_CTB_FK_DEPTNO';
INDEX_NAME MON USE
------------------------------ --- ---
I_CTB_FK_DEPTNO YES NO
3、不存在外键索引时监控索引情形
--删除外键上的索引
SQL> drop index i_ctb_fk_deptno;
Index dropped.
SQL> set autot trace stat;
SQL> delete from ptb where deptno=30; --再次从主表删除纪录
1 row deleted.
Statistics
----------------------------------------------------------
186 recursive calls
6 db block gets
3502 consistent gets --此时的一致读为3502，较上次多出100多倍
0 physical reads
740 redo size
2065 bytes sent via SQL*Net to client
1479 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
4、小结
a、在监控索引时，如果子表上存在外键约束且存在外键索引，对于主表得DML操作不会使得子表索引被使用
b、尽管子表索引不会由于主表DML操作被监控到，但如果子表外键索引不存在，主表上的DML会产生更多的一致读(相对外键索引存在)
c、由上可知，对于外键索引未被监控到的情形，不可盲目的认为该索引无效而删除
d、对于子表不存在索引的情形，对于主表上的主键的更新以及删除主表中的一行都将导致整个子表被锁住
更多参考
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
dbms_xplan之display_cursor函数的使用
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)