oracle删除表加cascade,设值外键同时delete on cascade的效率，比分别删除两张表的效率低很多啊。...

构建测试表：

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

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