Oracle删除性能

Question

假设我有两个表：

create table parent (
  id 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)
)

父表很大，比如 300 万条记录。现在我运行删除语句：

delete from parent; //even without where clause

您能否解释一下执行此语句时实际发生的情况？哪里没有指定“ON DELETE CASCADE”选项，据我了解，这意味着如果子表包含对父 id 的引用，则从父表中删除应该失败。因此，这意味着在从父表中删除行之前，Oracle 应检查是否存在任何子记录。但这确实非常慢——它是逐行删除。

我说得对吗？如果没有，请解释Oracle在从父表删除时如何工作，并检查子表中是否没有留下孤儿？

Answer 1

这是一个测试。我的 PARENT 表有 100000 行。针对空 CHILD 表（在 PARENT_ID 上有索引）删除需要很长时间：

SQL> set timing on
SQL> delete from parent;

100000 rows deleted.

Elapsed: 00:00:07.24
SQL> 

让我们向 CHILD 中插入一些行。这将为 PARENT 中的每一行生成一行

SQL> insert into child
  2  select level, level from dual
  3  connect by level <= 100000;


100000 rows created.

Elapsed: 00:00:02.21
SQL> 

如果我们现在从父级中删除，它会立即失败。

SQL> delete from parent;
delete from parent
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.CHILD_FK1) violated - child record found


Elapsed: 00:00:00.14
SQL> 

然而，如果我们让 CHILD 中的每条记录都指向 PARENT 中的一条记录，则需要更长的时间……

SQL> update child set parent_id=99999;

100000 rows updated.

Elapsed: 00:00:09.65
SQL> commit;

SQL> delete from parent;
delete from parent
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.CHILD_FK1) violated - child record found


Elapsed: 00:00:07.32

众所周知，挂钟计时非常不可靠，但看起来时间量大致相同。碰巧的是，删除没有外键依赖项的父表的时间大致相同：

SQL> drop table child;

Table dropped.

Elapsed: 00:00:02.29
SQL> delete from parent;

100000 rows deleted.

Elapsed: 00:00:06.54
SQL> 

因此，基本上，检查外键约束的开销很小或没有开销

有一个附带条件：这是正确的提供外键列已建立索引。我删除了 CHILD_FK1_I 索引，在我输入此响应（即大约十分钟）的时间内，从父级删除语句仍未完成。

Answer 2

因为你的子表在parent_id上没有索引，所以每次需要检查子表是否存在时，oracle都被迫进行完整扫描。当您计划对多个进程执行此操作时，缺少此索引可能会引发的另一个问题是争用。如果没有对parent_id建立索引，子表将获得表锁，通常只有一行被锁定。

除此之外，向数据库发出删除行的命令是最糟糕的执行命令。如果你看看水下发生的事情（撤消、重做等），你会发现一切都发生得那么快，真是令人惊讶......