Oracle：自下而上删除
假设我有两个表：
create table parent (
number not null,
constraint parent_pk primary key(id),
)
create table child (
id number not null,
parent_id number not null,
constraint child_pk primary key(id),
constraint child_fk1 foreign key(parent_id)
references parent(id)
)
我听说过自下而上的删除方法。像这样的事情：
DELETE FROM child where parent_id IN (SELECT id FROM parent WHERE ...);
DELETE FROM parent WHERE ...;
我还看到了以下版本：
DELETE FROM child c where exists (SELECT 1 FROM parent p WHERE c.parent_id=p.id AND ...);
DELETE FROM parent WHERE ...;
还存在 ON DELETE CASCADE 选项。您能比较一下上述方法的性能吗？
Say I have two tables:
create table parent (
number not null,
constraint parent_pk primary key(id),
)
create table child (
id number not null,
parent_id number not null,
constraint child_pk primary key(id),
constraint child_fk1 foreign key(parent_id)
references parent(id)
)
I've heard about bottom-up delete method. Something like this:
DELETE FROM child where parent_id IN (SELECT id FROM parent WHERE ...);
DELETE FROM parent WHERE ...;
I've also seen the following version:
DELETE FROM child c where exists (SELECT 1 FROM parent p WHERE c.parent_id=p.id AND ...);
DELETE FROM parent WHERE ...;
There are also exist ON DELETE CASCADE option. Could you please compare the performance of the mentioned ways?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于您对算法感兴趣，您的问题似乎归结为
IN和EXISTS之间的区别，因为您的两个示例是相同的，除了从更改为CHILD表中删除时，INEXISTS。多年来，已经有很多关于这种差异的文章，但本质上，
IN通常用于比较器数量较少的情况，而EXISTS对于返回较大数量的比较器（特别是如果这些值包含大量重复项）。IN必须评估每个返回的比较器，而EXISTS在遇到第一个匹配时满足。也有例外，如果你用谷歌搜索它们，你就会找到它们，但总的来说，这似乎是正确的。
Tom Kyte（Oracle 副总裁）有一个很好的答案，并在此处进行了解释：
http://asktom.oracle.com/pls/asktom/f?p=100:11:2148775836129778::::P11_QUESTION_ID:953229842074
TechRepublic在这里也有一个很好的解释：
http:// /www.techrepublic.com/article/oracle-tip-understand-the-difference- Between-in-and-exists-in-subqueries/5297080
希望这有帮助...
As you are interested in the algorithm, what your question seems to boil down to is the difference between
INandEXISTSas your two examples are the same save for theINchanging to anEXISTSin the delete from theCHILDtable.There has been quite a lot written about this difference over the years but in essence
INis generally used where the number of comparators is small whereasEXISTSis more efficient for subqueries returning a larger number of comparators (especially if those values contain a large number of duplicates).INhas to evaluate every returned comparator whileEXISTSis satisfied when it encounters the first match.There are exceptions to this and if you google for them then you will find them but on the whole this seems to hold true.
Tom Kyte (Oracle VP) has a very good answer with explanations here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:2148775836129778::::P11_QUESTION_ID:953229842074
TechRepublic also has a good explanation here:
http://www.techrepublic.com/article/oracle-tip-understand-the-difference-between-in-and-exists-in-subqueries/5297080
Hope this helps...