从空表中删除永久
我有一个空表，之前有大量行。
该表大约有 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
任何知道为什么这些会花费这么长时间以及如何解决它的想法将不胜感激！
I have an empty table that previously had a large amount of rows.
The table has about 10 columns and indexes on many of them, as well as indexes on multiple columns.
DELETE FROM item WHERE 1=1
This takes approximately 40 seconds to complete
SELECT * FROM item
this takes 4 seconds.
The execution plan of SELECT * FROM ITEM shows the following;
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
any idea why these would be taking so long and how to fix it would be greatly appreciated!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
一种可能性是锁。也就是说，表中有一行已被另一个删除提交并锁定。你的删除坐在锁上等待。当锁定事务提交后，您的删除就可以完成。
第二种可能性是您首先运行删除，这会将块从磁盘提取到缓存中（这需要时间）。当选择运行时，数据位于缓存中，因此运行速度更快。我认为这种情况不太可能发生，因为您选择的统计数据表明“5252 物理读取”，因此它没有从 SGA 缓存中获取它们。不过，可能涉及磁盘缓存。
第三种可能性是存在一个 BEFORE/AFTER DELETE 触发器（不是 FOR EACH ROW）执行了某些操作。
第四种可能性是 DELETE 导致块清除延迟。当行实际被删除时，如果它们在提交之前写入磁盘，它们仍然具有锁定/事务信息。您的删除随之而来，读取块，查看现已过时的交易信息，将其删除并重新写入块。
第五个可能性是争用。也许在删除的同时发生了更多事情。
有很多可能性。如果您可以重现它，则使用等待事件进行跟踪并通过 TKPROF 运行它。
One possibility is a lock. That is there was a row in the table which had been committed and locked by another delete. Your delete sat and waited on the lock. When the locking transaction was committed, your delete was then able to finish.
A second possibility is that you ran the delete first, which fetched the blocks from disk into the cache (which took time). When the select ran, the data was in the cache and so ran quicker. I think this is less likely as you select stats indicated "5252 physical reads", so it wasn't getting them from the SGA cache. It is possible that a disk cache was involved though.
A third possibility is that there is a BEFORE/AFTER DELETE trigger (not FOR EACH ROW) which did something.
A fourth possibility is that the DELETE resulted in a delayed block cleanout. When the rows were actually deleted, if they were written to disk prior to being committed they'd still have the lock/transaction info. Your delete comes along, reads the blocks, sees the now outdated transaction info, removes it and re-writes the block.
A fifth possibility is contention. Maybe there was just more happening at the same time as the delete.
Lots of possibilities. If you can reproduce it, then do a trace with wait events and run it through TKPROF.
如果您的表以前填充了大量数据，那么 Oracle 会扫描它直到高水位线，即使它现在没有数据。您可以使用 TRUNCATE 语句来重置 HWM。
也在 AskTom
If your table previously was filled with a large amount of data, then Oracle will scan it up to the high water mark, even if it has no data NOW. You may use TRUNCATE statement to reset HWM.
Also on AskTom
select只是进行全表扫描。另一方面（在 Oracle 中）删除必须将整个删除的行存储在回滚段中，以便允许您稍后撤消更改（因此它甚至可能比插入慢）。
您可以在 Ask Tom 论坛上找到与此相关的非常长且有用的讨论。根据您的业务案例，也许您可以应用更多技术。
http://asktom.oracle .com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689
Select is just doing a full table scan. Delete on the other hand (in Oracle ) will have to store the whole deleted rows in the rollback segments in order to allow you to undo the changes later (so it can be slower even than the insert).
You can find a very long and useful discussion related to that on Ask Tom forum. Depending on your business case maybe you can apply more techniques.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2345591157689
如果表上有很多索引，那么删除将需要时间，因为索引中的每个条目也需要删除。索引加速读取过程并减慢写入或其他修改过程（因此快速选择）。-- 编辑：抱歉没有完全阅读问题。如果表为空，则不可能是索引问题。或者如果是的话我会感到惊讶--
If you have many indexes on the table then a delete is going to take time because each entry in the index needs removing as well. Indexes speed up read processes and slows down write or other modification processes (hence the speedy select).-- EDIT: Sorry didn't read the questions fully. If the table is empty then it can't be an index issue. or I'll be surprised if it was --
如果您定期删除表中的所有行，“截断表 XXX”通常比批量删除快得多。也许可以尝试一下，看看你能多快获得它。
If you are regularly deleting all rows from a table, 'truncate table XXX' is typically much faster than a mass-delete. Maybe try that and see how much faster you can get it.