[MS-SQL] 테이블 파티션 인덱스 설정

안녕하세요. 이번포스팅은 파티션인덱스에 관하여 작성하였습니다. MS-SQL에서 대용량처리시 필요한 내용입니다. 관리하고 검색하기 위한 기술로, 인덱스 자체를 여러 개의 논리적인 파티션으로 나누어 저장하는 인덱스입니다.

1. 파티션 함수 생성

먼저, 파티션 함수를 작성해야합니다. 

CREATE PARTITION FUNCTION PF_ITM_NO (VARCHAR(10))
AS RANGE RIGHT FOR VALUES (
    'A'
   ,'B'
   ,'C'
)

 

위의 함수는 'A','B','C'로 시작하는 품목 번호를 파티션을 나누기 위해 작성합니다. RIGHT 대신 LEFT도 사용가능합니다. LEFT 또는 RIGHT는 파티션 경계 값을 포함할 위치를 지정합니다. LEFT는 해당 값 이전의 파티션에 포함되고, RIGHT는 해당 값 이후의 파티션에 포함됩니다. 그리고 FOR VALUES대신 FOR VALUES LESS THAN 구문을 사용 할 수 있습니다. 예를 들어 FOR VALUES LESS THAN ('D')를 작성하게 되면 D보다 작은값과 D보다 큰값의 두개의 파티션으로 구분 됩니다.

2. 파티션 스키마 생성

CREATE PARTITION SCHEME PS_ITM_NO
AS PARTITION PF_ITM_NO
TO ([파일그룹명], [파일그룹명], [파일그룹명], [파일그룹명])

파티션 스키마는 파티션 함수에서 사용될 논리적 파티션을 지정합니다. 파일 그룹명을 지정하여 파티션을 논리적으로 구성되는것을 셋팅합니다.

3.파티션 인덱스 생성

CREATE NONCLUSTERED INDEX [IDX_ITM_NO] ON [dbo].[TABLE_ITM]
(
   [ITM_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PS_ITM_NO]([ITM_NO])

해당 파티션 스키마를 지정하여 인덱스를 생성합니다. 상황에 따라 클러스트형 인덱스를 작성하여도 가능합니다.

12000만건의 데이터를 조회하기 위해 6.1초가 걸렸다면 위를 통하여 0.1초때로 결과를 얻을 수 있었습니다. 이건 파티션 인덱스의 설정등과 테이블 구성환경에 따라 달라 질 수 있습니다.

4. 파티션 인덱스 조회

SELECT
    i.name AS IndexName,
    t.name AS TableName,
    p.partition_number AS PartitionNumber,
    pf.name AS PartitionFunction,
    ps.name AS PartitionScheme
FROM sys.indexes AS i
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.tables AS t ON i.object_id = t.object_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
WHERE 1=1
    AND t.is_ms_shipped = 0 -- 시스템 테이블 제외
ORDER BY TableName, PartitionNumber;

위의 쿼리를 조회하여 설정이 제대로 되었는지 확인 할 수 있습니다.

포스팅은 여기서 마치겠습니다. 궁금하신점이나 잘못된 점에 대한 내용은 댓글 또는 메일에 남겨주시면 감사하겠습니다.