728x90
MSSQL에는 테이블 파티셔닝이라는 개념이 존재한다.
특정 구성표로 데이터 범위를 지정하고 지정한 범위의 데이터를 특정 파티션에 저장하도록 테이블을 생성하여 운영하는 기법이다.
이렇게 분할을 한 테이블은 데이터가 작아지기 때문에 INSERT, UPDATE, SELECT등의 작업 속도를 향상 시킨다.
이제 테이블 파티셔닝 실습(SPLIT, SWITCH, MERGE)을 진행해보자
1. 데이터베이스 생성
-- 데이터베이스 생성
USE master
GO
CREATE DATABASE bebeDB
ON PRIMARY (
NAME = bebeDB,
FILENAME = 'C:\data\bebeDB.MDF',
SIZE = 5,
FILEGROWTH = 1
)
LOG ON (
NAME = bebeDB_Log,
FILENAME = 'C:\data\bebeDB.LDF',
SIZE = 5,
FILEGROWTH = 1
)
GO
--파일그룹 생성 생성
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_FG1
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_FG2
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_FG3
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_FG4
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_IDX_FG1
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_IDX_FG2
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_IDX_FG3
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_IDX_FG4
GO
--파티션 데이터 파일 생성(데이터/인덱스)
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_FG1,
FILENAME = 'C:\data\bebeDB_FG1.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_FG1
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_FG2,
FILENAME = 'C:\data\bebeDB_FG2.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_FG2
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_FG3,
FILENAME = 'C:\data\bebeDB_FG3.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_FG3
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_FG4,
FILENAME = 'C:\data\bebeDB_FG4.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_FG4
GO
--파티션 데이터(인덱스용)
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_IDX_FG1,
FILENAME = 'C:\data\bebeDB_IDX_FG1.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_IDX_FG1
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_IDX_FG2,
FILENAME = 'C:\data\bebeDB_IDX_FG2.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_IDX_FG2
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_IDX_FG3,
FILENAME = 'C:\data\bebeDB_IDX_FG3.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_IDX_FG3
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_IDX_FG4,
FILENAME = 'C:\data\bebeDB_IDX_FG4.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_IDX_FG4
GO
sp_helpdb bebeDB
GO
2. 분할된 테이블 생성
USE bebeDB
GO
-- 파티션 함수 생성
CREATE PARTITION FUNCTION PF_DateRange(datetime)
AS RANGE RIGHT FOR VALUES (
'2010-01-01',
'2011-01-01',
'2012-01-01')
GO
-- 파티션 구성표 생성
CREATE PARTITION SCHEME PS_DateRange
AS PARTITION PF_DateRange TO (
bebeDB_FG1,
bebeDB_FG2,
bebeDB_FG3,
bebeDB_FG4)
GO
CREATE PARTITION SCHEME PS_IdxRange
AS PARTITION PF_DateRange TO (
bebeDB_IDX_FG1,
bebeDB_IDX_FG2,
bebeDB_IDX_FG3,
bebeDB_IDX_FG4)
GO
-- 분할된 테이블 생성 및 클러스터 인덱스 추가
-- 클러스터 인덱스는 반드시 DATA 파일그룹에 같이 생성(클러스터 인덱스를 index 파일그룹에 별도로 위치시킨다면 모든 데이터가 index 파일 그룹으로 옮겨가게 되는 현상이 생기니 주의 !!)
CREATE TABLE dbo.bebeDB (
KeyCol int IDENTITY NOT NULL,
InsDate datetime NOT NULL,
CharCol varchar(50),
CONSTRAINT PK_bebeDB PRIMARY KEY CLUSTERED (InsDate, KeyCol)
) ON PS_DateRange(InsDate)
GO
-- 분할된 인덱스 생성
--파티션 구성표를 정의하여 인덱스를 생성하는 방법입니다. 인덱스 전용 파티션을 구성표를 만들었으니 해당 구성표에 정의해 줍시다.
create nonclustered index IDX_bebeDB_03 ON bebeDB (CharCol) ON PS_IdxRange(InsDate)
/*
인덱스 생성 시 아무런 옵션을 지정하지 않으면 기본테이블의 파티션 정의(PS_DateRange(InsDate))를 따라갑니다
예시 : create nonclustered index IDX_bebeDB_01 ON bebeDB (CharCol)
참조 : mozi.tistory.com/351
*/-- 초기 데이터 입력
INSERT INTO dbo.bebeDB (InsDate, CharCol) VALUES ('2009-01-01', 'Test 2009 (1)')
INSERT INTO dbo.bebeDB (InsDate, CharCol) VALUES ('2010-01-01', 'Test 2010 (1)')
INSERT INTO dbo.bebeDB (InsDate, CharCol) VALUES ('2011-01-01', 'Test 2011 (1)')
INSERT INTO dbo.bebeDB (InsDate, CharCol) VALUES ('2012-01-01', 'Test 2012 (1)')
INSERT INTO dbo.bebeDB (InsDate, CharCol) VALUES ('2013-01-01', 'Test 2013 (1)')
SELECT *, $partition.PF_DateRange(InsDate) as 파티션ID FROM dbo.bebeDB ORDER BY KeyCol DESC
GO
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB') ORDER BY partition_number ASC
GO
3. 파티션 SPLIT
기본적인 준비가 끝났으니 파티션을 나눠보자
USE bebeDB
GO
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_FG5
ALTER DATABASE bebeDB ADD FILEGROUP bebeDB_IDX_FG5
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_FG5,
FILENAME = 'C:\data\bebeDB_FG5.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_FG5
GO
ALTER DATABASE bebeDB ADD FILE (
NAME = bebeDB_IDX_FG5,
FILENAME = 'C:\data\bebeDB_IDX_FG5.NDF',
SIZE = 5,
FILEGROWTH = 1
)
TO FILEGROUP bebeDB_IDX_FG5
GO
sp_helpdb bebeDB
GO
ALTER PARTITION SCHEME PS_DateRange NEXT USED bebeDB_FG5
ALTER PARTITION SCHEME PS_IdxRange NEXT USED bebeDB_IDX_FG5
GO
ALTER PARTITION FUNCTION PF_DateRange()
SPLIT RANGE ('2013-01-01')
GO
SELECT *, $partition.PF_DateRange(InsDate) FROM dbo.bebeDB ORDER BY KeyCol DESC
go
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB') ORDER BY partition_number ASC
4. 파티션 SWITCH
--파티션된 동일한 테이블 구성(인덱스까지 동일해야함)
CREATE TABLE dbo.bebeDB_2013 ( --drop table bebeDB_2013
KeyCol int IDENTITY NOT NULL,
InsDate datetime NOT NULL,
CharCol varchar(50),
CONSTRAINT PK_bebeDB_2013 PRIMARY KEY CLUSTERED (InsDate, KeyCol)
) ON PS_DateRange(InsDate)
GO
create nonclustered index IDX_bebeDB_03 ON bebeDB_2013 (CharCol) ON PS_IdxRange(InsDate)
--파티션 to 파티션 데이터 SWITCH
ALTER TABLE dbo.bebeDB SWITCH PARTITION 5 TO bebeDB_2013 PARTITION 5
GO
--bebeDB_2013 파티션 테이블 데이터 및 원본 bebeDB 데이터 확인
select * From dbo.bebeDB_2013
select * From dbo.bebeDB
--원복
Alter Table bebeDB_2013
SWITCH PARTITION 5 TO bebeDB
PARTITION 5
GO
--bebeDB_2013 파티션 테이블 데이터 및 원본 bebeDB 데이터 확인
select * From dbo.bebeDB_2013
select * From dbo.bebeDB
--상세 확인
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB') ORDER BY partition_number ASC
GO
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB_2013') ORDER BY partition_number ASC
GO
SELECT *, $partition.PF_DateRange(InsDate) FROM dbo.bebeDB ORDER BY KeyCol ASC
SELECT *, $partition.PF_DateRange(InsDate) FROM dbo.bebeDB_2013 ORDER BY KeyCol ASC
GO
/*
파티션 to 파티션 데이터 이동을 위해서는 목적지 파티션이 비어 있어야한다. 만약 비어있지 않다면 아래와 같은 오류를 발생 시킴
ALTER TABLE SWITCH 문이 실패했습니다. 대상 테이블 'bebeDB.dbo.bebeDB'의 지정된 파티션 5이(가) 비어 있어야 합니다.
이럴 경우 대상 테이블 'bebeDB.dbo.bebeDB'의 지정된 파티션 5 를 비우거나 insert 문으로 테이터를 밀어 넣는다.
SET IDENTITY_INSERT bebeDB ON;
INSERT INTO bebeDB ([KeyCol], [InsDate], [CharCol]) select [KeyCol], [InsDate], [CharCol] from bebeDB_2013
SET IDENTITY_INSERT bebeDB OFF;
*/
5. 파티션 MERGE
--스위칭을 위한 테이블 비우기
delete from dbo.bebeDB_2013
--파티션 to 파티션 데이터 SWITCH
ALTER TABLE dbo.bebeDB SWITCH PARTITION 5 TO bebeDB_2013 PARTITION 5
GO
--파티션 함수 정보 확인 [전]
SELECT f.name AS [pf_DataRange]
, f.type_desc AS PartitionType
, CASE WHEN f.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'Right' END AS LeftRightBoundary
, v.value AS BoundaryValue
, v.boundary_id AS BoundaryId
, t.name AS DataType
FROM sys.partition_functions f
inner join sys.partition_range_values v
on f.function_id = v.function_id
inner join sys.partition_parameters p
on f.function_id = p.function_id
inner join sys.types t
on t.system_type_id = p.system_type_id
order by pf_DataRange desc, BoundaryId
-- 파티션 MERGE
-- 파티션 함수가 RIGHT로 생성되어 있기 때문에 MERGE시 왼쪽(2012-01-01)으로 병합됨
ALTER PARTITION FUNCTION PF_DateRange()
MERGE RANGE ('2013-01-01');
GO
--파티션 함수 정보 확인 [후]
SELECT f.name AS [pf_DataRange]
, f.type_desc AS PartitionType
, CASE WHEN f.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'Right' END AS LeftRightBoundary
, v.value AS BoundaryValue
, v.boundary_id AS BoundaryId
, t.name AS DataType
FROM sys.partition_functions f
inner join sys.partition_range_values v
on f.function_id = v.function_id
inner join sys.partition_parameters p
on f.function_id = p.function_id
inner join sys.types t
on t.system_type_id = p.system_type_id
order by pf_DataRange desc, BoundaryId
--상세 확인
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB') ORDER BY partition_number ASC
GO
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB_2013') ORDER BY partition_number ASC
GO
--스위칭해둔 bebeDB_2013 테이블의 데이터 ->bebeDB 테이블로 INSERT
SET IDENTITY_INSERT bebeDB ON;
INSERT INTO bebeDB ([KeyCol], [InsDate], [CharCol]) select [KeyCol], [InsDate], [CharCol] from bebeDB_2013
SET IDENTITY_INSERT bebeDB OFF;
--상세 확인
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB') ORDER BY partition_number ASC
GO
SELECT object_name(object_id) as object_name, partition_number, rows FROM sys.partitions
WHERE object_id = object_id('dbo.bebeDB_2013') ORDER BY partition_number ASC
GO
SELECT *, $partition.PF_DateRange(InsDate) FROM dbo.bebeDB ORDER BY KeyCol ASC
SELECT *, $partition.PF_DateRange(InsDate) FROM dbo.bebeDB_2013 ORDER BY KeyCol ASC
GO
이상으로 MSSQL 테이블 파티셔닝 실습을 마치겠습니다.
반응형
그리드형
'IT > MSSQL' 카테고리의 다른 글
| [MSSQL] MSDB 파일 사이즈 증가로 인한 백업 히스토리 기록 삭제방법(복원,백업 로딩속도 개선) (0) | 2022.12.19 |
|---|---|
| [MSSQL] 윈도우 호스트네임 변경 후 SQL 윈도우인증 접속 불가 (0) | 2022.11.15 |
| [MSSQL] 어셈블리 등록 오류 / 구성 옵션 'clr strict security'이 없거나 고급 옵션입니다 (0) | 2022.08.21 |
| [MSSQL] 오류 35208, master 데이터베이스를 사용중인 경우에만 가용성 그룹 DDL 작업이 허용됩니다. (0) | 2022.08.21 |
| [MSSQL] tempdb 용량축소(SHRINKFILE) 안될 때 캐시 삭제하기 (0) | 2022.08.21 |
댓글