Oracle:自下而上删除 [英] Oracle: Bottom-up delete

问题描述

说我有两个表:

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 ...;

我也看到了以下版本:

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选项.您能否比较上述方法的性能?

There are also exist ON DELETE CASCADE option. Could you please compare the performance of the mentioned ways?

推荐答案

当您对算法感兴趣时，您的问题似乎归结为IN和EXISTS之间的区别，因为您的两个示例是在CHILD表的删除操作中，将IN更改为EXISTS的相同保存.

As you are interested in the algorithm, what your question seems to boil down to is the difference between IN and EXISTS as your two examples are the same save for the IN changing to an EXISTS in the delete from the CHILD table.

多年来，已经有很多关于这种差异的文章，但是实质上，IN通常用于比较器数量少的地方，而EXISTS对于返回大量比较器的子查询更有效(特别是如果这些值包含大量重复项.

There has been quite a lot written about this difference over the years but in essence IN is generally used where the number of comparators is small whereas EXISTS is more efficient for subqueries returning a larger number of comparators (especially if those values contain a large number of duplicates).

IN必须对每个返回的比较器进行求值，而EXISTS在遇到第一个匹配项时必须满足.

IN has to evaluate every returned comparator while EXISTS is satisfied when it encounters the first match.

对此有一些例外，如果您在Google上搜索它们，就会找到它们，但总的来说，这似乎是正确的.

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)有一个很好的答案，并在下面给出了解释: http://asktom.oracle. com/pls/asktom/f?p = 100:11:2148775836129778 ::::: P11_QUESTION_ID:953229842074

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在这里也有很好的解释: http://www.techrepublic.com/article/oracle-tip-understanding-the-difference-between-in-and-exists-in-subqueries/5297080

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...

这篇关于Oracle:自下而上删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！