MSSQL 분할테이블 사용

참고로 다음 블로그에 박성원님이 잘 정리해 놓았다.
http://gdbt.tistory.com/65

분할 테이블
  - 테이블이 대용량으로 향할때 INSERT, UPDATE, DELETE등의 작업은 갈수록 느려지게 되는데 이때 테이블을 일정한 범주로 나누어 분할하게 되면 시스템 성능에 큰 도움이 된다.
  - 행이 적을때는 나눌 필요가 없을것이다(이득이 적다)

분할뷰와 분할 테이블의 차이점은
  - 분할 뷰는 서로다른 서버에 저장된 데이터베이스에 분산된 데이터를 하나의 뷰로 보겠다는 것이고
  - 분할 테이블은 로컬 데이터베이스에서 하나의 테이블을 여러 파티셔닝된 디스크 또는 파일 그룹으로로 나누어서 저장하는 방식이다.

분할테이블의 개념도

1. 파일그룹으로 분리한 데이터베이스 생성
2. 파티션함수(Partition Function)를 정의 : 파티션 함수는 테이블 또는 인덱스를 분할하는 방법을 지정한다. - 파티션 구성표(Partition Schema)를 정의 : 파티션 구성표는 파티션 함수에 의해 생성된 파티션을 파일 그룹별로 나눠주는 역할을 수행
3. 테이블 정의시에 파티션 구성표를 적용
4. 데이터 입력 또는 대량 데이터 로드
5. 자동으로 범위에 따라서 파일 그룹에 분할되어 저장됨

작성예)
각디스크별로 파일그룹을 생성한다(C:\, D:\, E:\, F:\

파일그룹으로 분리된 데이터베이스 생성

USE master
CREATE DATABASE partTblDB
ON PRIMARY -- 75년 이전 출생자(75년 포함)
(NAME = partTblDB
 FILENAME = N'C:\Data\partTblDB.mdf'
),
FILEGROUP YEAR_76_80 -- 76~80년 출생자 파일그룹 시작
(NAME = partTblDB2,
 FILENAME=N'D:\Data\partTblDB2.ndf'
),
FILEGROUP YEAR_81 -- 81년 이후 출생자(81년 포함)
(NAME = partTblDB3,
 FILENAME=N'E:\Data\partTblDB3.ndf'
),
LOG ON
(NAME=partTblDB_Log,
 FILENAME=F:\Data\partTblDB_log.ldf'
)

파티션 함수 및 파티션 구성표 정의

* 파티션 함수 정의
 USE partTblDB
GO
CREATE PARTITION FUNCTION birthYearRangeF(INT)
    AS RANGE LEFT FOR VALUES (1975, 1981)

-- 여기서 파티션 번호 1은 값 <= 1975
              파티션 번호 2는 1976 <= 값 <1981
              파티션 번호 3은 값 >= 1981
    과 같이 나눈다는 의미

* 파티션 구성표

파일그룹	PRIMARY	YEAR_76_80	YEAR_81
파티션번호	1	2	3
값	값<=1975	1976 <= 값 < 1981	값 >= 1981

 위 구성표를 만들기 위한 스크립트는

  CREATE PARTITION SCHEME birthYearRangePS
  AS PARTITION birthYearRangePF
  TO ( [PRIMARY], YEAR_76_80, YEAR_81)
  GO

테이블을 정의 한다.

CREATE TABLE userTbl
(
     userID char(8) NOT NULL,
     name nvarchar(10) NOT NULL,
     birthYear int NOT NULL,
     addr nchar(40) NOT NULL,
     mobile1 nchar(3),
     mobile2 nchar(8),
     height smallint
) ON birthYearRangePS(birthYear)

파티션함수, 파티션 구성표, 테이블의 관계를 표현하면

각 파티션별로 나뉘어서 입력된 데이터를 확인하는 방법

SELECT * FROM userTbl
WHERE $PARTITION.birthYearRangePF(birthYear) = 1 -- 파티션 번호

파티션간의 데이터 이동
파티션 3번에 저장된 데이터를 일반 파티션되지 않은 테이블로 이동하는 예제
기존 3번 파티션에 저장되었던 데이터는 사라진다

ALTER TABLE userTbl SWITCH PARTITION 3 TO noPart_userTbl