|
本帖最后由 szxiaocong 于 2018-2-2 14:14 编辑
8000w左右的总数据量,删除1500w 左右的记录
大家有什么好的思路,想法,就delete 语句 ,表结构设计,
多多发言。
应该是个很不错的case
#A(guid uniqueidentifier)
---#A 1500w
table_all (guid uniqueidentifier, col1 varchar(36).....)
---table_all 8000w, guid pk, clustered index,
----solution 1
WHILE 1 = 1
BEGIN
DELETE TOP(5000)
FROM table_all
WHERE guid in (SELECT guid FROM #A1 );
IF @@rowcount < 5000 BREAK;
END
GO
#A(guid uniqueidentifier, rowid int identity pk)
---solution2
declare @i int
set @i = 1 ---不断修改,循环
while @i < 100000 ---不断修改,循环
begin
select * into #T from #A1 where rowid >= @i and rowid < @i + 10000
delete a
--select a.guid
FROM table_all a inner join #T t on a.guId = t.guId
drop table #T
set @i = @i + 10000
end
GO
|
|