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: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_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 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體繫結構)