删除/删除包含数百万条记录且没有索引的表 [英] Remove / Delete Table with millions of records and no indexes

问题描述

我有一张包含数亿条记录的表格，并且没有在那里创建索引。





现在我需要删除表格从生产中不打断数据库。



我试过以下但都失败了，



1。 ）删除约束

2.）截断表

3.）创建索引以加快处理速度全部失败



是否有快速删除数据的方法？

I have a table with hundred million records and which has no indexed created there.


Now i need to remove the table from production without interrupting the database.

I have tried following but it all fails,

1.) Removing constraints
2.) Truncate table
3.) Creating index for faster processing it all fails

Is there any method for quick deleting of the data?

推荐答案

创建另一个具有与原始表相同模式的表，这次使用所有索引集。



一切准备就绪后，在原始表上执行快速表重命名语句，并使用原始表名重命名新表。



这个重命名表根本不会影响性能。



以下是要遵循的步骤：

1）创建一个新表T able_A_New具有与原始表格相同的模式Table_A

2）向表Table_A_New添加所需的索引

3）删除Table_A
4）在表Table_A上执行重命名语句

EXEC sp_rename'Table_A'，'Table_A_Old';

5）在新表上执行重命名语句Table_A_New到原始名称

EXEC sp_rename'Table_A_New'，'Table_A';

6）将约束添加回Table_A

Create another table with the same schema as that of your original table and this time with all indexing set.

Once everything is ready execute a quick table rename statements on your original table to something else and rename the new table with the original table name.

This renaming tables doesn't impact performance at all.

Here are the steps to follow:
1) Create a new table "Table_A_New" with same schema as that of original table "Table_A"
2) Add required indexing to table "Table_A_New"
3) Remove any constraints on "Table_A"
4) Execute rename statement on table "Table_A"
EXEC sp_rename 'Table_A', 'Table_A_Old';
5) Execute rename statement on new table "Table_A_New" to original name
EXEC sp_rename 'Table_A_New', 'Table_A';
6) Add constraints back to "Table_A"

这篇关于删除/删除包含数百万条记录且没有索引的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！