外鍵不加索引引起的效能問題

      外鍵不加索引會造成什麼問題呢，下面在做個實驗：

SQL> drop table t_primary  purge;
SQL> drop table t_foreign purge;
SQL> create table t_primary(id number(10) primary key);
SQL> create table t_foreign(fid number(10) references t_primary);
SQL> insert /*+append*/into t_primary select rownum from dual connect by level <=100000;
SQL> commit;
SQL> insert /*+append*/into t_foreign select rownum from dual connect by level <=100000;
SQL> commit;
SQL> delete t_foreign where fid between 90001 and 100000;
SQL> commit;
SQL> alter session set tracefile_identifier = '2014-02-09';
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> delete t_primary where id between 90001 and 100000;
SQL> alter session set events '10046 trace name context off';

對trace出來的檔案用tkprof進行格式化，D:\oracle\product\10.2.0\admin\ordb10\udump>tkprof ordb10_ora_472_2014-02-09.trc  0209.txt

********************************************************************************
delete t_primary 
where
 id between 90001 and 100000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          4          1           0
Execute      1      0.93       1.14         34        159      50628       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.93       1.15         34        163      50629       10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T_PRIMARY (cr=1840226 pr=34 pw=0 time=27799619 us)
  10000   TABLE ACCESS FULL T_PRIMARY (cr=157 pr=34 pw=0 time=52004 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                          9        0.02          0.03
  db file sequential read                         7        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
select /*+ all_rows */ count(1) 
from
 "TEST"."T_FOREIGN" where "FID" = :1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      1.54       1.72          0          1          0           0
Fetch    10000     25.25      24.92          0    1840000          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001     26.79      26.64          0    1840001          0       10000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  SORT AGGREGATE (cr=1840000 pr=0 pw=0 time=24947346 us)
      0   TABLE ACCESS FULL T_FOREIGN (cr=1840000 pr=0 pw=0 time=24890373 us)
********************************************************************************

 結論：可以看到刪除主表時，刪了多少條記錄就要掃描從表多少次，如果沒有索引，可想而知效能有多差。為什麼要掃描從表呢，原因是要做約束的檢查。