[MSSQL] 파티션 테이블

기존에 가지고 있던 특정 테이블을 파티션테이블로 교체하는 작업 쿼리 기록.

파일그룹을 분리할지 기본 파일그룹에 추가하여 사용할지를 먼저 정해야한다. DB서버에 드라이브가 따로 분리되어있는 경우는 파일그룹을 분리하여 운영하면 디스크 부하를 줄일 수 있으나, 만약 DB 서버 드라이브가 1개라면 크게 의미가 없다.

소요시각을 확인하기 위해서, 로그 기록용 아래 변수를 선언해두고 사용하기로 한다.

DECLARE @V_LOG_DESC VARCHAR(1000)

 

앞서 말했던 파일그룹 분리 케이스의 경우 1~2를 수행해야하는데, 기본 그룹 PRIMARY 를 사용할 경우 생략한다.

1. 파일그룹 생성
   

   ALTER DATABASE haileydb ADD FILEGROUP FG_MSG_LOG ;
   --ALTER DATABASE haileydb ADD FILEGROUP FG_MSG_LOG1;
   --ALTER DATABASE haileydb ADD FILEGROUP FG_MSG_LOG2 ;
   --ALTER DATABASE haileydb ADD FILEGROUP FG_MSG_LOG3 ;
   SET @V_LOG_DESC = CONCAT('--- 파일그룹 생성 완료 : ', CONVERT(VARCHAR, GETDATE(), 120) );
   PRINT @V_LOG_DESC;

    
2. 파일 생성
   

   ALTER DATABASE haileydb ADD FILE ( NAME = N'PF_MSG_LOG', FILENAME = N'C:\HAILEY_PF_DATA\PF_MSG_LOG.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP FG_MSG_LOG ;
   --ALTER DATABASE haileydb ADD FILE ( NAME = N'PF_MSG_LOG_01', FILENAME = N'C:\HAILEY_PF_DATA\PF_MSG_LOG_01.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP FG_MSG_LOG1 ;
   --ALTER DATABASE haileydb ADD FILE ( NAME = N'PF_MSG_LOG_02', FILENAME = N'C:\HAILEY_PF_DATA\PF_MSG_LOG_02.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP FG_MSG_LOG2 ;
   --ALTER DATABASE haileydb ADD FILE ( NAME = N'PF_MSG_LOG_03', FILENAME = N'C:\HAILEY_PF_DATA\PF_MSG_LOG_03.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP FG_MSG_LOG3 ;
   SET @V_LOG_DESC = CONCAT('--- 파일 생성 완료 : ', CONVERT(VARCHAR, GETDATE(), 120) );
   PRINT @V_LOG_DESC;

    
3. 파티션 함수 생성
    ( 보관일 기준이 90일이면 90개 )
   파티션 함수로 실제 테이블을 나눌 기준을 정의한다. 숫자/일자를 기준으로 하며, 기준이 될 특정컬럼 값이 필요하다.
   로그테이블의 경우 일자별로 또는 월별로 로그 기록 일자를 기준으로 파티션을 나눈다.범위의 값을 정해서 파티션 함수를 선언해야하는데, 일자 값이 많아서 아래와같이 여러 데이터를 모아서 동적쿼리를 실행하여 파티션 함수를 생성했다. 파티션 함수를 생성/변경하기 위해서는 db_owner 또는 db_ddladmin 권한이 필요하며 권한이 없으면  15151 파티션 함수 '[함수명]'이(가) 없거나 권한이 없어서 변경할 수 없습니다. 라는 메시지와 함께 실패한다.

   DECLARE @P_REMAIN INT = 90 
   ,@V_QRYSTR NVARCHAR(2000) = concat(' CREATE PARTITION FUNCTION FN_MSG_LOG(DATETIME) AS RANGE RIGHT FOR VALUES ( ''', convert(varchar(10), getdate() + 1, 120), '''' )
   ;
   WITH C1 AS (
   SELECT TOP(@P_REMAIN) CONVERT(VARCHAR(10), C.DAY_DATE, 120) AS DAY_DATE
   FROM COM.CALENDAR C 
   WHERE C.DAY_DATE BETWEEN GETDATE() - @P_REMAIN AND GETDATE()
   )
   SELECT @V_QRYSTR = concat(@V_QRYSTR, (SELECT CONCAT(',''', C1.DAY_DATE, '''') FROM C1 ORDER BY C1.DAY_DATE FOR XML PATH('')) , ')' )
   --FROM C1
   ;
   PRINT @V_QRYSTR;
   SET @V_LOG_DESC = CONCAT('--- 파티션 함수 생성 쿼리 : ', @V_QRYSTR );
   PRINT @V_LOG_DESC;
   EXEC SP_EXECUTESQL @V_QRYSTR;
   SET @V_LOG_DESC = CONCAT('--- 파티션 함수 생성 완료 : ', CONVERT(VARCHAR, GETDATE(), 120) );
   PRINT @V_LOG_DESC;

   
   
   범위 지정 시 LEFT / RIGHT 옵션을 지정하게되는데 이는 지정한 범위 외에 기본 파티션이 LEFT의 경우 가장 작은것의 왼쪽. RIGHT 의 경우 가장 큰 값 오른쪽에 위치 하게된다는 뜻 같다.
   
   
4. 파티션 스키마 생성
   

   CREATE PARTITION SCHEME PS_MSG_LOG AS PARTITION FN_MSG_LOG 
   ALL TO ('PRIMARY')
   ;
   -- 만약 PRIMARY 파일그룹을 사용하지 않을 경우 또는 각 범위별로 다른 파일그룹을 지정해야하는 경우
   CREATE PARTITION SCHEME PS_MSG_LOG AS PARTITION FN_MSG_LOG 
   TO (PG_MSG_LOG_01, PG_MSG_LOG_02, 'PRIMARY')
   ;
   
   SET @V_LOG_DESC = CONCAT('--- 파티션 스키마 생성 완료 : ', CONVERT(VARCHAR, GETDATE(), 120) );
   PRINT @V_LOG_DESC;

   
   
   파티션 스키마를 생성해야 실제 바운더리와 파일그룹을 연결시킬 수 있다. 파티션함수에서 정의한 바운더리 수 + 1 개만큼의 파일그룹을 지정해주어야 하며, 모든 바운더리를 한 파일그룹에 사용하는 경우 ALL TO 로 명시하면 간단하다.
   
   
5. 기존 로그 테이블 이름변경
   

   EXEC SP_RENAME 'LOGS.MSG_LOG','MSG_LOG_OLD';

   기존에 존재하던 테이블을 신규 파티션 테이블로 교체해야해서, RENAME 으로 이름변경하여 데이터 백업 후 신규 테이블을 생성한다.
    
6. 파티션 테이블 생성
   

   CREATE TABLE haileydb.LOGS.MSG_LOG (
   SEQ_NO float NOT NULL,
   CREATE_BY varchar(32) COLLATE Korean_Wansung_CI_AS NULL,
   CREATE_DATE datetime DEFAULT getdate() NOT NULL,
   API_NO varchar(50) COLLATE Korean_Wansung_CI_AS NULL,
   MSG_TYPE varchar(3) COLLATE Korean_Wansung_CI_AS NULL,
   TR_NO varchar(60) COLLATE Korean_Wansung_CI_AS NULL,
   TR_DT varchar(8) COLLATE Korean_Wansung_CI_AS NULL,
   TR_TIME varchar(6) COLLATE Korean_Wansung_CI_AS NULL,
   CHNL_NO varchar(10) COLLATE Korean_Wansung_CI_AS NULL,
   DVC_NO varchar(10) COLLATE Korean_Wansung_CI_AS NULL,
   RST varchar(1) COLLATE Korean_Wansung_CI_AS NULL,
   RST_CD varchar(10) COLLATE Korean_Wansung_CI_AS NULL,
   RST_MSG nvarchar(100) COLLATE Korean_Wansung_CI_AS NULL,
   MSG ntext COLLATE Korean_Wansung_CI_AS NULL,
   CONSTRAINT PK_MSG_LOG PRIMARY KEY (SEQ_NO,CREATE_DATE) -- 파티션 키 값이 무조건 포함되어야함!
   ) ON PS_MSG_LOG(CREATE_DATE);
   CREATE NONCLUSTERED INDEX MSG_LOG_IDX_API_NO ON haileydb.LOGS.MSG_LOG (API_NO);
   CREATE NONCLUSTERED INDEX MSG_LOG_IDX_RST ON haileydb.LOGS.MSG_LOG (RST, RST_CD);
   CREATE NONCLUSTERED INDEX MSG_LOG_IDX_TR_NO ON haileydb.LOGS.MSG_LOG (TR_NO);
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API 인바운드 로그 파티션 테이블', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'순번', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'SEQ_NO';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'생성자', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'CREATE_BY';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'생성일시 (파티션 키)', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'CREATE_DATE';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API번호', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'API_NO';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'메시지유형(REQ, RES)', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'MSG_TYPE';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'거래고유번호', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'TR_NO';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'거래발생일자', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'TR_DT';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'거래발생시간', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'TR_TIME';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'채널번호', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'CHNL_NO';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'디바이스(POS)번호', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'DVC_NO';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'응답결과', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'RST';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'응답코드', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'RST_CD';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'응답메시지', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'RST_MSG';
   EXEC haileydb.sys.sp_addextendedproperty @name=N'MS_Description', @value=N'전문', @level0type=N'Schema', @level0name=N'LOGS', @level1type=N'Table', @level1name=N'MSG_LOG', @level2type=N'Column', @level2name=N'MSG';
   SET @V_LOG_DESC = CONCAT('--- 파티션 테이블 생성 완료 : ', CONVERT(VARCHAR, GETDATE(), 120) );
   PRINT @V_LOG_DESC;

 

생성된 파티션 확인 

SELECT
DISTINCT 
object_name(PT.object_id) AS table_name
, PS.name AS part_schema
, PF.name AS part_function
, RV.value AS boundary_value
, PT.rows -- 파티션에 들어있는 데이터 수
FROM
SYS.PARTITIONS PT
--INNER JOIN SYS.ALLOCATION_UNITS AU
-- ON AU.CONTAINER_ID = PT.PARTITION_ID
LEFT JOIN SYS.INDEXES IDX
ON PT.OBJECT_ID = IDX.OBJECT_ID
AND PT.INDEX_ID = IDX.INDEX_ID
LEFT JOIN SYS.PARTITION_SCHEMES PS
ON PS.DATA_SPACE_ID = IDX.DATA_SPACE_ID
LEFT JOIN SYS.PARTITION_FUNCTIONS PF
ON PS.FUNCTION_ID = PF.FUNCTION_ID
LEFT JOIN SYS.PARTITION_RANGE_VALUES RV
ON RV.FUNCTION_ID = PF.FUNCTION_ID
AND RV.BOUNDARY_ID = PT.PARTITION_NUMBER
WHERE 1=1
AND PT.OBJECT_ID = OBJECT_ID('LOGS.MSG_LOG')

 

삭제 쿼리

삭제는 순서대로 진행해야 한다

-- 1. 테이블
DROP TABLE LOGS.MSG_LOG;
-- 2. 스키마
DROP PARTITION SCHEME PS_MSG_LOG; 
-- 3. 함수
DROP PARTITION FUNCTION FN_MSG_LOG;
-- 4. 파일
ALTER DATABASE haileydb REMOVE FILE [PF_MSG_LOG];
--ALTER DATABASE haileydb REMOVE FILE [PF_MSG_LOG_01];
--ALTER DATABASE haileydb REMOVE FILE [PF_MSG_LOG_02];
--ALTER DATABASE haileydb REMOVE FILE [PF_MSG_LOG_03];
-- 5. 파일그룹
ALTER DATABASE haileydb REMOVE FILEGROUP FG_MSG_LOG ;
--ALTER DATABASE haileydb REMOVE FILEGROUP FG_MSG_LOG1 ;
--ALTER DATABASE haileydb REMOVE FILEGROUP FG_MSG_LOG2 ;
--ALTER DATABASE haileydb REMOVE FILEGROUP FG_MSG_LOG3 ;테이블 파티션 단위로 삭제

 

파티션 활용

-- 첫번째 파티션의 데이터 조회
SELECT * FROM LOGS.MSG_LOG WHERE $PARTITION.FN_MSG_LOG(CREATE_DATE) = 1 ;
-- 특정 파티션 삭제
TRUNCATE TABLE LOGS.MSG_LOG WITH (PARTITIONS (1)) ;
-- 범위로 삭제
TRUNCATE TABLE PS_MSG_LOG WITH (PARTITIONS (1 TO 3)) ;

1번 파티션에 가장 과거 일자 (숫자범위로 나눌 시 가장 작은 숫자)의 데이터가 존재한다.

 

범위 조정

새로운 범위를 추가(split) 할 때에는 하기전에 스키마에서 사용할 파일그룹 공간을 명시해주어야 한다. PRIMARY 에 하지 않을거면 특정 파일그룹명. 스키마에 NEXT USED 를 수행하지 않고 SPLIT 할 경우 공간이 없어서 오류가 발생한다.

-- 기존범위 삭제
ALTER PARTITION FUNCTION FN_MSG_LOG () MERGE RANGE ('2023-09-20'); 
-- 신규 범위 추가
ALTER PARTITION SCHEME PS_MSG_LOG NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION FN_MSG_LOG () SPLIT RANGE ('2023-09-21');