특징
1) 어떤 하나의 테이블이 특정 컬럼의 value 값에 따라 서른 다른 물리적인 파일로 저장.
2) 대용량 데이터베이스화 하기 위한 필수 기술
3) MS-SQL2005 부터 지원
4) 읽고 쓸 때 TRAFFIC의 OVERHEAD를 막을 수 있다.
5) PARTITON TABLE은 생성시에만 별도의 작업이 필요할 뿐, 실제 데이터의 추가, 삭제, 조회는 기
존의 방법과 동일하게 이루어진다
실행계획 | |
STEP 1 | HARDWARE적인 설계 및 기획 |
STEP 2 | 테이블을 분리하는데 기준이 되는 PARTITION FUNCTION 생성 |
STEP 3 | PARTITION SCHEMA 생성 |
STEP 4 | 논리적으로 접근할 TABLE 생성 |
1. STEP 1
1)로컬 디렉터리 생성
xp_cmdshell 'mkdir E:\MSSQL\PART\DATA1'
go
xp_cmdshell 'mkdir E:\MSSQL\PART\DATA2'
go
xp_cmdshell 'mkdir E:\MSSQL\PART\DATA3'
go
xp_cmdshell 'mkdir E:\MSSQL\PART\INDEX'
go
xp_cmdshell 'mkdir E:\MSSQL\PART\LOG'
go
2) DATABASE CREATION
CREATE DATABASE PART
ON PRIMARY
(
NAME = 'PART_DATA1959',
FILENAME = 'E:\MSSQL\PART\DATA1\PART_DATA1959.MDF',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH= 10%
)
,
FILEGROUP PART2
(
NAME = 'PART_DATA1960_69',
FILENAME = 'E:\MSSQL\PART\DATA2\PART2_DATA1960_69.NDF',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH= 10%
)
,
FILEGROUP PART3
(
NAME = 'PART_DATA1970',
FILENAME = 'E:\MSSQL\PART\DATA3\PART3_DATA1970.NDF',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH= 10%
)
,
FILEGROUP INDEXGROUP
(
NAME = 'PART_IDX',
FILENAME = 'E:\MSSQL\PART\INDEX\PART_IDX.NDF',
SIZE = 4MB,
MAXSIZE = 8MB,
FILEGROWTH= 10%
)
LOG ON
(
NAME = 'PART_LOG',
FILENAME = 'E:\MSSQL\PART\LOG\PART_LOG.LDF',
SIZE = 2MB,
MAXSIZE = 4MB,
FILEGROWTH= 10%
)
GO
2. STEP 2
1)파티션 함수생성
CREATE PARTITION FUNCTION PF_AGE(INT)
AS
RANGE [LEFT|RIGHT] FOR VALUES(1959, 1970) 분기점 left면 1.~1959 2.1960~1970 3.1971~
GO 분기점 right면 1.~1960 2.1961~1969 3.1970
--$Patition . 파티션 함수(값)으로 분할 번호 확인
select $partition.pf_age(1950);
select $partition.pf_age(1959);
select $partition.pf_age(1960);
select $partition.pf_age(1965);
select $partition.pf_age(1966);
select $partition.pf_age(1969);
select $partition.pf_age(1970);
select $partition.pf_age(1971);
select $partition.pf_age(1979);
--2. 파티션 스키마 생성
CREATE PARTITION SCHEME PS_AGE
AS
PARTITION PF_AGE --PARTITION FUNCTION PF_AGE(INT)기준으로 의해
TO([PRIMARY], [PART2], [PART3]) -- 파일그룹이름
GO
--3. 논리 테이블 생성
CREATE TABLE FAMILY
(
ID VARCHAR(12) NOT NULL,
NAME VARCHAR(10) NOT NULL,
BIRTHYEAR INT, -- 위에도 INT
ADDR NVARCHAR(50)
)ON PS_AGE(BIRTHYEAR) --birthyear는 Partition scheme의해 저장 된다
GO
--primary key 지정
ALTER TABLE FAMILY
ADD CONSTRAINT F_ID_PK PRIMARY KEY(ID)
GO
-- 실행이 불가능
-- PRIMARY KEY는 기본적으로 CLUSTERED INDEX를 이용하므로(정렬되서 보관되는 저장공간이 한곳이다)
-- 저장 공간이 분산되어 있는 PARTITION TABLE에서는 사용할 수 없다.
create unique clustered index famiy_birth_id_pk -- not조건은 적용이 안되지만 PK와 같다.
on family(birthyear,id)
go
INSERT INTO FAMILY
VALUES('FATHER','아버지',1938,'경북포항시용흥동');
INSERT INTO FAMILY
VALUES('MOTHER','어머니',1940,'경북포항시용흥동');
INSERT INTO FAMILY
VALUES('OLDSISTER','큰누나',1965,'서울강남구');
INSERT INTO FAMILY
VALUES('YOUNGSISTER','작은누나',1967,'서울동작구');
INSERT INTO FAMILY
VALUES('BROTHER','동생',1974,'서울서초구');
INSERT INTO FAMILY
VALUES('WIFE','마누라',1974,'서울송파구');
INSERT INTO FAMILY
VALUES('CHILD','딸',2002,'서울송파구');
SELECT * FROM FAMILY
ADDITIONAL FEATURE
- 분산 저장된 데이터별로 검색
: $PARTITION.[PARTITON_FUNCTION()] = [PARTITION_NUMBER]
SELECT *
FROM FAMILY
WHERE $PARTITION.PF_AGE(BIRTHYEAR)=1;
SELECT *
FROM FAMILY
WHERE $PARTITION.PF_AGE(BIRTHYEAR)=2;
SELECT *
FROM FAMILY
WHERE $PARTITION.PF_AGE(BIRTHYEAR)=3;
--partition function information
select * from sys.partition_functions;
select * from sys.partition_range_values;
--new partition create
--partition scheme 수정
alter partition scheme pf_age
next used indexgroup --새로운 분기점이 생기면 index파일그룹에 저장한다.
go
--partition function split (분할) ,서버 다운된다
alter partition function pf_age() --정의 되어있는 function이라 데이타 타입을 안쓴다.
split range(1965) --left,right는 이미 결정 되어져 있다.
go
select * from sys.partition_functions;
select * from sys.partition_range_values;
--partition function merge 결합
alter partition function pf_age()
merge range(1965) -- 위에서 나눈 1965 범위를 합친다.
select * from sys.partition_functions;
select * from sys.partition_range_values;
--일반 테이블로 이동
-- 1.파티션 테이블에 저장된 데이터의 구조와 일반 테이블의 구조
-- 는 일치해야 한다.
-- 2.일반 테이블의 index구조는 파티션 테이블의 index구조와 일치
-- 해야 한다. index에 의해 참조해서 집어 넣기때문에
-- 3.일반 테이블의 index file의 위치는 이동할 partition과 같은
-- 파일 그룹에 존재해야 한다.
sp_tables 'sister';
ALTER TABLE SISTER
ALTER COLUMN ADDR NVARCHAR(50);
특정 파티션에 저장된 데이터를 다른 테이블로 이동
: ALTER TABLE [TABLE_NAME]
SWITCH PARTITION [PARTITION_NUMBER]
TO [DESTINATION_TABLE]
-- 특정 파티션의 데이터를 다른 테이블로 이동 2,3번 조건 충족되지 않음
ALTER TABLE FAMILY
SWITCH PARTITION 2 TO SISTER;
create table sister
(
id varchar(12) not null,
name varchar(10) not null,
birthyear int,
addr nvarchar(50)
)
go
-- partition table과 돌일한 인덱스 구조의 인덱스를 이동할 파티션의
-- 파일 그룹에 생성
'연구개발 > SQL2005' 카테고리의 다른 글
[MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정 (0) | 2011.07.18 |
---|---|
프로시져 연습 (0) | 2010.06.18 |
Open Query (0) | 2010.06.18 |
Linked Server (0) | 2010.06.18 |
INDEX 2 (0) | 2010.06.18 |