/**********************************************************************************************
-- Title : [2k5] 슬라이딩 윈도우 기법(sliding window)
-- Reference : mcpworld.com
-- Key word : alter partition function schema switch merge next split
**********************************************************************************************/
/*
-- 슬라이딩 윈도우 기법
--History 테이블의 가장 오래된 한 달치의 데이터를
--HistoryArchive 테이블 생성한 후, History 테이블에서 삭제
*/
/*
-- 데이터베이스 생성
*/
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'AdventureWorksPartition')
DROP DATABASE AdventureWorksPartition;
GO
CREATE DATABASE AdventureWorksPartition
ON PRIMARY
( NAME = AdventureWorksPartition_PRM,
FILENAME = 'D:\AdventureWorksPartition_PRM.mdf',
SIZE = 3 MB,
MAXSIZE = 20 MB,
FILEGROWTH = 5 MB ),
FILEGROUP AdventureWorks_FG1
( NAME = AdventureWorksPartition_dat1,
FILENAME = 'D:\AdventureWorksPartition_dat1.ndf',
SIZE = 5 MB,
MAXSIZE = 50 MB,
FILEGROWTH = 5 MB)
LOG ON
( NAME = AdventureWorksPartition_Log,
FILENAME = 'D:\AdventureWorksPartition_Log.ldf',
SIZE = 5 MB,
MAXSIZE = 50 MB,
FILEGROWTH = 5 MB );
GO
-- 파일 그룹 추가
ALTER DATABASE AdventureWorksPartition
ADD FILEGROUP AdventureWorks_FG2;
GO
ALTER DATABASE AdventureWorksPartition
ADD FILE (
NAME = N'AdventureWorksPartition_dat2',
FILENAME = N'D:\AdventureWorksPartition_dat2.ndf',
SIZE = 5 MB,
MAXSIZE = 50 MB,
FILEGROWTH = 5MB)
TO FILEGROUP AdventureWorks_FG2;
GO
-- 테스트 속도를 위해 단순 모드로 설정
SELECT DATABASEPROPERTYEX('AdventureWorksPartition','RECOVERY') as Recovery;
GO
ALTER DATABASE AdventureWorksPartition SET RECOVERY SIMPLE;
GO
/*
-- History 테이블 생성
*/
USE AdventureWorksPartition;
GO
-- 각 파티션의 기준 데이터 값을 정하는 파티션 함수 생성
-- RIGHT FOR VALUES로 기준 설정
CREATE PARTITION FUNCTION HistoryPF1 (datetime)
AS RANGE RIGHT FOR VALUES (
'2003-11-01',
'2003-12-01',
'2004-01-01',
'2004-02-01');
GO
-- 파티션 구성표 생성
CREATE PARTITION SCHEME HistoryPS1
AS PARTITION HistoryPF1
TO (AdventureWorks_FG1, AdventureWorks_FG1
,AdventureWorks_FG1, AdventureWorks_FG2, AdventureWorks_FG2);
GO
-- 파티션 구성표를 사용하여 분할된 테이블 생성
CREATE TABLE History(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL CONSTRAINT DF_ReferenceOrderLineID DEFAULT (0),
TransactionDate datetime NOT NULL CONSTRAINT DF_TransactionDate DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT (GETDATE())
) ON HistoryPS1 (TransactionDate);
GO
-- 데이터 입력
INSERT INTO History (TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID
,TransactionDate,TransactionType,Quantity,ActualCost,ModifiedDate)
SELECT * FROM AdventureWorks.production.TransactionHistory
WHERE TransactionDate >= '2003-11-01' AND TransactionDate < '2004-03-01'
-- 테이블과 같은 파티션 구성표를 설정하여 분할된 인덱스 생성
ALTER TABLE History ADD
CONSTRAINT PK_History PRIMARY KEY CLUSTERED
(
TransactionDate,
TransactionID
) ON HistoryPS1 (TransactionDate);
GO
-- 분할된 테이블의 정보 확인
SELECT OBJECT_NAME(object_id) AS ObjectName, *
FROM sys.partitions
WHERE object_id = OBJECT_ID('History')
ORDER BY partition_number, index_id;
GO
-- 파티션 범위 확인
SELECT $partition.HistoryPF1(TransactionDate)
AS 'Partition Number'
, min(TransactionDate) AS 'Min TransactionDate'
, max(TransactionDate) AS 'Max TransactionDate'
, count(*) AS 'Rows In Partition'
FROM History
GROUP BY $partition.HistoryPF1(TransactionDate)
ORDER BY 1
GO
/*
-- HistoryArchive 테이블 생성
*/
-- HistoryArchive 테이블을 분할된 테이블로 생성하기 위한 파티션 함수 생성
CREATE PARTITION FUNCTION ArchivePF2 (datetime)
AS RANGE RIGHT FOR VALUES ('11/01/2003');
GO
-- HistoryArchive 테이블의 파티션 스키마 생성
CREATE PARTITION SCHEME ArchivePS2
AS PARTITION ArchivePF2
TO (AdventureWorks_FG1, AdventureWorks_FG1);
GO
-- HistoryArchive 분할된 테이블 생성
CREATE TABLE HistoryArchive(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL CONSTRAINT DF_HistoryArchive_ReferenceOrderLineID DEFAULT (0),
TransactionDate datetime NOT NULL CONSTRAINT DF_HistoryArchive_TransactionDate DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL CONSTRAINT DF_HistoryArchive_ModifiedDate DEFAULT (GETDATE())
) ON ArchivePS2 (TransactionDate);
GO
-- HistoryArchive 테이블에 분할된 인덱스 생성
ALTER TABLE HistoryArchive
ADD CONSTRAINT PK_HistoryArchive PRIMARY KEY CLUSTERED
(
[TransactionDate],
[TransactionID]
) ON ArchivePS2 (TransactionDate);
GO
-- HistoryArchive 테이블의 정보 확인
SELECT OBJECT_NAME(object_id) as ObjectName, * FROM sys.partitions
WHERE object_id = OBJECT_ID('HistoryArchive')
ORDER BY partition_number, index_id;
GO
-- 파티션 범위 확인
SELECT $partition.ArchivePF2(TransactionDate)
AS 'Partition Number'
, min(TransactionDate) AS 'Min TransactionDate'
, max(TransactionDate) AS 'Max TransactionDate'
, count(*) AS 'Rows In Partition'
FROM HistoryArchive
GROUP BY $partition.ArchivePF2(TransactionDate)
ORDER BY 1
GO
/*
-- 스위칭
*/
-- 스위칭을 하기 전에 HistoryArchive 테이블에
-- CHECK 제약 조건을 추가하여 데이터 오류 확인
ALTER TABLE HistoryArchive WITH CHECK
ADD CONSTRAINT CK_HistoryArchive_DateRange_S
CHECK (TransactionDate >= '11/01/2003');
GO
ALTER TABLE HistoryArchive WITH CHECK
ADD CONSTRAINT CK_HistoryArchive_DateRange_E
CHECK (TransactionDate < '12/01/2003');
GO
-- History 테이블에서 HistoryArchive 테이블로 데이터 이동
ALTER TABLE History
SWITCH PARTITION 2
TO HistoryArchive PARTITION 2;
GO
-- 데이터 이동 확인
SELECT $partition.HistoryPF1(TransactionDate)
AS 'Partition Number'
, min(TransactionDate) AS 'Min TransactionDate'
, max(TransactionDate) AS 'Max TransactionDate'
, count(*) AS 'Rows In Partition'
FROM History
GROUP BY $partition.HistoryPF1(TransactionDate)
ORDER BY 1
GO
SELECT $partition.ArchivePF2(TransactionDate)
AS 'Partition Number'
, min(TransactionDate) AS 'Min TransactionDate'
, max(TransactionDate) AS 'Max TransactionDate'
, count(*) AS 'Rows In Partition'
FROM HistoryArchive
GROUP BY $partition.ArchivePF2(TransactionDate)
ORDER BY 1
GO
-- History, HistoryArchive 테이블의 정보 확인
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('History')
ORDER BY partition_number, index_id;
GO
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('HistoryArchive')
ORDER BY partition_number, index_id;
GO
-- History 테이블의 2003년 11월 파티션을 통합
ALTER PARTITION FUNCTION HistoryPF1()
MERGE RANGE ('11/01/2003');
GO
-- 데이터 이동이 완료된 테이블의 파티션과 데이터를 확인
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('History')
ORDER BY partition_number, index_id;
GO
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('HistoryArchive')
ORDER BY partition_number, index_id;
GO
-- History 테이블의 제약 조건을 제거
ALTER TABLE HistoryArchive
DROP CONSTRAINT CK_HistoryArchive_DateRange_S;
GO
ALTER TABLE HistoryArchive
DROP CONSTRAINT CK_HistoryArchive_DateRange_E;
GO
/*
-- History 테이블에 데이터 입력
*/
-- 파티션 구성표를 지정한 파일 그룹에 추가
ALTER PARTITION SCHEME HistoryPS1
NEXT USED AdventureWorks_FG2;
GO
-- History 테이블의 끝에 새로운 파티션 추가
ALTER PARTITION FUNCTION HistoryPF1()
SPLIT RANGE ('2004-03-01');
GO
-- History 테이블 정보 확인
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('History')
ORDER BY partition_number, index_id;
GO
-- 임시 테이블 생성
CREATE TABLE History_20040301(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL CONSTRAINT DF_ROrderLineID DEFAULT (0),
TransactionDate datetime NOT NULL CONSTRAINT DF_TDate DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL CONSTRAINT DF_MDate DEFAULT (GETDATE())
) ON AdventureWorks_FG2;
GO
-- CHECK 제약 조건 생성
ALTER TABLE History_20040301
ADD CONSTRAINT CK_History_20040301_DateRange_S
CHECK (TransactionDate >= '2004-03-01');
GO
ALTER TABLE History_20040301
ADD CONSTRAINT CK_History_20040301_DateRange_E
CHECK (TransactionDate < '2004-04-01');
GO
-- 임시 테이블에 데이터 입력
INSERT INTO History_20040301 (TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID
,TransactionDate,TransactionType,Quantity,ActualCost,ModifiedDate)
SELECT * FROM AdventureWorks.production.TransactionHistory
WHERE TransactionDate >= '2004-03-01' AND TransactionDate < '2004-04-01'
GO
-- 스위칭
ALTER TABLE History_20040301
SWITCH TO History PARTITION 5;
GO
/*
메시지 4913, 수준 16, 상태 2, 줄 1
ALTER TABLE SWITCH 문이 실패했습니다. 테이블 'AdventureWorksPartition.dbo.History_20040301'에는
클러스터형 인덱스가 없지만 테이블 'AdventureWorksPartition.dbo.History'에는 클러스터형 인덱스
'PK_History'이(가) 있습니다.
*/
-- 클러스터드 인덱스 생성
ALTER TABLE History_20040301 ADD
CONSTRAINT PK_History_20040301 PRIMARY KEY CLUSTERED
(
[TransactionDate],
[TransactionID]
) ON AdventureWorks_FG2;
GO
-- 스위칭 다시
ALTER TABLE History_20040301
SWITCH TO History PARTITION 5;
GO
-- History 테이블 정보 확인
SELECT OBJECT_NAME(object_id), * FROM sys.partitions
WHERE object_id = OBJECT_ID('History')
ORDER BY partition_number, index_id;
GO
-- 모든 파일 그룹 정보 확인
SELECT * FROM sys.filegroups
-- History 테이블의 파티션과 관계 있는 파일 그룹 정보 확인
SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
dds.data_space_id AS FileGroup,
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'History') and (i.index_id IN (0,1));
GO
-- 스테이징 테이블 삭제
DROP TABLE History_20040301;
GO