- Partition Truncate Table2017년 04월 14일 14시 36분 43초에 업로드 된 글입니다.작성자: DE 군고구마반응형
들어가며
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
반응형'Database > MSSQL Server' 카테고리의 다른 글
The data types xml and nvarchar are incompatible in the add operator. (0) 2018.03.14 SQL Server 2016 - JSON parser (0) 2017.06.01 Alwayson Automatic synchronization Login(Alwayson 로그인 자동 동기화) ② (0) 2016.08.25 DbMgrPartnerCommitPolicy::SetSyncState (0) 2016.08.11 SQL Server - License Core Limitaion (40코어 이상 인식) (0) 2016.08.09 다음글이 없습니다.이전글이 없습니다.댓글