[MSSQL] 파티션 테이블

[참조] http://blog.eloitcube.co.kr/7

이 기능은 SQL2005 Enterprise Edition , Developer Edition에서만 지원됩니다.

SQL2005의 파티션 기능을 이용하여 대용량 테이블 혹은 인덱스를 특정 컬럼의 값을 기준으로 분할하여, DB에 정의된 여러개의 파일그룹에 분산하여 저장이 가능하고, 이를 통하여, 데이터베이스 백업, 복원, 데이타 조회등을 보다 더 효율적으로 할 수 있습니다.

분할된 테이블 또는 인덱스를 만드는 단계는 아래와 같습니다.

1. 파티션 함수 생성 (테이블 또는 인덱스를 분할하는 방법을 지정)

2. 파티션 스키마 생성 (파티션 함수에 의해 생성된 파티션을 사용자가 정의한 파일 그룹 집합에 매핑)

3. 테이블 혹은 인덱스 생성시 파티션 스키마 지정

그럼 테스트를 통해서 데이터 파티셔닝에 대한 이해를 좀 더 쉽게 해보도록 하죠.

우선 아래와 같은 테스트 DB (PartitionTestDB) 를 하나 생성하겠습니다.

여기서 중요한 부분은 파일그룹을 여러개로 하여 생성하는데 있습니다.

USE master

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionTestDB')

DROP DATABASE PartitionTestDB

GO

CREATE DATABASE PartitionTestDB

      ON PRIMARY

       (NAME='Primary FG',

        FILENAME=

          'C:\MSSQL\Data\Primary\Primary FG.mdf',

        SIZE=5,

        MAXSIZE=500,

        FILEGROWTH=1 ),

      FILEGROUP PartitionTESTDB_FG1

       (NAME = 'PartitionTESTDB_FG1',

        FILENAME =

         'C:\MSSQL\Data\FG1\PartitionTESTDB_FG1.ndf',

        SIZE = 5MB,

        MAXSIZE=500,

        FILEGROWTH=1 ),

      FILEGROUP PartitionTESTDB_FG2

       (NAME = 'PartitionTESTDB_FG2',

        FILENAME =

         'C:\MSSQL\Data\FG2\PartitionTESTDB_FG2.ndf',

        SIZE = 5MB,

        MAXSIZE=500,

        FILEGROWTH=1 ),

      FILEGROUP PartitionTESTDB_FG3

       (NAME = 'PartitionTESTDB_FG3',

        FILENAME =

         'C:\MSSQL\Data\FG3\PartitionTESTDB_FG3.ndf',

        SIZE = 5MB,

        MAXSIZE=500,

        FILEGROWTH=1 )

GO

SP_HELPDB PartitionTestDB

GO

자 이렇게 해서 3개의 파일그룹으로 분할한 데이터베이스 PartitionTestDB를 만들었습니다.

이제, 파티션 함수를 생성할 차례입니다.

앞서 설명한 것처럼 파티션 함수는 테이블 혹은 인덱스를 어떤 기준으로 분할할 것인가에 대한 정의를 하는 것 입니다.

생성 구문은 아래와 같습니다.

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] )

[ ; ]

여기서 input_parameter_type 은 분할에 사용되는 컬럼의 데이타 형식입니다.

참고로 text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), 별칭 데이터 형식 또는 CLR 사용자 정의 데이터 형식은 분할기준 컬럼으로 사용할 수 없습니다.

자 그럼 파티션 함수를 만들어 보도록 하지요.

USE PartitionTestDB

GO

CREATE PARTITION FUNCTION pf_DataRange (int)

    AS RANGE LEFT FOR VALUES (100,200)

GO

위 파티션 함수는 함수명은 pf_DataRange, 파티션 기준 컬럼의 형식은 int형식, 그리고 파티션 수는 3개로 분할하겠다고 정의를 하였으며, 3개의 파티션에는 각각 100보다 작거나 같은 수, 100보다 크고 200보다 작거나 같은 수, 200보다 큰 수로 분류되어 저장되게 됩니다.

여기서 RANGE LEFT로 구성한 것과 RANGE RIGHT로 구성한 것의 차이를 잠깐 살펴보겠습니다.

예) RANGE LEFT FOR VALUES (100,200)

파티션 1 : 컬럼값 <= 100

파티션 2 : 100 < 컬럼값 <= 200

파티션 3 : 200 < 컬럼값

예) RANGE RIGHT FOR VALUES (100,200)

파티션 1 : 컬럼값 < 100

파티션 2 : 100 <= 컬럼값 < 200

파티션 3 : 200 <= 컬럼값

이제 다음 단계인 파티션 스키마를 생성해 보도록 하겠습니다.

생성구문

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

[ ; ]

USE PartitionTestDB

GO

CREATE PARTITION SCHEME ps_DataRange

    AS PARTITION pf_DataRange

    TO (PartitionTESTDB_FG1,PartitionTESTDB_FG2,PartitionTESTDB_FG3);

GO

파티션 스키마는 분할된 테이블 및 인덱스를 어떤 파일 그룹에 매핑 할지를 정의 합니다.

앞서 생성한 파티션 함수와 비교해보면 각 파티션 그룹에 아래 조건을 기준으로 데이타들이 저장되게 됩니다.

PartitionTESTDB_FG1 : 컬럼값 <= 100

PartitionTESTDB_FG2 : 100 < 컬럼값 <= 200

PartitionTESTDB_FG3 : 200 < 컬럼값

이제 파티셔닝을 할 테이블을 만들고 샘플 데이타를 입력하여 실제로 지정한 기준대로 분할되어 저장되는지 확인을 해보도록 하겠습니다.

--테스트 테이블 생성. ON구문을 주의해서 보자!!

CREATE TABLE TBL_User

(

    idx int not null,

    name varchar(20),

    regdate smalldatetime,

    salary int

) ON ps_DataRange (idx);

GO

SET NOCOUNT ON

GO

-- 샘플데이타 400개 입력

DECLARE @i INT

SET @i = 1

WHILE (@i <= 400)

BEGIN

    INSERT INTO TBL_User VALUES (@i,'TEST',GETDATE(),@i*100)

    SET @i = @i + 1

END

GO

--입력된 결과 보기

SELECT * FROM TBL_USER

GO

idx         name                 regdate                 salary

----------- -------------------- ----------------------- -----------

1           TEST                 2006-12-20 20:45:00     100

2           TEST                 2006-12-20 20:45:00     200

3           TEST                 2006-12-20 20:45:00     300

4           TEST                 2006-12-20 20:45:00     400

5           TEST                 2006-12-20 20:45:00     500

6           TEST                 2006-12-20 20:45:00     600

....

....

391         TEST                 2006-12-20 20:45:00     39100

392         TEST                 2006-12-20 20:45:00     39200

393         TEST                 2006-12-20 20:45:00     39300

394         TEST                 2006-12-20 20:45:00     39400

395         TEST                 2006-12-20 20:45:00     39500

396         TEST                 2006-12-20 20:45:00     39600

397         TEST                 2006-12-20 20:45:00     39700

398         TEST                 2006-12-20 20:45:00     39800

399         TEST                 2006-12-20 20:45:00     39900

400         TEST                 2006-12-20 20:45:00     40000

자 이렇게 해서 400개의 데이타가 삽입되었습니다.

그러면, 우리가 앞서 지정한 대로 파일그룹에 분할되어 저장되었는지 확인해 봅시다.

select * from sys.partitions where object_name(object_id)='TBL_USER'

GO

partition_id              object_id   index_id    partition_number      hobt_id              rows

------------------- ----------- ------- ----------------   ---------------    ----

72057594038517760    2105058535  0           1                72057594038517760    100

72057594038583296    2105058535  0           2                72057594038583296    100

72057594038648832    2105058535  0           3                72057594038648832    200

3개의 파티션에 각각 100개,100개,200개의 row가 저장된 것을 확인 할 수 있습니다.

예제에서는 파일그룹들을 모두 동일한 디스크에 생성하였지만, 테스트 환경의 문제로 이해하셔야 하구요

분리한 파일그룹들이 각각의 디스크로 분리되어야 디스크IO에 대한 동시성이 향상된다고 볼수 있겠습니다.