Oracle 索引监控与外键索引
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 completedSQL> 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中则会被监控到--下面开启autotraceSQL> 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:372、存在外键时监控索引情形
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_infoEnter Schema (i.e. SCOTT) : SCOTTEnter 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 |+----------------------------------------+ ConstraintConstraint 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 --开启autotraceSQL> 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 processedSQL> commit;Commit complete.-- Author : Robinson-- Blog : http://blog.csdn.net/robinson_0612SQL> 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 NO3、不存在外键索引时监控索引情形
--删除外键上的索引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 --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
Oracle ROWID
NULL 值与索引(一)
NULL 值与索引(二)
启用 AUTOTRACE 功能
函数使得索引列失效
Oracle 绑定变量窥探
Oracle 自适应共享游标
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)