在處理交易型平台資料常常會遇到一個問題，我該如何清理資料表中老舊的資料，像是每天自動去清除三十天前的交易紀錄。
如果使用最簡單的 DELETE 語法，可能會影響線上交易並且產生大量的Transaction Log。
過往會建立Partition Table，然後透過搬動Parition分區到另一張資料表然後在 TRUNCATE TABLE清除掉。
但這樣需要先建立一個一模一樣的資料表，並且設定一樣的Partition Schema，會有點小麻煩。
在SQL Server 2016開啟，提供了TRUNCATE TABLE WITH PARTITION的語法能更方便的清除老舊的資料
TRUNCATE TABLE [資料表名稱] WITH(PARTITIONS([第幾個分區]))
EX:
TRUNCATE TABLE [資料表名稱] WITH(PARTITIONS(1))
TRUNCATE TABLE [資料表名稱] WITH(PARTITIONS(1,2,3))
TRUNCATE TABLE [資料表名稱] WITH(PARTITIONS(1,2, 4 to 8))
範例
Step1. 先建立以時間為分區的Partition Function & Partition Schema
(1) Partition Function
CREATE PARTITION FUNCTION [F_DAY] (datetime) AS RANGE LEFT FOR VALUES (N'2019-12-01',N'2019-12-02',N'2019-12-03',N'2019-12-04')
(2) Partition Schema
CREATE PARTITION SCHEME [S_DAY] AS PARTITION [F_DAY] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
(3) 自動擴充F_DAY、S_DAY
DECLARE @D_LAST DATETIME
DECLARE @I_CNT INT = 0
SELECT TOP 1 @D_LAST = CAST(v.value AS DATETIME)
FROM sys.partition_range_values AS V
LEFT JOIN sys.partition_functions AS F ON V.function_id = F.function_id
WHERE f.name = 'F_DAY'
ORDER BY value DESC
WHILE @I_CNT < 30 --自動產生30天的分區
BEGIN
SET @D_LAST = DATEADD(DAY,1,@D_LAST)
SET @I_CNT = @I_CNT + 1
ALTER PARTITION SCHEME S_Day NEXT USED [Primary]
ALTER PARTITION FUNCTION F_DAY() SPLIT RANGE(@D_LAST)
END
Step2. 建立測試資料表 & 塞入資料
CREATE TABLE T_DATA
(
Id INT IDENTITY(1,1) NOT NULL,
C1 NVARCHAR(10) NOT NULL,
C2 INT NOT NULL,
CreateTime DATETIME
CONSTRAINT [PK_T_DATA] PRIMARY KEY CLUSTERED
(
id ASC,CreateTime ASC
) ON [S_Day](CreateTime)
) ON [S_Day](CreateTime)
DECLARE @I_CNT2 INT = 0,@I_NO INT = 0
DECLARE @D_START DATETIME = '2019-12-01'
DECLARE @D_INSERT DATETIME
WHILE @I_CNT2 < 100000
BEGIN
SET @I_CNT2 = @I_CNT2 + 1
SET @I_NO = ROUND(@I_CNT2/(100000/30),0)
SET @D_INSERT = CONVERT(DATETIME,FORMAT(DATEADD(DAY,@I_NO,@D_START),'yyyy-MM-dd') + FORMAT(GETDATE(),' HH:mm:ss'))
INSERT INTO T_DATA(C1,C2,CreateTime)
VALUES(CONVERT(NVARCHAR,@I_CNT2),@I_CNT2,@D_INSERT)
END
Step3. 查詢分區資訊
SELECT OBJECT_NAME(P.object_id) AS TableName,I.name AS IndexName,R.value AS BoundaryValue
,P.partition_number,S.name AS PartitionSchema,F.type_desc,P.row_count
FROM sys.dm_db_partition_stats AS P
JOIN sys.indexes AS I ON I.object_id = P.object_id AND I.index_id = P.index_id
JOIN sys.partition_schemes AS S ON I.data_space_id = S.data_space_id
JOIN sys.partition_functions AS F ON S.function_id = F.function_id
LEFT JOIN sys.partition_range_values AS R ON F.function_id = R.function_id AND R.boundary_id = P.partition_number
WHERE OBJECT_NAME(P.object_id) = 'T_DATA'
ORDER BY TableName,IndexName,partition_number
Step4. 清除前兩個分區後合併分區
TRUNCATE TABLE T_DATA WITH(PARTITIONS(1,2))
DECLARE @D_FIRST DATETIME
SELECT TOP 1 @D_FIRST = CAST(v.value AS DATETIME)
FROM sys.partition_range_values AS V
LEFT JOIN sys.partition_functions AS F ON V.function_id = F.function_id
WHERE f.name = 'F_DAY'
ORDER BY value
ALTER PARTITION FUNCTION F_DAY() MERGE RANGE(@D_FIRST)
ALTER PARTITION FUNCTION F_DAY() MERGE RANGE(DATEADD(DAY,1,@D_FIRST))
Step5. 再查看一次分區資訊
可以看到最前面的兩個分區已經被刪除並且收回Partition分區
透過Truncate Table With Partition的方式可以快速並且方便的清除資料並回收分區
也不會影響到線上活動
留言列表
