外键不加索引引起的性能问题

外键不加索引会造成什么问题呢,下面在做个实验:

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)
********************************************************************************

结论:可以看到删除主表时,删了多少条记录就要扫描从表多少次,如果没有索引,可想而知性能有多差。为什么要扫描从表呢,原因是要做约束的检查。

Tagged:

Comments are closed.