从空表中删除数据需要很长时间。

Question

我有一张之前拥有大量行的空表。

这个表大约有10列，其中许多列都有索引，并且有多列的索引。

DELETE FROM item WHERE 1=1

这需要大约40秒钟才能完成。

SELECT * FROM item

这需要4秒钟。

SELECT * FROM ITEM的执行计划如下：

SQL> select * from midas_item;

no rows selected

Elapsed: 00:00:04.29

Execution Plan
----------------------------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=123 Bytes=73
      80)

1    0   TABLE ACCESS (FULL) OF 'MIDAS_ITEM' (Cost=19 Card=123 Byte
      s=7380)





Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   5263  consistent gets
   5252  physical reads
      0  redo size
   1030  bytes sent via SQL*Net to client
    372  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

任何关于这些为什么需要花费如此长时间以及如何解决它的想法都将不胜感激！

- Will

---

请问这个表有多少数据（行）？ - bragboy

这个表是否被其他空表通过外键引用？ - dpbradley

上次我清空这个表时，它大约有20万行。但在过去，它的行数远远超过这个数字（可能在其生命周期内达到了200万行）。 - Will

你已经提供了SELECT的计划和统计信息，但是DELETE呢？ - Jeffrey Kemp

Answer 1

一种可能是锁定。这意味着表中的一行已经被提交并被另一个删除操作锁定。您的删除操作等待锁。当锁定事务被提交后，您的删除操作才能完成。

第二种可能是您首先运行了删除操作，从磁盘将块加载到缓存中（这需要时间）。当选择操作运行时，数据在缓存中，因此运行速度更快。我认为这种情况不太可能，因为您的选择统计信息显示“5252个物理读取”，因此它没有从SGA缓存中获取它们。但是可能涉及磁盘缓存。

第三种可能性是存在BEFORE/AFTER DELETE触发器（而不是FOR EACH ROW），该触发器执行了某些操作。

第四种可能性是DELETE操作导致延迟块清除。实际删除行时，如果它们在提交之前写入磁盘，则仍具有锁定/事务信息。您的删除操作读取块，看到现在已过时的事务信息，将其删除并重新写入块。

第五种可能性是争用。也许在删除操作同时发生了更多的事情。

有很多可能性。如果您可以重现它，请使用等待事件进行跟踪，并通过TKPROF运行它。

- Gary Myers

Answer 2

如果您的表之前装满了大量数据，即使现在没有数据，Oracle也会扫描到高水位线。您可以使用TRUNCATE语句重置HWM。 AskTom上也有相关内容。

- user38123

---

这也是我的第一反应 - 但我不确定它能解释为什么DELETE比SELECT慢那么多。 - Dave Costa

我最初使用TRUNCATE TABLE清除内容，但即使在使用后仍然很慢。 - Will

Answer 3

如果表上有许多索引，则删除操作需要花费时间，因为每个索引中的条目都需要被删除。索引加速读取过程，减慢写入或其他修改过程（因此选择速度较快）。--编辑：抱歉没有完全阅读问题。如果表为空，则不可能是索引问题。否则我会感到惊讶--

- super9

---

好的，选择操作本身仍需要4秒钟的时间，但是相比之下确实很快。 - Will

但他说表已经没有行了，所以DELETE不应该修改索引。 - Dave Costa

Answer 4

选择语句只是进行了全表扫描。另一方面，删除操作（在Oracle中）需要将整个被删除的行存储在回滚段中，以便稍后可以撤消更改（因此甚至可能比插入操作更慢）。您可以在Ask Tom论坛上找到与此相关的非常长而有用的讨论。根据您的业务情况，也许可以应用更多技术。http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689

- Cornel Creanga

Answer 5

如果您经常从表中删除所有行，使用“truncate table XXX”通常比大规模删除要快得多。也许可以尝试一下，看看能够获得多快的速度。

- Dark Castle

---

确实如此 - 但请确保您理解TRUNCATE的作用（释放空间，提交事务）。 - Dave Costa

我使用了truncate来删除所有行。 - Will