파티션 (02. 테이블 파티션)
/* 02. 테이블 파티션 */
--(1) 파티션 테이블 생성 절차
/*
1.파일 그룹을 생성한다.(선택)
2.파일을 파일 그룹에 추가한다.(선택)
3.파티션 함수를 생성한다.(필수)
-> 분할 방법과 경계 값을 지정한다.
4.파티션 구성표(Partition Scheme)를 생성한다.(필수)
-> 파티션 함수에 정의된 각 파티션의 위치(=파일그룹)를 지정한다.
5.파티션 테이블을 생성한다.
-> 분할(=파티션)하고자 하는 테이블을 파티션 구성표에 생성한다.
*/
-- 1) 파티션 함수(Partition Function) 생성
USE jwjung
GO
CREATE PARTITION FUNCTION pf_OrderDate (CHAR(8))
AS RANGE RIGHT
FOR VALUES (
'1997' /* 1996년 파티션 */
,'1998' /* 1997년 파티션 */
,'1999' /* 1998년 파티션 */
)
GO
/* RIGHT 이므로
파티션 번호 시작 값 종료 값 비고
1 허용 가용한 최소 값 < '1997' 1996년도 이하
2 '1997' <= < '1998' 1997년도
3 '1998' <= < '1999' 1998년도
4 '1999' <= 허용 가능한 최대 값 final 파티션(자동 생성됨)
*/
SELECT * FROM jwjung.sys.partition_functions
GO
--name function_id type type_desc fanout boundary_value_on_right create_date modify_date
--pf_OrderDate 65536 R RANGE 4 1 2012-01-27 23:33:00.223 2012-01-27 23:33:00.223
SELECT * FROM jwjung.sys.partition_range_values
GO
--function_id boundary_id parameter_id value
--65536 1 1 1997
--65536 2 1 1998
--65536 3 1 1999
SELECT CONVERT(DATETIME, '2009-12-31 23:59:59.997')
UNION ALL
SELECT CONVERT(DATETIME, '2009-12-31 23:59:59.997')
UNION ALL
SELECT CONVERT(DATETIME, '2009-12-31 23:59:59.999')
GO
--2009-12-31 23:59:59.997
--2009-12-31 23:59:59.997
--2010-01-01 00:00:00.000
-- 2) 파티션 구성표(Partition Schema) 생성
--각 파티션이 위치할 파일 그룹을 정의한 파티션 구성표를 만든다.
CREATE PARTITION SCHEME ps_OrderDate
as partition pf_OrderDate
to ([primary], [primary], [primary], [primary])
GO
--모든 파티션을 한 파일 그룹에 매핑하고자 한다면
CREATE PARTITION SCHEME ps_OrderDate
as partition pf_OrderDate
ALL to ([primary])
GO
SELECT * FROM jwjung.sys.partition_schemes
GO
--name data_space_id type type_desc is_default function_id
--ps_OrderDate 65618 PS PARTITION_SCHEME 0 65536
SELECT a.*, b.name, b.type, b.type_desc
FROM jwjung.sys.destination_data_spaces a
, jwjung.sys.data_spaces b
WHERE a.data_space_id = b.data_space_id
GO
--partition_scheme_id destination_id data_space_id name type type_desc
--65618 1 1 PRIMARY FG ROWS_FILEGROUP
--65618 2 1 PRIMARY FG ROWS_FILEGROUP
--65618 3 1 PRIMARY FG ROWS_FILEGROUP
--65618 4 1 PRIMARY FG ROWS_FILEGROUP
-- 3) 파티션 테이블 생성
USE jwjung
GO
CREATE TABLE Orders_Range (
OrderID INT IDENTITY(1, 1) NOT NULL
,CustomerID NCHAR(5) NULL
,EmployeeID INT NULL
,OrderDate CHAR(8) NULL
,RequiredDate DATETIME NULL
,ShippedDate DATETIME NULL
,ShipVia INT NULL
,Freight MONEY NULL
,ShipName NVARCHAR(40) NULL
,ShipAddress NVARCHAR(60) NULL
,ShipCity NVARCHAR(15) NULL
,ShipRegion NVARCHAR(15) NULL
,ShipPostalCode NVARCHAR(10) NULL
,ShipCountry NVARCHAR(15) NULL
)
ON ps_OrderDate (OrderDate) /* 파티션 구성표와 파티션 컬럼을 기술한다 */
GO
--파티션 컬럼(=OrderDate)의 데이터 형식, 길이 및 전체 자릿수는 (해당 파티션 구성표가 사용하는) 파티션 함수에
--지정된 입력 파라미터의 것과 일치해야 한다.
SELECT a.name as '테이블', f.name as '파티션 함수', c.name as '파티션 구성표'
, d.destination_id '파티션 번호', e.name as '파일 그룹'
FROM sys.tables a
, sys.indexes b
, sys.partition_schemes c
, sys.destination_data_spaces d
, sys.data_spaces e
, sys.partition_functions f
WHERE a.name = 'Orders_Range'
AND a.object_id = b.object_id
AND b.index_id in (0, 1)
AND b.data_space_id = c.data_space_id
AND c.data_space_id = d.partition_scheme_id
AND d.data_space_id = e.data_space_id
AND c.function_id = f.function_id
GO
--테이블 파티션 함수 파티션 구성표 파티션 번호 파일 그룹
--Orders_Range pf_OrderDate ps_OrderDate 1 PRIMARY
--Orders_Range pf_OrderDate ps_OrderDate 2 PRIMARY
--Orders_Range pf_OrderDate ps_OrderDate 3 PRIMARY
--Orders_Range pf_OrderDate ps_OrderDate 4 PRIMARY
USE jwjung
GO
INSERT INTO Orders_Range
SELECT a.CustomerID, a.EmployeeID
, CONVERT(CHAR(8), a.OrderDate, 112) as OrderDate
, a.RequiredDate
, a.ShippedDate, a.ShipVia, a.Freight, a.ShipName, a.ShipAddress
, a.ShipCity, a.ShipRegion, a.ShipPostalCode, a.ShipCountry
FROM Northwind.dbo.Orders a
, (SELECT TOP 100 * FROM Northwind.dbo.Orders) b
GO
SELECT substring(OrderDate, 1, 4) AS '주문연도'
, COUNT(*) '건수', SUM(Freight) '합계'
FROM Orders_Range
GROUP BY substring(OrderDate, 1, 4)
ORDER BY 1
GO
--주문연도 건수 합계
--1996 15200 1027987.00
--1997 40800 3246877.00
--1998 27000 2219405.00
--파티션 번호 알아내기
SELECT $partition.pf_OrderDate(OrderDate) as '파티션 번호'
, substring(OrderDate, 1, 4) AS '주문연도'
, COUNT(*) '건수', SUM(Freight) '합계'
FROM Orders_Range
GROUP BY $partition.pf_OrderDate (OrderDate), substring(OrderDate, 1, 4)
ORDER BY 1
GO
--파티션 번호 주문연도 건수 합계
--1 1996 15200 1027987.00
--2 1997 40800 3246877.00
--3 1998 27000 2219405.00
--참고로, 아래처럼 $partition 함수에 특정 값을 입력하면 해당 로우가 어떤 파티션에 저장될지 미리 확인할 수 있다.
SELECT '20091231' as '입력할 값'
, $partition.pf_OrderDate('20091231') as '파티션 번호'
GO
--입력할 값 파티션 번호
--20091231 4
--(2) 파티션 테이블 실행 계획
SELECT COUNT(*) as cnt, SUM(Freight) as Freight
FROM Orders_Range
WHERE OrderDate BETWEEN '19960101' AND '19961231'
GO
--(1개 행이 영향을 받음)
--테이블 'Orders_Range'. 검색 수 4, 논리적 읽기 수 1990, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
SELECT COUNT(*) as cnt, SUM(Freight) as Freight
FROM Orders_Range
WHERE OrderDate BETWEEN '19960101' AND '19971231'
GO
DECLARE @p1 CHAR(8), @p2 CHAR(8)
SET @p1 = '19960101'
SET @p2 = '19971231'
SELECT COUNT(*) as cnt, SUM(Freight) as Freight
FROM Orders_Range
WHERE OrderDate BETWEEN @p1 AND @p2
GO
--(1개 행이 영향을 받음)
--테이블 'Orders_Range'. 검색 수 2, 논리적 읽기 수 1340, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
DECLARE @p1 CHAR(8), @p2 CHAR(8)
SET @p1 = '19960101'
SET @p2 = '1998'
SELECT COUNT(*) as cnt, SUM(Freight) as Freight
FROM Orders_Range
WHERE OrderDate >= @p1
AND OrderDate < @p2
GO
--(1개 행이 영향을 받음)
--테이블 'Orders_Range'. 검색 수 3, 논리적 읽기 수 1990, 물리적 읽기 수 26, 미리 읽기 수 1344, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF