Oracle 索引监控与外键索引-阿里云开发者社区

开发者社区> 数据库> 正文

Oracle 索引监控与外键索引

简介:       Oracle 监控索引特性为我们提供了一个大致判断索引是否被使用的情形。之所以这么说,是因为在Oracle 10g 中收集统计信息时会导致索引被监控,此并非sql语句而产生。

      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 --> 隐式游标(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体系结构)

版权声明:本文首发在云栖社区,遵循云栖社区版权声明:本文内容由互联网用户自发贡献,版权归用户作者所有,云栖社区不为本文内容承担相关法律责任。云栖社区已升级为阿里云开发者社区。如果您发现本文中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,阿里云开发者社区将协助删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章