[참조] 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에 대한 동시성이 향상된다고 볼수 있겠습니다.
