主从表级联删除效率测试

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4

有网友在论坛发贴(http://www.itpub.net/thread-1751626-1-1.html)，发现自己系统的主从表级联删除比分别删除的效率要低很多，本人不认可这一观点，作如下测试。

构建测试表：

SQL> create table t1 as select * from all_objects;

Table created

 

SQL> create table t1t as select * from t1;

Table created

 

SQL> insert into t1t select * from t1t;

67752 rows inserted

 

SQL> insert into t1t select * from t1t;

135504 rows inserted

 

SQL> insert into t1t select * from t1t;

271008 rows inserted

 

SQL> insert into t1t select * from t1t;

542016 rows inserted

 

SQL> insert into t1t select * from t1t;

1084032 rows inserted

 

SQL> commit;

Commit complete

 

检查测试表数据量：

SQL> select count(*) from t1;

  COUNT(*)

----------

     67751

 

SQL> select count(*) from t1t;

  COUNT(*)

----------

   2168032

 

构建对比测试表：

SQL> create table t2 as select * from t1;

Table created

 

SQL> create table t2t as select * from t1t;

Table created

 

创建索引和主外键：

SQL> alter table T1  add constraint pk_t1 primary key (OBJECT_ID);

Table altered

 

SQL> create index idx_t1t_objectid on T1T (object_id);

Index created

 

SQL> alter table T1T

  2    add constraint fk_t1t_t1 foreign key (OBJECT_ID)

  3    references t1 (OBJECT_ID);

Table altered

 

SQL> alter table T2  add constraint pk_t2 primary key (OBJECT_ID);

Table altered

 

SQL> create index idx_t2t_objectid on T2T (object_id);

Index created

 

SQL> alter table T2T

  2    add constraint fk_t2t_t2 foreign key (OBJECT_ID)

  3    references t2 (OBJECT_ID) on delete cascade;

Table altered

 

开始测试：

SQL> set timing on

SQL> select count(*) from t1;

  COUNT(*)

----------

     67751

 

Elapsed: 00:00:00.28

SQL> select count(*) from t1t;

  COUNT(*)

----------

   2168032

 

Elapsed: 00:00:00.35

SQL> delete t1t where object_id=54;

32 rows deleted.

 

Elapsed: 00:00:00.19

SQL> delete t1 where object_id=54;

1 row deleted.

 

Elapsed: 00:00:00.00

SQL> commit;

Commit complete.

 

Elapsed: 00:00:00.01

SQL> delete t2 where object_id=54;

1 row deleted.

 

Elapsed: 00:00:00.05

SQL> commit;

Commit complete.

 

Elapsed: 00:00:00.01

SQL> select count(*) from t2t where object_id=54;

  COUNT(*)

----------

         0

 

Elapsed: 00:00:00.09

 

结论：级联删除效率更高。

 

转载于:http://blog.itpub.net/81227/viewspace-751707/