Partition Truncate Table

들어가며

SQL Server 2016 부터 파티션 단위로 Truncate가 가능 합니다. 

다음의 블로그를 참고하여, 만들었으며 2016 기능을 사용해 보고자 포스트를 작성하였습니다.

참고 : https://www.mssqltips.com/sqlservertip/4436/sql-server-2016-truncate-table-with-partitions/

위의 블로그의 경우 Partition 테이블의 최대 장점이 스트라이프로 데이터를 넣는 것이므로, NDF 파일과 파일그룹을 여러개 생성해서 만들고 있는데요.

이 부분은 제외하고, 단순히 파티션만 나눠서 Truncate 하는 부분만 포스팅 하고자 합니다. (기능만 보면 되므로) 

제가 알기로 대용량의 DB를 관리 할 경우 테이블을 일자 또는 월별로 쪼개서 각각 파일그룹과 NDF를 생성하여, 기간이 일정 부분 지나면, 삭제하는 식으로 운영 하였다면

이 Partition Truncate를 통하여, 한 테이블에 넣고 그냥 해당 파티션만 Truncate 날려버리면 되므로 운영 포인트가 많이 줄어 들게 될 것 같습니다. 

시나리오

Step 1 

파일그룹 및 NDF 파일 생성 - 파티션용으로 새롭게 만듭니다.

USE [master] GO -- 파일 그룹 생성 ALTER DATABASE [TEST] ADD FILEGROUP [PARTITON_GROUP] GO   -- NDF파일 생성 ALTER DATABASE [TEST] ADD FILE ( NAME = N'PARTITION', FILENAME = N'C:\MSSQL_DATA\PARTITION_GROUP.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PARTITON_GROUP] GO

Step 2

파티션 함수 및 파티션 스키마 생성 

USE TEST GO CREATE PARTITION FUNCTION PARTITION_FN(DATETIME) AS   RANGE RIGHT FOR VALUES   ('2017-01-01') GO     CREATE PARTITION SCHEME PARTITION_SH AS   PARTITION PARTITION_FN  TO   (    [PARTITON_GROUP],    [PRIMARY] ) GO

Step 3

테이블 생성 - 파티션 스키마 명을 기재하고, 어떤 값을 기준으로 나눌 것인지 명시 합니다.

USE TEST GO   CREATE TABLE TEST_Partition (  USERID                INT ,Name                  VARCHAR(20) ,Reg_date              DATETIME )ON PARTITION_SH (Reg_date) GO

Step 4

파티션이 정상적으로 생성되었는지 확인

SELECT  table_name             = object_name(PT.object_id)         ,       index_name             = IDX.name         ,       part_schema            = PS.name         ,       part_function  = PF.name         ,       boundary               = CASE WHEN PF.boundary_value_on_right = 1 THEN 'Right(<)' ELSE 'Left(=<)' END         ,       boundary_value = ISNULL(CONVERT(nvarchar(100),RV.value,121),N'LargePartition')         ,       PT.rows         ,       AU.total_pages         ,       AU.used_pages         ,       PT.*   FROM                 sys.allocation_units   AS AU  INNER  JOIN    sys.partitions                AS PT                               ON      AU.container_id = PT.partition_id  INNER  JOIN    sys.indexes                           AS IDX                               ON      PT.object_id   = IDX.object_id                               AND     PT.index_id            = IDX.index_id  INNER  JOIN    sys.partition_schemes  AS PS                               ON      PS.data_space_id = IDX.data_space_id   LEFT  JOIN    sys.partition_functions       AS PF                               ON      PS.function_id = PS.function_id   LEFT  JOIN    sys.partition_range_values    AS RV                               ON      RV.function_id = PF.function_id                               AND     RV.boundary_id = PT.partition_number  WHERE  PT.object_id = object_id('TEST_Partition')

Step 5 

아래의 스크린샷을 보면, 둘 다 Rows가 0으로 표시 됩니다. 현재 파티션에 들어있는 row가 없기 때문입니다.

3개의 INSERT를 통해서, 데이터를 넣어보겠습니다.

INSERT INTO TEST_Partition VALUES (1,'주형권01','2017-01-03 02:03') INSERT INTO TEST_Partition VALUES (2,'주형권02','2016-12-03 02:03') INSERT INTO TEST_Partition VALUES (6,'주형권03','2018-12-03 02:03')

위의 Rows를 보시면 증가했음을 보실 수 있습니다. 부등호에 의해서 2016년도 날짜로 표기된 데이터만 파티션에 들어가고, 나머지는 LargePartition에 들어갑니다.

현재가 2017-04-14일 이므로, 2016년도 데이터가 필요 없다고 판단되어, 2017-01-01 이전의 데이터를 Truncate 시켜야 한다고 가정 하면 다음과 같이 합니다.

Step 6

파티션 Truncate 로 데이터 삭제 

- 아래의 Truncate 구문에서 어떤 파티션을 지울 것인지 지정하는 곳이 있습니다. 이것은 위의 파티션 테이블을 조회하는 쿼리에서 partition_Number로 조회 가능 합니다.

TRUNCATE TABLE TEST_Partition WITH (PARTITIONS (1)); GO

현재 보면 Partiotion_number가 1인 2017-01-01 이전의 데이터는 row가 0건 입니다.

실제로 데이터는 조회하면, 우리가 지우고자 했던 데이터만 지워졌음을 볼 수 있습니다. 

이는 Truncate 테이블이 모든 데이터를 지우는 것에 비해 Partition Truncate는 지정된 파티션만 삭제 함을 알 수 있습니다.

그 밖에...

1. 파티션 Truncate 는 여러개의 Partition을 삭제 가능 합니다. 범위로 Ttruncate 됩니다. 아래와 같이 지정해주시면 됩니다. 

TRUNCATE TABLE PartitionDemo2016 WITH (PARTITIONS (2 TO 3)); GO