Oracle 索引监控与外键索引

--演示环境
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

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

--删除外键上的索引
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