Oracle索引监控与外键索引(二)
leted.
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 :
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]
--删除外键上的索引
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 vi