[MSSQL] Partition Table 생성 방법

PARTITION TABLE

일반적인 DBMS에서는 데이터를 저장 할 때 특정 값을 기준으로 데이터를 나누어 저장 하는 기능인 파티션을 제공한다.

 

각 DBMS별로 각각의 구현방법과 성능이 차이가 있으나, 기본적으로 파티션 테이블은 특정 값 기준으로 분산하여 저장하기에 데이터 관리측면과 I/O측면에 긍정적인 효과를 가져온다.

 

보통의 데이터 중에 로그성 데이터를 일자별로 저장하여, 관리적인 측면에 도움을 주는 경우가 많지만 작성자의 회사에서는 MSSQL의 20억이 넘는 데이터의 I/O분산을 위해 20개의 파티션으로 분산하여 사용 중에 있다. (물론 다른 용도의 파티션도 많음)

 

ORACLE과 달리 MSSQL에서는 파티션 테이블을 생성하는 방법이 조금 불편하기에 정리 겸 한번 파티션 테이블을 생성하는 방법에 대해 한번 소개해보도록 하겠다.

 

PARTITION TABLE 구성하기

파티션 테이블을 생성하기 앞서 여러가지 먼저 생성해야하는 것들이 있다.

선택사항이지만 먼저 파티션이 이 나누어 저장 될 파일 그룹과 데이터 파일이 있다.

I/O분산이 목적이라하면 파일 그룹과 데이터파일을 분리하여 저장 디스크를 나누어 disk I/O의 분산 목적을 이루길 바란다.

 

그다음으로는 파티셔닝을 할 파티션 함수와 저장 위치를 정하는 파티션 스킴이 있다.

마지막으로 파티션 테이블을 생성한다.

 

Filegroup 및 File 생성

선택사항이지만 파티션 테이블을 생성할때 파티션별로 데이터가 나누어 저장되게 하고싶다면 filegroup과 file을 생성하여 준비해둔다.

 

본 게시글에서는 총 4개의 파티션으로 분산할 예정으로 4개의 Filegroup을 생성하도록 하겠다.

 

[FILEGROUP 생성]

alter database BAN_DB add filegroup FG_PARTITION_00
alter database BAN_DB add filegroup FG_PARTITION_01
alter database BAN_DB add filegroup FG_PARTITION_02
alter database BAN_DB add filegroup FG_PARTITION_03

총 4개의 파일그룹을 생성하고 그 하위에 각각 데이터 파일을 생성하도록 하겠다.

 

[FILE 생성]

alter database BAN_DB add file (
  name = N'FL_PARTITION_00',
  filename = N'G:\data\BAN_DB\FL_PARTITION_00.ndf'
) to filegroup FG_PARTITION_00

alter database BAN_DB add file (
  name = N'FL_PARTITION_01',
  filename = N'G:\data\BAN_DB\FL_PARTITION_01.ndf'
) to filegroup FG_PARTITION_01

alter database BAN_DB add file (
  name = N'FL_PARTITION_02',
  filename = N'G:\data\BAN_DB\FL_PARTITION_02.ndf'
) to filegroup FG_PARTITION_02

alter database BAN_DB add file (
  name = N'FL_PARTITION_03',
  filename = N'G:\data\BAN_DB\FL_PARTITION_03.ndf'
) to filegroup FG_PARTITION_03

본 게시글에서는 테스트 환경이라 G드라이브에 다같이 넣어놨지만, I/O분산이 필요할 경우 물리적인 DISK분산을 하여 I/O가 분산되게 처리해주길 바란다.

 

Partition Function 생성

이제 데이터를 어떤 구분으로 파티셔닝을 할지 정의하는 파티션 함수를 생성할 차례이다.

우리는 int형의 컬럼에 0, 1, 2, 3 이라는 파티션 값을 가지는 총 4개의 파티션을 생성할 예정이다.

create partition function PF_PARTITION_RANGE (int)
    as range left for values (0, 1, 2)

 

파티션 함수 명으로는 PF_PARTITION_RANGE 라는 이름을 사용하였다. 원하는 이름으로 바꿔 사용하면 된다.

파티션을 구분할 값의 타입을 int형으로 지정해주었으며, 파티션을 분할하는 경계 값을 0,1,2를 주었다.

 

경계 값은 나누어질 파티션의 개수 N개보다 1개적은 N-1개가 지정된다.

옵션으로 range left 는 해당 범위에 경계값을 포함하는 옵션, range right는 해당 범위에 경계값을 포함하지 않는 옵션이다.

위에 값으로 range left에 대해 예시를 들자면 아래와 같다.

• 1번 파티션 : <= 0
• 2번 파티션 : > 0 and <= 1 
• 3번 파티션 : > 1 and <= 2
• 4번 파티션 : > 2

 

Partition Scheme 생성

파티셔닝을 할 함수를 생성하였다면, 파티션 스킴을 생성하여 파티션이 저장될 위치를 정해주게 된다.

create partition scheme PS_PARTITION_SCHEME 
    as partition PF_PARTITION_RANGE
    to (FG_PARTITION_00, 
        FG_PARTITION_01, 
        FG_PARTITION_02, 
        FG_PARTITION_03)

 

파티션 스킴 명으로는 PS_PARTITION_SCHEME 라는 이름을 사용하였다. 마찬가지로 원하는 이름으로 바꾸어 사용하면 된다.

먼저 생성하였던 파티션 함수를 as partition 으로 입력하고, 파티션이 저장될 파일그룹을 지정하여준다.

 

지정된 파일그룹의 순서에 따라 파티션 함수에서 정의한 경계값의 순서와 매핑이된다. 직접적으로 파일그룹과 매칭을 해줄 순 없다.

즉, 해당 예제에서는 아래와 같이 매핑되어 데이터가 저장된다.

• 1번 파티션 FG_PARTITION_00 : <= 0  
• 2번 파티션 FG_PARTITION_01 : > 0 and <= 1 
• 3번 파티션 FG_PARTITION_02 : > 1 and <= 2
• 4번 파티션 FG_PARTITION_03 : > 2

만약 파티션 경계값보다 적은 파일그룹을 지정할 경우, 마지막 파티션에 지정받지 경계값들이 모두 저장되며,

파티션 경계값보다 많은 파일그룹을 지정한 경우에는 남는 만큼은 사용하지않고 유휴로 정의만 하고있다가. SPLIT이 발생 할 경우 사용된다.

 

 

Partition Table 생성

결과적으로 사용하고 싶었던 파티션 테이블을 이제 생성하게된다.

create table TB_TEST_PARTITION (
  TEST_ID int not null identity(1, 1),
  TEST_CTS varchar(100),
  TEST_PARTITION_ID as test_id%4 PERSISTED -- 계산열 사용시 물리적으로 저장해야 파티션 컬럼으로 사용가능
) on PS_PARTITION_SCHEME(TEST_PARTITION_ID)

 

여러가지 방법으로 파티셔닝을 할 수 있지만, 실무에서 사용하고 있는 파티션 컬럼처럼 생성해보았다.

자동증가하는 ID를 4등분하여 파티셔닝을 하는것이다. 

 

앞서 생성한 파티션 스킴을 create table ~ on 절을 이용하여 해당 테이블과 연결시킨다.

이때, 어떤 컬럼을 사용할지 지정해준다.

 

만약 컬럼에 null값이 들어갈 경우 null값은 마지막 파티션에 같이 저장된다.