SQL Oracle级联删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8571692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle cascade delete
提问by Rnet
Is cascade delete on a table more efficient than individual delete statements (executed in a single plsql block) ?
表上的级联删除是否比单个删除语句（在单个 plsql 块中执行）更有效？
回答by Adam Musch
What cascade deletedoes is issue individual delete statements.
什么cascade delete是发出单独的删除语句。
Examine the following test case:
检查以下测试用例：
create table parent
(parent_id number,
parent_name varchar2(30),
constraint parent_pk primary key (parent_id) using index);
create table child
(child_id number,
parent_id number,
child_name varchar2(30),
constraint child_pk primary key (parent_id, child_id) using index,
constraint child_fk01 foreign key (parent_id)
references parent (parent_id)
on delete cascade;
);
insert into parent
(parent_id, parent_name)
select object_id, object_name from dba_objects where rownum <= 10000;
begin
for i in 1..10
loop
insert into child
(child_id, parent_id, child_name)
select i, parent_id, parent_name
from parent;
end loop;
end;
/
exec dbms_stats.gather_table_stats (tabname => 'PARENT', cascade => true);
exec dbms_stats.gather_table_stats (tabname => 'CHILD', cascade => true);
exec dbms_monitor.session_trace_enable;
alter table child drop constraint child_fk01;
alter table child add constraint child_fk01 foreign key (parent_id)
references parent (parent_id) on delete cascade enable novalidate ;
delete from parent;
rollback;
In the trace file, you will find a line like this:
在跟踪文件中，您将找到如下一行：
delete from "<MY_SCHEMA_NAME>"."CHILD" where "PARENT_ID" = :1
END OF STMT
PARSE #6:c=0,e=182,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1293353992514766
EXEC #6:c=0,e=545,p=0,cr=2,cu=32,mis=1,r=10,dep=1,og=4,tim=1293353992515354
EXEC #6:c=0,e=233,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515644
EXEC #6:c=0,e=238,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515931
EXEC #6:c=0,e=252,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992516229
EXEC #6:c=0,e=231,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516507
EXEC #6:c=0,e=227,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516782
EXEC #6:c=0,e=244,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992517072
EXEC #6:c=0,e=219,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517337
EXEC #6:c=0,e=236,p=0,cr=3,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517622
EXEC #6:c=0,e=235,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517921
EXEC #6:c=0,e=229,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518196
EXEC #6:c=0,e=246,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992518487
EXEC #6:c=0,e=234,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518767
EXEC #6:c=6999,e=570,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992519383
That is Oracle issuing a delete statement against CHILDfor each record it's deleting in PARENT.
那是 Oracle 针对CHILD它在PARENT.
A different question would be which of the two are more efficient:
一个不同的问题是两者中哪一个更有效：
DELETE FROM CHILD WHERE PARENT_ID = 1;
DELETE FROM PARENT WHERE PARENT_ID = 1;
vs
对比
DELETE FROM PARENT WHERE PARENT_ID = 1;
both with on delete cascadeenabled. Suprisingly enough, in the first case above, Oracle will probe the foreign key index on the child table to see if any rows exist which would require a cascade. If no rows exist, Oracle does not execute the cascaded delete.
两者都on delete cascade启用。令人惊讶的是，在上述第一种情况下，Oracle 将探测子表上的外键索引，以查看是否存在需要级联的行。如果不存在行，Oracle 不执行级联删除。
回答by CloudyMarble
You can not compare both options like this. its not a performance issue but more design und structure.
你不能像这样比较两个选项。它不是性能问题，而是更多的设计和结构。
If you design your database using primary / foreign keys it will be easier to delete using cascading-deletes than searching manually where do you have foreign keys on which column and table and generate maching SQL statements.
如果您使用主键/外键设计数据库，则使用级联删除比手动搜索哪些列和表的外键并生成匹配的 SQL 语句更容易删除。
The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions
级联删除功能的主要优点是它允许您减少执行删除操作所需的 SQL 语句数量
回答by Benoit
If you want to cascade delete and don't have a foreign key defined, you can use something like this:
如果你想级联删除并且没有定义外键，你可以使用这样的东西：
DELETE FROM my_table
WHERE ROWID IN
( SELECT ROWID
FROM my_table
START WITH (condition_on_the_row_that_you_want_to_delete)
CONNECT BY PRIOR (primary_key) = (self_foreign_key)
)
