DataBaser.Net: 파티션

1 파티션 관리

1.1 적재
1.2 삭제
1.3 소스

2 meta data

3 제한사항

4 참고자료

5 파티션 주의사항

[edit]

1 파티션 관리 #

실전 SQL SEVER MVP 53 책 참고..

[edit]

1.1 적재 #

동일한 데이터베이스의 임시 테이블에 데이터를 적재
SPLIT명령으로 가장 끝에 새로운 빈 파티션 생성
SWITCH명령으로 임시테이블과 파티션 전환

[edit]

1.2 삭제 #

동일한 데이터베이스에 빈 임시 테이블을 하나 생성
SWITCH명령을 이용하여 가장 오래된 파티션과 빈 테이블 교체
MERGE 명령을 이용하여 오랜된 파티션과 이웃하는 파티션을 병합

[edit]

1.3 소스 #

drop table p_test;
drop partition scheme ps_test;
drop partition function pf_test;
go

create partition function pf_test(date)
as range right for values
(
	'20101101'
,	'20101102'
,	'20101103'
);

create partition scheme ps_test 
as partition pf_test
--to ([primary],[primary],[primary],[primary]);
all to ([primary]);

create table p_test(dt date, id varchar(20))
on ps_test(dt);

insert p_test values('20101101', 'a');
insert p_test values('20101102', 'b');
insert p_test values('20101103', 'c');

drop index p_test.nix_id
create index nix_id on p_test(id,dt)--분할열 dt가 포함되어야만 함
on ps_test(dt)

select *, $partition.pf_test(dt) from p_test
go

/*
적재
1. 동일한 데이터베이스의 임시 테이블에 데이터를 적재
2. SPLIT명령으로 가장 끝에 새로운 빈 파티션 생성
3. SWITCH명령으로 임시테이블과 파티션 전환
*/

--1. 동일한 데이터베이스의 임시 테이블에 데이터를 적재
drop table dbo.p_switch;
create table dbo.p_switch(dt date, id varchar(20))
on [primary]

insert p_switch values('20101104', 'd');

drop index p_switch.nix_id
create index nix_id on p_switch(id, dt) --분할열 dt가 포함되어야만 함
--on ps_test(dt)

alter table dbo.p_switch add constraint ck1 
check(dt >= '20101104' and dt is not null);


--2. SPLIT명령으로 가장 끝에 새로운 빈 파티션 생성
alter partition scheme ps_test next used [primary];
alter partition function pf_test() split range('20101104');

--3. SWITCH명령으로 임시테이블과 파티션 전환
alter table p_switch switch to p_test partition 5

select * 
from sys.partition_functions a
	inner join sys.partition_range_values b
		on a.function_id = b.function_id
where name = 'pf_test'


/*
삭제
1. 돌일한 데이터베이스에 빈 임시 테이블을 하나 생성
2. SWITCH명령을 이용하여 가장 오래된 파티션과 빈 테이블 교체
3. MERGE 명령을 이용하여 오랜된 파티션과 이웃하는 파티션을 병합
*/

--1. 동일한 데이터베이스에 빈 임시 테이블을 하나 생성
drop table dbo.p_drop;
create table dbo.p_drop(dt date, id varchar(20));

--2. SWITCH명령을 이용하여 가장 오래된 파티션과 빈 테이블 교체
alter table p_test switch partition 2 to p_drop;

--3. MERGE 명령을 이용하여 오랜된 파티션과 이웃하는 파티션을 병합
alter partition function pf_test() merge range('20101102')


select * 
from sys.partition_functions a
	inner join sys.partition_range_values b
		on a.function_id = b.function_id
where name = 'pf_test'

[edit]

2 meta data #

http://blogs.solidq.com/rdyess/Post.aspx?ID=4&title=Table+Partitioning+and+Indexes+(Part+2)
http://blogs.solidq.com/rdyess/Post.aspx?ID=5&title=Table+Partitioning+and+Indexes+(Part+1)

--review the indexes just created
select 
    object_name(i.[object_id]) 'table name'
,   i.[name] as 'index name'
,   i.index_id as 'index id'
,   i.type_desc as 'index type'
,   ic.index_column_id as 'column'
,   c.[name] as 'column name'
,   ic.key_ordinal as 'key column'
,   ic.is_included_column as 'include column'
,   ic.partition_ordinal as 'partitioning column'--partitioning column added to index or part of key
from sys.indexes i
    inner join sys.index_columns ic
        on i.[object_id] = ic.[object_id]
       and i.index_id = ic.index_id
    inner join sys.columns c
        on ic.[object_id] = c.[object_id]
        and ic.column_id = c.column_id
order by i.name, i.index_id,ic.index_column_id


select 
    pf.[name]
,   pf.[type] --what type of partitioning function
,   pf.type_desc --what type of partitioning function
,   pf.fanout --how many partitions do we have
,   pf.boundary_value_on_right --1 for right, 0 for left
,   pf.create_date
,   pf.modify_date
,   rv.boundary_id
,   rv.[value]
,   t.[name]
,   s.[name]
,   pp.max_length
,   pp.[precision]
,   pp.scale
,   pp.collation_name
,   t.is_nullable
,   t.is_user_defined
,   t.is_assembly_type
from sys.partition_functions pf
    inner join sys.partition_parameters pp
        on pf.function_id = pp.function_id
    inner join sys.types t
        on pp.system_type_id = t.system_type_id
    inner join sys.schemas s
        on t.schema_id = s.schema_id
    inner join sys.partition_range_values rv
        on pp.function_id = rv.function_id
        and pp.parameter_id = rv.parameter_id
order by pf.[name], rv.boundary_id

[edit]

3 제한사항 #

2008 r2버전에서는 파티션을 1000 개까지만 지원한다.
2008 r2 sp1에서는 파티션을 15,000개까지 지원하도록 변경된다고 한다. 희소식이다. (2011-06-09) 자세한 내용은 여기를 클릭..

2008 r2 sp1 이상에서 파티션을 15,000개까지 사용하려면 다음의 sp를 실행해야 한다. 주의사항 [http]

여기를 보면 오타가 있다. 원래는 "sp_db_increased_partitions"인데 "sp_dp_increased_partitions"와 같이 되어 있다.

select @@version
/*
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
	Jun 17 2011 00:54:03 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
*/

exec sp_db_increased_partitions 
    @dbname='admin' 
,   @increased_partitions='true' --true/on || false/off

[edit]

4 참고자료 #

[edit]

5 파티션 주의사항 #

파티션된 테이블을 사용할 경우에는 약간의 주의를 요한다. SQL문을 작성할 때에 '파티션'을 항상 생각해야만 우리가 원하는 대로 옵티마이저를 움직이게 할 수 있다. 예를 들어, 다음과 같은 경우다.

select max(col_a) from partition_table_a

실제로 해보면 다음과 같다. (이전에 파티션을 만든 테이블을 약간 변경해서 사용하자.)

--연습할 테이블 만들기
drop table p_test;
drop partition scheme ps_test;
drop partition function pf_test;

create partition function pf_test(date)
as range right for values
(
	'20101101'
,	'20101102'
,	'20101103'
);

create partition scheme ps_test 
as partition pf_test
to ([primary],[primary],[primary],[primary]);

create table p_test(dt date, cola char(500))
on ps_test(dt);

insert p_test values('20101101', 'aa')
go 100
insert p_test values('20101102', 'aa')
go 100
insert p_test values('20101103', 'aa')
go 100

다음의 sql문을 실행해 보고 실행계획을 살펴보자

select max(dt) from p_test

Rows                 Executes             StmtText                                                                   
-------------------- -------------------- ---------------------------------------------------------------------------
1                    1                    select max(dt) from p_test                                                 
1                    1                      |--Stream Aggregate(DEFINE:([Expr1005]=MAX([work].[dbo].[p_test].[dt]))) 
300                  1                           |--Table Scan(OBJECT:([work].[dbo].[p_test]))

Rows컬럼을 보면 300건(풀스캔)을 읽고고서야 원하는 결과를 얻었다. 내가 의도한 바는 적어도 1건만 읽던지 3건만 읽어서 max(dt)값을 조회하는 것이었다. 그럼 어떻게 해결할까? 간단하다. metadata를 이용하는 방법이다. 아니면 partition의 경계을 저장한 dummy테이블을 이용하는 방법이다. 여기서는 metadata를 이용해보자.

select max(b.dt)
from sys.partitions a
	cross apply (
		select top 1 dt
		from p_test
		where $partition.pf_test(dt) = a.partition_number
	) b
where object_id = object_id('dbo.p_test')

Rows                 Executes             StmtText                                                                         
-------------------- -------------------- ---------------------------------------------------------------------------------
1                    1                      |--Stream Aggregate(DEFINE:([Expr1010]=MAX([work].[dbo].[p_test].[dt])))       
3                    1                           |--Nested Loops(Inner Join, OUTER REFERENCES:([rs].[numpart]))            
4                    1                                |--Clustered Index Scan(OBJECT:([work].[sys].[sysrowsets].[clust] AS 
3                    4                                |--Top(TOP EXPRESSION:((1)))                                         
3                    4                                     |--Table Scan(OBJECT:([work].[dbo].[p_test]), SEEK:([PtnId1006]=

sys.partitions에서 4건을 읽었고, p_test 테이블에서 3건만 읽은 것이 보일 것이다.

Contents

1 파티션 관리 #

1.1 적재 #

1.2 삭제 #

1.3 소스 #

2 meta data #

3 제한사항 #

4 참고자료 #

5 파티션 주의사항 #