如何更快地删除? [英] How to delete faster?

问题描述

我有一个DB表，其中包含25亿条记录. 复制品多达1100万张. 删除这1100万条记录的最快方法是什么?

I have a DB table which consists of 2.5 billion records. There are duplicates to the tune of 11 million. Which is the fastest way to delete these 11 million records?

推荐答案

从多个副本中删除一个重复项是一项棘手的事情，并且由于有那么多记录，您就遇到了麻烦.

Deleting one duplicate from many is a tricky business, and with that many records, you have a problem.

一个选择是解决问题，然后将要保留的记录复制到新表中.您可以使用CREATE TABLE AS SELECT DISTINCT ... NOLOGGING语法，该语法将复制重复数据删除的记录，而无需使用事务日志，这会更快.填充新表后，删除/重命名旧表，然后将新表重命名.

One option is to turn the problem on its head, and copy the records you want to keep into a new table. You can use CREATE TABLE AS SELECT DISTINCT ... NOLOGGING syntax, which will copy your de-duplicated records without using the transaction log, which is much faster. Once your new table is populated, delete/rename the old one, and rename the new one into place.

请参见 http://www.databasejournal.com/features/oracle/article.php/3631361/Managing-Tables-Logging-versus-Nologging.htm

哦，请记住在新表上拍一个UNIQUE索引，这样就不会再发生这种情况了.

Oh, and remember to slap a UNIQUE index on the new table so this doesn't happen again.

故事的寓意是……从不永远不要使用DELETE来删除大量记录，这太慢了，因为它必须将所有已删除的记录存储在重做日志中.复制并切换或TRUNCATE.

The moral of the story is... never use DELETE for getting rid of large numbers of records, it's horrifyingly slow because it has to store all of the deleted records in the redo log. Either copy-and-switch, or TRUNCATE.

这篇关于如何更快地删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！