Partition Table

특징

1) 어떤 하나의 테이블이 특정 컬럼의 value 값에 따라 서른 다른 물리적인 파일로 저장.

2) 대용량 데이터베이스화 하기 위한 필수 기술
3) MS-SQL2005 부터 지원
4) 읽고 쓸 때 TRAFFIC의 OVERHEAD를 막을 수 있다.
5) PARTITON TABLE은 생성시에만 별도의 작업이 필요할 뿐,   실제 데이터의 추가, 삭제, 조회는 기

   존의 방법과 동일하게 이루어진다
 

실행계획
STEP 1	HARDWARE적인 설계 및 기획
STEP 2	테이블을 분리하는데 기준이 되는 PARTITION FUNCTION 생성 범위 설정 시 LEFT, RIGHT 지시어에 대한 사용법 숙지
STEP 3	PARTITION SCHEMA 생성  실제 저장되어질 파일 그룹 설정 그룹 지정시 PRIMARY는 반드시 []로 묶어준다
STEP 4	논리적으로 접근할 TABLE 생성 PARTITION SCHEMA를 사용

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과 돌일한 인덱스 구조의 인덱스를 이동할 파티션의
-- 파일 그룹에 생성