|
테이블을 만들고 데이터를 적제하고 어떻게하면 할당된 PGAE 에 많은 데이터를 넣어서 효율적으로 관리할까?
테이블 사이즈가 40G ~ 80G 가 되고 있는데, 풀스캔을 한번 타면 어떻게 될까? 생각만 해도 아찔한 순간입니다.. 모 사이트의 어떤서버를 통합하니 1 TERA 테이블이 생겼다. 인덱스 리빌딩을 해야 하는데... 이럴경우 관리자가 떠 안아야 할 부담은 거의 절정을 이루게된다. 이러한 대용량 테이블을 보다 효과적으로 관리하기 위해서 SQL 2005 에서는 PARTITIONED TABLE 이라는 기술을 선보이고 있습니다. 모두 아는 기능이고 해서 특별히 시간내기 어렵고, 기능을 실습해 보고 싶은 분을 위해서 간단하게 따라하기 식으로 글을 적어 보았습니다.
이미 다른 상용 DBMS 들은 다양한 테이블 분할방법 (RANGE PARTITIONED TABLE, LIST PARTITIONED TABLE, HASH PARTITIONED TABLE)을 선보이며, 실제로 운영하고 있습니다. 늦은 감이 없지는 않지만, 언제까지 2008년에 제품지원이 중단되는 2000만을 MSSQL 이라고 생각하고 있는 사람들도 우끼는 이야기 라고 생각합니다. 어떤 데이터베이스가 가장 멋진 데이터베이스가 아니라, 해당 제품을 가장 잘 핸들링 하는 사람이 있는 데이터베이스가 가장 좋은 데이터베이스 라고 개인적으로 생각합니다.
SQL 2000 엔터프라이즈를 쓰면서 SCHEMA BINDING INDEXED VIEW 도 모르고, MSCS 도 써보지 않았다면?? 혹자는 어려워 못쓸 것이고, 혹자는 들어보지 못해서 못 쓸 것입니다. 간단하게 실습하고 긴요하게 쓰이길 바랍니다.
우리가 꿈꾸고 갈망하던 많은 부분이 MSSQL 2005 를 통해서 해결되어 가고 있고, 더해서 차기 활화산(카트마이)도 멋지게 타고 있으니 열심히 공부해 보도록 하자.
목차
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
EXAMPLE
TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE
ON SQL 2005
http://msdn2.microsoft.com/en-us/library/aa964122.aspx
|
USE MASTER
GO
-- DROP DATABASE PDB
-- GO
/*-----------------------------------------------------------------------
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
* PARTITIONED FUNCTION
분할된 테이블 및 인덱스를 만드는 첫번째 단계로 CREATE PARTITION FUNCTION 이다.
-------------------------------------------------------------------------*/ |
CREATE PARTITION FUNCTION PARTITION_FUNCTION_NAME ( INPUT_PARAMETER_TYPE )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ BOUNDARY_VALUE [ ,...N ] ] )
[ ; ]
기준이 있어야 나누지!! 어떤 기준으로 나눌지를 정의하는 단계이다.
예를 들어 나는 SEQUENCE 를 기준으로 나누기를 원한다라고 생각 했다면,
|
CREATE PARTITION FUNCTION MYRANGEPF1 (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000); |
값
COL1 <= 1
COL1 > 1 AND COL1 <= 100
COL1 > 100 AND COL1 <= 1000
COL1 > 1000
|
CREATE PARTITION FUNCTION MYRANGEPF2 (INT)
AS RANGE RIGHT FOR VALUES (1, 100, 1000); |
값
COL1 < 1
COL1 >= 1 AND COL1 < 100
COL1 >= 100 AND COL1 < 1000
COL1 >= 1000
|
/*************************
[질문] 내 맘데로 넣으면 넌 어떻게 할꺼야?
**************************/
-- 실행
CREATE DATABASE PDB
GO
USE PDB
GO
CREATE PARTITION FUNCTION MYRANGEPF3 (INT)
AS RANGE LEFT FOR VALUES ( 1, NULL,1000, 100,-100);
GO
-- DROP PARTITION FUNCTION MYRANGEPF3 |
결과
경고: 파티션 함수 'MYRANGEPF3'의 범위 값 목록이 값을 기준으로 정렬되어 있지 않습니다. CREATE PARTITION SCHEME 작업 동안 파일 그룹에 파티션을 매핑하면 함수 'MYRANGEPF3'이(가) CREATE PARTITION SCHEME에서 참조된 경우 정렬된 경계 값을 사용합니다.
|
-- 확인
SELECT *
FROM SYS.PARTITION_RANGE_VALUES
WHERE
FUNCTION_ID IN
(
SELECT FUNCTION_ID
FROM SYS.PARTITION_FUNCTIONS
WHERE NAME = ('MYRANGEPF3')
) |
결과
| FUNCTION_ID |
BOUNDARY_ID |
PARAMETER_ID |
VALUE |
| ----------- |
----------- |
----------- |
----------- |
| 65536 |
1 |
1 |
NULL |
| 65536 |
2 |
1 |
-100 |
| 65536 |
3 |
1 |
1 |
| 65536 |
4 |
1 |
100 |
| 65536 |
5 |
1 |
1000 |
똑똑한 SQL 서버는 이 값들을 정렬해서 순서를 재배열 하여 사용하게 된다.
또한 NULL 은 우선순위 최상으로 소팅되는 되는 것을 알수 있다.
음 나누다 보니 보기 이상한데 하나더 나누어 보고 싶다면 어떻게 할까? 바꾸어 보자!
|
/* 구문정보
ALTER PARTITION FUNCTION PARTITION_FUNCTION_NAME()
{
SPLIT RANGE ( BOUNDARY_VALUE )
| MERGE RANGE ( BOUNDARY_VALUE )
} [ ; ]
*/ |
생성된 모든 파티션을 확인 부터 해보자.
개별 파티션 함수에 대한 정보를 얻으려면
|
SELECT * FROM SYS.PARTITION_FUNCTIONS --(파티션 함수정보)
SELECT * FROM SYS.PARTITION_PARAMETERS --(매개 변수 정보)
SELECT * FROM SYS.PARTITION_RANGE_VALUES --(경계구성)
SELECT * FROM SYS.PARTITION_SCHEMES --(파티션 구성정보)
SELECT * FROM SYS.DATA_SPACES --(파티션 구성정보)
SELECT * FROM SYS.DESTINATION_DATA_SPACES --(개별 파티션 구성정보)
SELECT * FROM SYS.PARTITIONS --(파티션 정보)
SELECT * FROM SYS.TABLES --(테이블 분할정보)
SELECT * FROM SYS.INDEXES --(인덱스 분할정보)
SELECT * FROM SYS.INDEX_COLUMNS --(인덱스 컬럼정보)
-- SPLIT
ALTER PARTITION FUNCTION MYRANGEPF3 ()
SPLIT RANGE (-1000);
GO
SELECT *
FROM SYS.PARTITION_RANGE_VALUES
WHERE
FUNCTION_ID IN
(
SELECT FUNCTION_ID
FROM SYS.PARTITION_FUNCTIONS
WHERE NAME = ('MYRANGEPF3')
) |
결과
| FUNCTION_ID |
BOUNDARY_ID |
PARAMETER_ID |
VALUE |
| ----------- |
----------- |
----------- |
----------- |
| 65536 |
1 |
1 |
NULL |
| 65536 |
2 |
1 |
-1000 |
| 65536 |
3 |
1 |
-100 |
| 65536 |
4 |
1 |
1 |
| 65536 |
5 |
1 |
100 |
| 65536 |
6 |
1 |
1000 |
알아서 잘 배열 되는 것을 관찰할 수 있다.
|
-- MERGE
ALTER PARTITION FUNCTION MYRANGEPF3 ()
MERGE RANGE (-1000); |
| FUNCTION_ID |
BOUNDARY_ID |
PARAMETER_ID |
VALUE |
| ----------- |
----------- |
----------- |
----------- |
| 65536 |
1 |
1 |
NULL |
| 65536 |
2 |
1 |
-100 |
| 65536 |
3 |
1 |
1 |
| 65536 |
4 |
1 |
100 |
| 65536 |
5 |
1 |
1000 |
정리
PARTITION_RANGE_VALUES + 1 개의 영역이 생기게 되고,기준값은 LEFT, RIGHT 의 지정에 따라 기준값의 위치가 다르게 속하게 되는 것을 알 수 있다. 예를들어 RIGHT 파티션을 만들고 경계값이 NULL 이 있다면, RIGHT 파티션의 경우 기준값을 포함하지 않는 더작은 영역을 만들게 된다. 그러나 NULL 보다 더 작은 값이 없으므로 첫번째 파티션은 비게 된다.
|
DROP PARTITION FUNCTION MYRANGEPF3
GO
CREATE PARTITION FUNCTION MYRANGEPF3 (INT)
AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000);
GO |
참고 : 기준값을 정열해서 배치하면 에러 없음
5개로 기준값으로 나누었으니 다음과 같은 6개의 영역이 생긴다.
1 값 < NULL -- 첫번째 기준값 보다 작은 값들 (빈파티션)
2 NULL <= 값 < -100 -- NULL 포함 기준값보다 더 큰 값들
3 -100 <= 값 < 1 -- -100 포함 다음기준값 보다 작은 값들
4 1 <= 값 < 100 -- 1 포함 다음기준값 보다 작은 값들
5 100 <= 값 < 1000 -- 100 포함 다음기준값 보다 작은 값들
6 값 >= 1000 -- 1000 포함 더 큰값들 다
|
/*-----------------------------------------------------------------------
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
* PARTITIONED SCHEME
파일그룹 매핑 구성표 만들기 (필수 선행작업: 파티션 함수 생성)
-------------------------------------------------------------------------*/
/* 구문정보
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]
*/ |
ALL 을 지정하면 하나의 파일그룹만을 지정 할 수 있고, 파티션 펑션의 기준값 보다 같거나 많은 개수를 정의해야 하며, 적을 경우 에러를 나타내며 종료 많은경우 NEXT USED 로 정의 됨
|
-- 실험
CREATE PARTITION SCHEME PSMYSCHEME3
AS PARTITION MYRANGEPF3
TO (FG1,FG2) |
결과
메시지 7707, 수준 16, 상태 1, 줄 2
연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다.
음~ 파일그룹이 있는지 없는지 검사하지도 않고 바로 에러!
|
-- 설정하려는 파티션 펑션의 기준값 개수 확인
SELECT *
FROM SYS.PARTITION_RANGE_VALUES
WHERE
FUNCTION_ID IN
(
SELECT FUNCTION_ID
FROM SYS.PARTITION_FUNCTIONS
WHERE NAME = ('MYRANGEPF3')
) |
| 65537 |
1 |
1 |
NULL |
| 65537 |
2 |
1 |
-100 |
| 65537 |
3 |
1 |
1 |
| 65537 |
4 |
1 |
100 |
| 65537 |
5 |
1 |
1000 |
|
-- 음~ 최소 6개 만들고 더 만들면 다음파티션으로 쓰겠군...
CREATE PARTITION SCHEME PSMYSCHEME3
AS PARTITION MYRANGEPF3
TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7) |
결과
메시지 208, 수준 16, 상태 58, 줄 1
개체 이름 'FG1'이(가) 잘못되었습니다.
개수를 만족하고 나서 파일 그룹이 실제로 있는지 찾음 해당데이터 베이스에 파일그룹이 없으면 에러가 난다.
여기서 잠깐! 스토리지 이야기도 조금 하고 파일그룹 및 데이터 파일 핸들링 하는 방법에 대해서도 조금만 알아보자.
데이터 베이스에서 파일의 숫자 만큼 쓰래드가 생성 될 수 있기 때문에 파일을 여러개 만드는 것이 좋다. 클러스터 인덱스가 있는 데이터 파일을 파일그룹, 난클러스터 인덱스는 다른 디스크에 위치시키는 것이 절대적으로 유리하다. 파일그룹을 잘게 나누면 파일그룹별로 백업 복구도 가능하고 용량산정을 위해 다른 위치로 옮길때도 옮기기 좋은 단위가 된다는 것을 상기하자!
또한 로그파일[LDF] 는 분리된 다른 위치에 독립적으로 쓰게 하는것이 좋다. I/O 패턴이 다른 파일을 같은 디스크에 배치하는 것은 어떤경우에도 좋은 방법이 되지 못한다.
개인적으로 로그는 300G 10000RPM RAID 1 을 추천하고 싶다. 최대 테이블 사이즈가 300기가를 넘는다면 다른 방법을 구상하도록 한다.
또한 데이터 디스크는 RAID 1+0 를 추천한다. MMORPG 같은 경우나 빈번하고 짧은 트랜젝션이 많은 데이터베이스에서 쓰기스피드가 느린 RAID 5 는 CheckPoint시 트랜잭션 숫자를 감소시키는 주요 범인이 된다.
로컬백업을 한다면 로컬백업이 되는 위치는 백업시간을 단축하기 위해 RAID 0 에 위치시킨다. 두장의 RAID 5 로 구성된 디스크 두장에 백업 하는것 보다 두장의 RAID 0 로 백업한다면 훨씬 더 빠른 백업을 수행 할 수 있다. 그러나 디스크 장애로 잦은 RAID 리빌딩을 하기 싫다면 디스크 에러에 강한 RAID 1+0 로 구성하는 것이 바르다고 할 수 있다.
이런 디스크 문제가 빈번하게 일어난다면 스토리지 시스템을 도입하는 것을 고려해 보기 바란다. 스토리지 시스템은 자체 리드 캐쉬 라이트 캐쉬가 있기 때문에 메모리까지만 받아주면 스토리지 컨트롤러가 알아서 디스크에 쓸것이다. 최신 엔터프라이즈 스토리지는 컨트롤러 캐쉬가 16G에 이른다.
|
-- 파일그룹 생성방법
ALTER DATABASE PDB
ADD FILEGROUP FG1
-- 파일그룹에 간단한 파일 추가
ALTER DATABASE PDB
ADD FILE ( NAME = PDB_DAT1, FILENAME = 'C:\FG1\PDB_DAT1.ndf') TO FILEGROUP FG1
-- 삭제방법
-- 1, 파일 삭제
ALTER DATABASE PDB
REMOVE FILE PDB_DAT1
-- 2, 파일그룹 삭제
ALTER DATABASE PDB
REMOVE FILEGROUP FG1
-- 좀더 자세하게
USE master
GO
ALTER DATABASE PDB
ADD FILEGROUP FG1;
ALTER DATABASE PDB
ADD FILEGROUP FG2;
ALTER DATABASE PDB
ADD FILEGROUP FG3;
ALTER DATABASE PDB
ADD FILEGROUP FG4;
ALTER DATABASE PDB
ADD FILEGROUP FG5;
ALTER DATABASE PDB
ADD FILEGROUP FG6;
ALTER DATABASE PDB
ADD FILEGROUP FG7;
ALTER DATABASE PDB
ADD FILEGROUP FG8;
GO
-- 해당경로를 미리 만들어 두어야 한다.
-- C:\ 드라이브에 FG1,FG2,FG3,FG4,FG5
-- G:\ 드라이브에 FG6,FG7,FG8
ALTER DATABASE PDB
ADD FILE
(
NAME = PDB_DAT1,
FILENAME = 'C:\FG1\PDB_DAT1.ndf', -- 테스트 이므로
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
--, -- 여러개의 파일로 분할 하고 싶은 경우
--(
-- NAME = PDB_DAT2,
-- FILENAME = 'C:\FG1\PDB_DAT2.ndf',
-- SIZE = 5MB,
-- MAXSIZE = 100MB,
-- FILEGROWTH = 5MB
--)
TO FILEGROUP FG1
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT2,FILENAME = 'C:\FG2\PDB_DAT2.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG2
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3,FILENAME = 'C:\FG3\PDB_DAT3.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT4,FILENAME = 'C:\FG4\PDB_DAT4.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG4
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT5,FILENAME = 'C:\FG5\PDB_DAT5.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG5
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT6,FILENAME = 'G:\FG6\PDB_DAT6.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG6
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT7,FILENAME = 'G:\FG7\PDB_DAT7.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG7
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT8,FILENAME = 'G:\FG8\PDB_DAT8.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG8
-- 다시 하던일 계속하자!
USE PDB
GO
CREATE PARTITION SCHEME PSMYSCHEME3
AS PARTITION MYRANGEPF3
TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7,FG8)
GO |
결과 R1
파티션 구성표 'PSMYSCHEME3'이(가) 작성되었습니다. 파티션 구성표 'PSMYSCHEME3'에서 'FG7'은(는) 다음에 사용되는 파일 그룹으로 표시됩니다.
다음에 사용되는 파일 그룹 다음에 지정된 1 파일 그룹은 무시됩니다.
음~ 우리는 6개의 파일그룹만 필요한데, 더 만들어 두니 바로 다음 파일그룹은 NEXT USED 로 지정되고 나머지들은 무시되는 것이구나.
|
-- 삭제방법
-- DROP PARTITION SCHEME PSMYSCHEME3
-- 해당디비의 상황
EXEC SP_HELPDB N'PDB'
EXEC SP_HELPFILEGROUP;
EXEC SP_HELPFILE;
SELECT * FROM SYS.SYSALTFILES WHERE DBID = DB_ID('PDB') |
여기서 잠깐!
데이터 베이스 미러링 같은 경우 피지컬 영역 구성이 다를수 있고 스토리지 용량도 보장 할 수 없고 이런 파티션 스킴을 고치는 작업은 미러링중에 미러 대상서버에 적용되지 않는다.
|
/*-----------------------------------------------------------------------
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
* CREATE PARTITIONED TABLE
준비하는데 수고했습니다. 이제 파티션드 테이블을 만들어 보겠습니다.
-------------------------------------------------------------------------*/
-- 두번째 작업에서 힘들게 만들었던 파일스키마 정의에 테이블을 매핑 시켜 만든다.
IF OBJECT_ID('DBO.PT_LOG') IS NOT NULL
DROP TABLE DBO.PT_LOG
GO
CREATE TABLE [dbo].[PT_LOG] (
PIDX INT -- 파티션 기준값
, IDX INT IDENTITY(1,1)
, DATA VARCHAR(6000) NULL
) ON PSMYSCHEME3 (PIDX)
GO
/*-----------------------------------------------------------------------
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
* CHECK PARTITIONED INFORMATION ,PARTITION MERGE, PARTITION SPLIT
데이터 삽입과 파티션 정보확인 및 MERGE SPLIT 해보도록 하겠습니다. NULL 은 어디에 있을까? 궁금하지 않나요?
-------------------------------------------------------------------------*/
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (NULL, REPLICATE('A',6000))
GO 601 -- 인서트 601번 실행
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (-100, REPLICATE('A',6000))
GO 602
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (1, REPLICATE('A',6000))
GO 603
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (100, REPLICATE('A',6000))
GO 604
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (1000, REPLICATE('A',6000))
GO 605
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (2000, REPLICATE('A',6000))
GO 606
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT COUNT(*) FROM PT_LOG |
결과
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1008],0)))
|--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007]))
** |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6))))
|--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]))
테이블 'PT_LOG'. 검색 수 6, 논리적 읽기 수 3621, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
별표 별표(**) 부분이 어떤 파티션을 읽었는지 보여주눈 부분이다. 상수 조인을 이용한 6회 읽은 것을 알 수 있다.
|
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF |
결과
3621
|
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT COUNT(*) FROM PT_LOG WHERE PIDX IS NULL
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF |
결과
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
|--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
|--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX] IS NULL) PARTITION ID:((2)))
|
-- 파티션 아이디 2번에서만 읽은 것을 볼 수 있다. 아무리 봐도 넘 이쁜 넘이다.
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT COUNT(*)
FROM PT_LOG
WHERE
PIDX IS NULL OR
PIDX = 1
GROUP BY PIDX
HAVING PIDX = 1
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF |
결과
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
|--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
|--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX]=(1)) PARTITION ID:((4)))
|
SELECT OBJECT_NAME(OBJECT_ID), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID('PT_LOG')
ORDER BY PARTITION_NUMBER, INDEX_ID; |
결과
partition_id object_id index_id partition_number hobt_id rows
| PT_LOG |
72057594040680448 |
53575229 |
0 |
1 |
72057594040680448 |
0 |
| PT_LOG |
72057594040745984 |
53575229 |
0 |
2 |
72057594040745984 |
601 |
| PT_LOG |
72057594040811520 |
53575229 |
0 |
3 |
72057594040811520 |
602 |
| PT_LOG |
72057594040877056 |
53575229 |
0 |
4 |
72057594040877056 |
603 |
| PT_LOG |
72057594040942592 |
53575229 |
0 |
5 |
72057594040942592 |
604 |
| PT_LOG |
72057594041008128 |
53575229 |
0 |
6 |
72057594041008128 |
1211 |
위와같이 RIGHT TYPE 에서는 NULL 보다 논리적으로 더 작은 것이 없으므로 제일 안쪽 파티션이 항상 비는 것을 알 수 있다.
음~ 배운게 있으니 1211 을 적당하게 다시 나누어 보아야 겠다.
파티션 기준값을 어떻게 나누었는지 다음 쿼리로 확인해 보자.
|
SELECT *
FROM SYS.PARTITION_RANGE_VALUES
WHERE
FUNCTION_ID IN
(
SELECT FUNCTION_ID
FROM SYS.PARTITION_FUNCTIONS
WHERE NAME = ('MYRANGEPF3')
) |
결과
| function_id |
boundary_id |
parameter_id |
value |
| ----------- |
----------- |
----------- |
----- |
| 65541 |
1 |
1 |
NULL |
| 65541 |
2 |
1 |
-100 |
| 65541 |
3 |
1 |
1 |
| 65541 |
4 |
1 |
100 |
| 65541 |
5 |
1 |
1000 |
2000으로 경계값을 더 나누어 보자 위에서 아래와 같은 데이터를 넣었다는것을 우리는 알고 있으므로
|
-- INSERT INTO PT_LOG (PIDX, DATA) VALUES (2000, REPLICATE('A',6000))
-- GO 606
-- 다음과 같은 쿼리를 실행했다.
ALTER PARTITION FUNCTION MYRANGEPF3 ()
SPLIT RANGE (2000);
GO |
결과 잘 나누어져 들어갔음을 확인 할 수 있다.
| FUNCTION_ID |
BOUNDARY_ID |
PARAMETER_ID |
VALUE |
| ----------- |
----------- |
----------- |
----- |
| 65541 |
1 |
1 |
NULL |
| 65541 |
2 |
1 |
-100 |
| 65541 |
3 |
1 |
1 |
| 65541 |
4 |
1 |
100 |
| 65541 |
5 |
1 |
1000 |
| 65541 |
6 |
1 |
2000 |
|
ALTER PARTITION FUNCTION MYRANGEPF3 ()
SPLIT RANGE (3000);
GO |
결과
메시지 7707, 수준 16, 상태 1, 줄 1
연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다.
우리는 위에서 파티션 스키마를 만들때, 결과 R1 에서 NEXT USED 로 설정된 FG7 까지만 적용이 되고 FG8에는 무시되었던 것을 기억할 것입니다. 이런 결과 값이 나오는 테이블에서 2000 보다 큰 값이 들어갈 영역을 만들려고 했으나, 역시 무시되는 것을 알 수 있습니다. 그럼 NEXT USED로 다음 파일그룹이 사용될 영역만 다시 정의해 주면 될것을 생각할 수 있겠습니다.
결과
Data_located_on_filegroup
-------------------------
PSMYSCHEME3
|
-- 해당 테이블의 스키마 정보를 이용해서 어떤 파일그룹을 쓰고 있는지 조사
SELECT
A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,
B.DATA_SPACE_ID,
C.NAME
FROM
SYS.DATA_SPACES A
INNER JOIN SYS.DESTINATION_DATA_SPACES B
ON A.DATA_SPACE_ID = B.DATA_SPACE_ID
INNER JOIN SYS.PARTITION_SCHEMES C
ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID
WHERE
C.NAME = 'PSMYSCHEME3' |
결과
| NAME |
DATA_SPACE_ID |
IS_DEFAULT |
DATA_SPACE_ID |
NAME |
| ---- |
------------- |
------------- |
------------- |
---- |
| FG1 |
2 |
0 |
2 |
PSMYSCHEME3 |
| FG2 |
3 |
0 |
3 |
PSMYSCHEME3 |
| FG3 |
4 |
0 |
4 |
PSMYSCHEME3 |
| FG4 |
5 |
0 |
5 |
PSMYSCHEME3 |
| FG5 |
6 |
0 |
6 |
PSMYSCHEME3 |
| FG6 |
7 |
0 |
7 |
PSMYSCHEME3 |
| FG7 |
8 |
0 |
8 |
PSMYSCHEME3 |
|
-- 해당 디비의 파일그룹 조회
EXEC SP_HELPFILEGROUP |
결과
| groupname |
groupid |
filecount |
| --------- |
------- |
--------- |
| PRIMARY |
1 |
1 |
| FG1 |
2 |
1 |
| FG2 |
3 |
1 |
| FG3 |
4 |
1 |
| FG4 |
5 |
1 |
| FG5 |
6 |
1 |
| FG6 |
7 |
1 |
| FG7 |
8 |
1 |
| FG8 |
9 |
1 |
|
-- 해당 스킴의 파티션펑션 및 TYPE 정보 조회
SELECT
A.BOUNDARY_ID, A.VALUE,
B.NAME,B.TYPE_DESC,B.FANOUT, BOUNDARY_VALUE_ON_RIGHT
FROM SYS.PARTITION_RANGE_VALUES A
INNER JOIN SYS.PARTITION_FUNCTIONS B
ON A.FUNCTION_ID = B.FUNCTION_ID
INNER JOIN SYS.PARTITION_SCHEMES C
ON B.FUNCTION_ID = C.FUNCTION_ID
WHERE
C.NAME = 'PSMYSCHEME3'
|
결과
| BOUNDARY_ID |
VALUE NAME |
TYPE_DESC |
FANOUT |
BOUNDARY_VALUE_ON_RIGHT |
| ----------- |
---------- |
--------- |
------ |
----------------------- |
| 1 |
NULL |
MYRANGEPF3 |
RANGE 8 |
1 |
| 2 |
-100 |
MYRANGEPF3 |
RANGE 8 |
1 |
| 3 |
1 |
MYRANGEPF3 |
RANGE 8 |
1 |
| 4 |
100 |
MYRANGEPF3 |
RANGE 8 |
1 |
| 5 |
1000 |
MYRANGEPF3 |
RANGE 8 |
1 |
| 6 |
2000 |
MYRANGEPF3 |
RANGE 8 |
1 |
해당하는 스키마는 PRIMARY 파일 그룹과 FG8을 안쓰고 있다는 것을 알 수 있다.
우리는 FG8을 다음 파티션 그룹으로 쓰기로 하자.
|
-- 용법
ALTER PARTITION SCHEME partition_scheme_name
NEXT USED [ filegroup_name ] [ ; ]
-- 실행
ALTER PARTITION SCHEME PSMYSCHEME3
NEXT USED FG8
GO |
결과
명령이 완료되었습니다.
|
-- 확인
SELECT
A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,
B.DATA_SPACE_ID,
C.NAME
FROM
SYS.DATA_SPACES A
INNER JOIN SYS.DESTINATION_DATA_SPACES B
ON A.DATA_SPACE_ID = B.DATA_SPACE_ID
INNER JOIN SYS.PARTITION_SCHEMES C
ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID
WHERE
C.NAME = 'PSMYSCHEME3' |
groupname groupid filecount
--------- ------- ---------
| BOUNDARY_ID |
VALUE NAME |
TYPE_DESC |
FANOUT |
BOUNDARY_VALUE_ON_RIGHT |
| FG1 |
2 |
0 |
2 |
PSMYSCHEME3 |
| FG2 |
3 |
0 |
3 |
PSMYSCHEME3 |
| FG3 |
4 |
0 |
4 |
PSMYSCHEME3 |
| FG4 |
5 |
0 |
5 |
PSMYSCHEME3 |
| FG5 |
6 |
0 |
6 |
PSMYSCHEME3 |
| FG6 |
7 |
0 |
7 |
PSMYSCHEME3 |
| FG7 |
8 |
0 |
8 |
PSMYSCHEME3 |
| FG8 |
9 |
0 |
9 |
PSMYSCHEME3 |
잘 적용된것을 확인 할 수 있다.
|
-- 펑션을 수정해 보도록 하자 !
ALTER PARTITION FUNCTION MYRANGEPF3 ()
SPLIT RANGE (3000);
GO |
잘 수행되는 것을 확인 할 수 있을 것이다.
이제는 다양한 값을 넣어 보겠습니다.
|
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (NULL, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (-99, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (2, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (101, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (1001, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (2001, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (3000, REPLICATE('A',6000))
GO 3
INSERT INTO PT_LOG (PIDX, DATA)
VALUES (3001, REPLICATE('A',6000))
GO 3
SELECT OBJECT_NAME(OBJECT_ID), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID('PT_LOG')
ORDER BY PARTITION_NUMBER, INDEX_ID; |
결과
groupname groupid filecount
--------- ------- ---------
| PT_LOG |
72057594041663488 |
117575457 |
0 |
1 |
72057594041663488 |
0 |
| PT_LOG |
72057594041729024 |
117575457 |
0 |
2 |
72057594041729024 |
604 |
| PT_LOG |
72057594041794560 |
117575457 |
0 |
3 |
72057594041794560 |
605 |
| PT_LOG |
72057594041860096 |
117575457 |
0 |
4 |
72057594041860096 |
606 |
| PT_LOG |
72057594041925632 |
117575457 |
0 |
5 |
72057594041925632 |
607 |
| PT_LOG |
72057594041991168 |
117575457 |
0 |
6 |
72057594041991168 |
608 |
| PT_LOG |
72057594042056704 |
117575457 |
0 |
7 |
72057594042056704 |
609 |
| PT_LOG |
72057594042122240 |
117575457 |
0 |
8 |
72057594042122240 |
6 |
|
SELECT
$PARTITION.MYRANGEPF3(PIDX) AS 'PARTITION ID',
MIN(PIDX) AS 'MIN',
MAX(PIDX) AS 'MAX',
COUNT(*) AS 'ROWCNT'
FROM
PT_LOG
GROUP BY $PARTITION.MYRANGEPF3(PIDX)
ORDER BY 1 |
결과
| PARTITION ID |
MIN |
MAX |
ROWCNT |
| ------------ |
----------- |
----------- |
----------- |
| 2 |
NULL |
NULL |
604 |
| 3 |
-100 |
-99 |
605 |
| 4 |
1 |
2 |
606 |
| 5 |
100 |
101 |
607 |
| 6 |
1000 |
1001 |
608 |
| 7 |
2000 |
2001 |
608 |
| 8 |
3000 |
3001 |
6 |
|
-- 파티션 머지
ALTER PARTITION FUNCTION MYRANGEPF3 ()
MERGE RANGE (1000);
SELECT
A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,
B.DATA_SPACE_ID,
C.NAME
FROM
SYS.DATA_SPACES A
INNER JOIN SYS.DESTINATION_DATA_SPACES B
ON A.DATA_SPACE_ID = B.DATA_SPACE_ID
INNER JOIN SYS.PARTITION_SCHEMES C
ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID
WHERE
C.NAME = 'PSMYSCHEME3' |
결과 FG6 이 없어진 것을 확인 할 수 있다.
| FG1 |
2 |
0 |
2 |
PSMYSCHEME3 |
| FG2 |
3 |
0 |
3 |
PSMYSCHEME3 |
| FG3 |
4 |
0 |
4 |
PSMYSCHEME3 |
| FG4 |
5 |
0 |
5 |
PSMYSCHEME3 |
| FG5 |
6 |
0 |
6 |
PSMYSCHEME3 |
| FG6 |
7 |
0 |
7 |
PSMYSCHEME3 |
| FG7 |
8 |
0 |
8 |
PSMYSCHEME3 |
| FG8 |
9 |
0 |
9 |
PSMYSCHEME3 |
결과
| PRIMARY |
1 |
1 |
| FG1 |
2 |
1 |
| FG2 |
3 |
1 |
| FG3 |
4 |
1 |
| FG4 |
5 |
1 |
| FG5 |
6 |
1 |
| FG6 |
7 |
1 |
| FG7 |
8 |
1 |
| FG8 |
9 |
1 |
물리적 파일 그룹은 그대로 이다.
결과
| 1 |
1 |
35 |
22 |
PDB |
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PDB.mdf |
| 3 |
2 |
80 |
1 |
PDB_DAT1 |
C:\FG1\PDB_DAT1.ndf |
| 4 |
3 |
80 |
78 |
PDB_DAT2 |
C:\FG2\PDB_DAT2.ndf |
| 5 |
4 |
80 |
78 |
PDB_DAT3 |
C:\FG3\PDB_DAT3.ndf |
| 6 |
5 |
80 |
78 |
PDB_DAT4 |
C:\FG4\PDB_DAT4.ndf |
| 7 |
6 |
160 |
78 |
PDB_DAT5 |
C:\FG5\PDB_DAT5.ndf |
| 8 |
7 |
160 |
78 |
PDB_DAT6 |
G:\FG6\PDB_DAT6.ndf |
| 9 |
8 |
80 |
79 |
PDB_DAT7 |
G:\FG7\PDB_DAT7.ndf |
| 10 |
9 |
80 |
2 |
PDB_DAT8 |
G:\FG8\PDB_DAT8.ndf |
정확하게 예전과 같이 분할된 것을 볼 수 있다.
|
/*-----------------------------------------------------------------------
PARTITIONED TABLE
PARTITIONED FUNCTION
PARTITIONED SCHEME
CREATE PARTITIONED TABLE
CHECK PARTITIONED INFORMATION
PARTITION MERGE
PARTITION SPLIT
PARTITION SWITCH
* PARTITION SWITCH
이제 파티션 이동하기를 해보겠습니다. 대용량 로그 파일이 있을때 DELETE 로 지우면서
세월아 내월아 기다릴 수 없으니 한방에 날릴 수 있도록 다른 파티션으로 이동해 보도록 하겠습니다.
그리고 스키마 정보만 바꾸어서
-------------------------------------------------------------------------*/
EXEC SP_HELPFILEGROUP
GO
ALTER DATABASE PDB
ADD FILEGROUP FG3_AUX;
GO
-- 폴더를 미리 만들고
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3_AUX,FILENAME = 'C:\FG3_AUX\PDB_DAT3_AUX.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3_AUX
IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL
DROP TABLE DBO.PT_LOG_AUX
GO
CREATE TABLE [dbo].[PT_LOG_AUX] (
PIDX INT -- 파티션 기준값
, IDX INT IDENTITY(1,1)
, DATA VARCHAR(6000) NULL
) ON FG3_AUX
GO
DBCC SHOWFILESTATS
ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ;
GO |
메시지 4939, 수준 16, 상태 1, 줄 1
ALTER TABLE SWITCH 문이 실패했습니다. 테이블 'PDB.dbo.PT_LOG_AUX'은(는) 파일 그룹 'FG3_AUX'에 있고 테이블 'PDB.dbo.PT_LOG'의 파티션 3은(는) 파일 그룹 'FG3'에 있습니다.
|
-- 파일 그룹이 다른 테이블은 스위칭 하지 못한다.
IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL
DROP TABLE DBO.PT_LOG_AUX
GO
CREATE TABLE [dbo].[PT_LOG_AUX] (
PIDX INT -- 파티션 기준값
, IDX INT IDENTITY(1,1)
, DATA VARCHAR(6000) NULL
) ON FG3
GO
ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ;
GO
-- 성공적으로 스위칭 된다.
SELECT * FROM PT_LOG WHERE PIDX = -100
-- 하나도 없다.
SELECT *
FROM SYS.PARTITION_RANGE_VALUES
WHERE
FUNCTION_ID IN
(
SELECT FUNCTION_ID
FROM SYS.PARTITION_FUNCTIONS
WHERE NAME = ('MYRANGEPF3')
)
SELECT * FROM PT_LOG_AUX
-- 결과값이 나온다.
DBCC SHOWFILESTATS
-- 결국 같은 파일 그룹에서 정의만 다르게 해서 다른 테이블로 빼버리는 형태이다. 결론적으로 부하가 없다는 말입니다.
ALTER TABLE PT_LOG_AUX SWITCH TO [PT_LOG] PARTITION 3 ;
GO |
결과
메시지 4982, 수준 16, 상태 1, 줄 2
ALTER TABLE SWITCH 문이 실패했습니다. 원본 테이블 'PDB.dbo.PT_LOG_AUX'의 CHECK 제약 조건에서 대상 테이블 'PDB.dbo.PT_LOG'의 파티션 3에서 정의한 범위가 허용하지 않는 값을 허용합니다.
|
SELECT OBJECT_NAME(OBJECT_ID), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID('PT_LOG')
ORDER BY PARTITION_NUMBER, INDEX_ID; |
결과
PT_LOG 72057594039042048 2073058421 0 3 72057594039042048 0
실제 파티션이 비어 있음에도 FUNCTION 제약 조건이 단일 테이블에는 적용되어 있지 않기 때문에 다시 스위칭 하는것을 허용하지 않는것 같습니다.
꽁수를 부려라...
|
-- 꽁수 시작
CREATE PARTITION FUNCTION MYRANGEPF3_AUX (INT)
AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000);
GO
CREATE PARTITION SCHEME PSMYSCHEME3_AUX
AS PARTITION MYRANGEPF3
ALL TO (FG3)
IF OBJECT_ID('DBO.PT_LOG_SRC') IS NOT NULL
DROP TABLE DBO.PT_LOG_AUX
GO
CREATE TABLE [dbo].[PT_LOG_SRC] (
PIDX INT -- 파티션 기준값
, IDX INT IDENTITY(1,1)
, DATA VARCHAR(6000) NULL
) ON PSMYSCHEME3_AUX(PIDX)
GO
SET IDENTITY_INSERT PT_LOG_SRC ON
INSERT INTO PT_LOG_SRC (PIDX, IDX, DATA)
SELECT PIDX, IDX, DATA FROM PT_LOG_AUX
SET IDENTITY_INSERT PT_LOG_SRC OFF
ALTER TABLE PT_LOG_SRC SWITCH PARTITION 3 TO [PT_LOG] PARTITION 3 ;
GO |
원래대로 끼워넣기 성공 파티션 스위치에 대한 감을 잡으셨기를 바랍니다.
다음 기사는 MSDN 에 올라온 글입니다.
관심있는 독자는 읽어보시길 바랍니다. SAN DISK 환경에서 일별로 어마어마한 로그가 쌓인다는 가정하에 2개의 자동화 프로시져를 이용하여 특정일 이후의 데이터를 삭제하는 멋진 로직을 설명 하고 있습니다. SQL2000 에서는 SP_RENAME 을 이용한 일별로그를 분리하곤 하지만 다양한 관리의 부담을 보이고 있으나, 오늘 배운 파티셔닝 기술과 SQL AGENT 로 두개의 프로시져만 번갈아 부르면 자동으로 현재일로부터 과거 몇일까지의 데이터를 저장하고 있는 테이블을 만들 수 있습니다.
EXAMPLE
HOW TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE ON SQL 2005
http://msdn2.microsoft.com/en-us/library/aa964122.aspx
끝으로 이번에 장가 갑니다. 멀리서나마 축하해 주시면 감사하겠습니다.
또한 김연욱 선배님(SQL MVP), 김대우님(MS DPE팀), 김종균님(TECHDATA), 송혁님(SQL MVP),장홍창님 그리고 많은 우리 SQL OFFLINE STUDY 사람들이 도와줘서 이글을 쓰게 되었습니다. 감사합니다.
HTTP://SQLER.PE.KR에 오시면 석이를 만날 수 있습니다. ^^;
부족한 글 끝까지 읽어 주셔서 감사합니다.
원문 : http://blog.naver.com/backs55/100035813367 |