테이블 파티셔닝,. (by niceboyous) [2014.03.11]
테이블 파티셔닝
파티셔닝 (Partitioning)은 태이블과 인텍스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것을 말한다.
테이블을 파티셔닝하면 하나의 테이블일지라도 파티션 거에 따라 물리적으로는 별도의 세그먼트에 데이터가 저장되며 인텍스도 마찬가지다.
● 파티셔닝이 필요한 이유
① 관리적 측면 : 파티션 단위 백업，추가，삭제 ，변경
② 성능적 측면 : 파티션 단위 조회 및 DML수행
h3.(1) 파티셔닝 기본 구조
수동 파티셔닝
파티셔닝 기능은 8버전부터 지원이 되었으며 그전 까지는 View로 직접 만들어서 관리를 하였다 이를 '수동파티셔닝'이라고 한다.
-- 파티션 뷰를 정의할 때 사용할 Base 테이블을 만든다.
SQL> create table p1 as select * from emp where deptno = 10;
테이블이 생성되었습니다.
SQL> create table p2 as select * from emp where deptno = 20;
테이블이 생성되었습니다.
SQL> create table p3 as select * from emp where deptno = 30;
테이블이 생성되었습니다.
-- 체크 제약을 반드시 설정해야 함
SQL> alter table p1 add constraint c_deptno_10 check(deptno < 20);
테이블이 변경되었습니다.
SQL> alter table p2 add constraint c_deptno_20 check(deptno >= 20 and deptno < 30);
테이블이 변경되었습니다.
SQL> alter table p3 add constraint c_deptno_30 check(deptno >= 30 and deptno < 40);
테이블이 변경되었습니다.
SQL> create index p1_empno_idx on p1(empno);
인덱스가 생성되었습니다.
SQL> create index p2_empno_idx on p2(empno);
인덱스가 생성되었습니다.
SQL> create index p3_empno_idx on p3(empno);
인덱스가 생성되었습니다.
SQL> analyze table p1 compute statistics;
테이블이 분석되었습니다.
SQL> analyze table p2 compute statistics;
테이블이 분석되었습니다.
SQL> analyze table p3 compute statistics;
테이블이 분석되었습니다.
-- 파티션 뷰를 정의한다.
SQL> create or replace view partition_view
2 as
3 select * from p1
4 union all
5 select * from p2
6 union all
7 select * from p3 ;
뷰가 생성되었습니다.
SQL> explain plan for
2 select * from partition_view
3 where deptno = :deptno ;
해석되었습니다.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1236824284
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 |
| 1 | VIEW | PARTITION_VIEW | 5 | 170 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | P1 | 3 | 93 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | P2 | 5 | 155 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | P3 | 6 | 204 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=TO_NUMBER(:DEPTNO))
4 - filter("DEPTNO"=TO_NUMBER(:DEPTNO))
5 - filter("DEPTNO"=TO_NUMBER(:DEPTNO))
19 개의 행이 선택되었습니다.
SQL> explain plan for
2 select * from partition_view
3 where deptno = 10;
해석되었습니다.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 180990235
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 |
| 1 | VIEW | PARTITION_VIEW | 5 | 170 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | P1 | 3 | 93 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| P2 | 1 | 31 | 3 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| P3 | 1 | 34 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
4 - filter(NULL IS NOT NULL)
5 - filter("DEPTNO"=10)
6 - filter(NULL IS NOT NULL)
7 - filter("DEPTNO"=10)
23 개의 행이 선택되었습니다.
파티션 테이블
SQL> create table partition_table
2 partition by range(deptno) --파티션 뷰의 Base 태이블에 체크 제약을 설정히는 것과 같은 역할
3 ( partition p1 values less than(20) --세개의 세그먼트가 생성되므로 앞에서 파티션 뷰를 정의할 때와 구조적으로 같다.
4 , partition p2 values less than(30)
5 , partition p3 values less than(40)
6 )
7 as
8 select * from emp ;
테이블이 생성되었습니다.
SQL> create index ptable_empno_idx on partition_table(empno) LOCAL; --Local옵션으로 파티션 뷰 Base 테이블에 각각 인덱스를
인덱스가 생성되었습니다. 만든 것과 같은 모습이다.
h3.(2)Range 파티셔닝
- 오라클 8 버전부터 제공된 가장 기초적인 파티셔닝 방식으로서 주로 날짜 컬럼을 기준으로 한다.
-- Range 파티셔닝
create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) )
partition by range(주문일자) (
partition p2009_q1 values less than('20090401')
, partition p2009_q2 values less than('20090701')
, partition p2009_q3 values less than('20091001')
, partition p2009_q4 values less than('20100101')
, partition p2010_q1 values less than('20100401')
, partition p9999_mx values less than( MAXVALUE )
);
h3.(3)Hash 파티셔닝
① 파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식이며，주로 고객 ID처럼
변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준 컬럼으로 선정해야 효과적이다.
② 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다.
③ 테이블 파티셔닝 여부를 결정할 때는 데이터가 얼마나 고르게 분산될 수 있느냐가 가장 중요한 관심사항이다.
(특히,해시 파티셔닝은 파티션 개수만 사용자가 결정하고 데이터를 분산시키는 해싱 알고리즘은 오라클에 의해 결정되기 때문)
④ 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션 개수를 2의 제곱，2， 4, 8, 16 등) 으로 설정할 것을 권고
SQL> create table t_hash1
2 (c1 number, c2 varchar2(10))
3 partition by hash (c1)
4 (partition p1 tablespace users,
5 partition p2 tablespace users);
테이블이 생성되었습니다.
- 병렬 쿼리 성능 향상
데이터가 모든파티션에 고르게 분산돼 있다면 특히 각 파티션이 서로 다른 디바이스에 저장돼 있다면 병렬 I/O 성능을 극대화할 수 있다.
반대로 말하면 데이터가 고르게 분산되지 않을 때는 병렬 쿼리 효과가반감된다.
- DML 경합분산
- 대용량 테이블이나 인텍스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 시용
- 데이터가 입력되는 테이블 블록에도 경합이 발생할 수 있지만 그보다는 입력할 블록을 할당받기 위한 Freelist 조회 때문에
세그먼트 헤더 블록에 대한 경합이 더 자주 발생한다 그럴 때 테이블을 해시 파티셔닝하면 세그먼트 헤더 블록에 대한 경합을 줄일 수 있다. - Right Growing 인텍스(예를 들어， 순차적으로 증가of는 일련번호 컬럼에 인댁스를 생성랜 경우)도 자주 경합 지점이 되곤하는데,
맨우측끝블록에만값이 입력되는특정 때문이다. 이때도 인덱스를 해시 파티셔닝함으로써 경합 발생 가능성을 낮출수 있다.
h3.(4) List 파티셔닝
① 오라클 9i 버전부터 제공되기 시작
② 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를분할 저장하는 방식
③ Range 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만， 리스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록으로써 결정
④해시 파티션과 비교하면 해시 파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할하는 구조인 반면 리스트 파티션은 사용자가
정의한논리적인 그룹에 따라분할
⑤ Range, 해시 파티셔닝과 달리 리스트 파티셔닝에는 단일 컬럼으로만 파티션 키를 지정할 수 있다. 그리고 Range 파티션에 maxvalue 파티션을
반드시 생성하라고 권고한 것과 같은 이유로，리스트 파티션에도 default 파티션을 생성해 두어야 안전하다
create table 인터넷매물( 물건코드varchar2(5), 지역?류varchar2(4) )
partition by list(지역?류)
(partition p_지역1 values ('서울'),
partition p_지역2 values ('경기', '인천'),
partition p_지역3 values ('부산', '대구', '대전', '광주'),
partition p_기타values (DEFAULT) ) ;
h3.(4) 결합 파티셔닝
- 결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고 서브 파티션 단위로 데이터를 저장한다.
- 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치(세그먼트)를 결정한다.
h5.Range + 해시 결합 파티셔닝
create table 주문( 주문번호number, 주문일자varchar2(8), 고객id varchar2(5) )
partition by range(주문일자)
subpartitionby hash(고객id) subpartitions8
( partition p2009_q1 values less than('20090401'),
partition p2009_q2 values less than('20090701'),
partition p2009_q3 values less than('20091001'),
partition p2009_q4 values less than('20100101'),
partition p2010_q1 values less than('20100401'),
partition p9999_mx values less than( MAXVALUE ));
h5.Range + 리스트 결합 파티셔닝
create table 판매 ( 판매점 varchar2(10), 판매일자 varchar2(8) )
partition by range(판매일자)
subpartition by list(판매점)
subpartition template
( subpartition lst_01 values ('강남지점', '강북지점', '강서지점', '강동지점')
, subpartition lst_02 values ('부산지점', '대전지점')
, subpartition lst_03 values ('인천지점', '제주지점', '의정부지점')
, subpartition lst_99 values ( DEFAULT ) )
( partition p2009_q1 values less than('20090401')
, partition p2009_q2 values less than('20090701')
, partition p2009_q3 values less than('20091001')
, partition p2009_q4 values less than('20100101') );
h5.기타 결합 파티셔닝
11g부터 다양한 결합 파티셔닝이 아래와 같이 가능해 졌다.
① Range-Range
② 리스트-해시
③ 리스트-리스트
④ 리스트-Range
h5.11g에 추가된 파티션 유형
① Reference 파티션
부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는기능
Create table 상품 {
상품번호 numberNOT NULL PRIMARY KEY
, 상품명 varchar2(50) not null
, 현재가격 number not null
, 상품대분류 varchar2(4) not null
, 등록일시 date not null
)
Partition by list(상품대분류) (
Partition p1 values ('의류')
,partition p2 values ('식품')
,partition p3 values ('가전')
,partition 4 values ('컴퓨터')
);
create table 일별상품거래 (
상품번호 number NOT NULL
, 거래일자 varchar2(8)
, 판매가격 number
, 판매수량 number
, 판매금액 number
, constraint 일별상품거래_fk foreign key(상품번호) references 상품
)
partition by reference (일별상품거래_fk);
② Interval 파티셔닝
11g 부터는 Range 파티션을 생성할 대 아래와 같이 interval, 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
create table 주문일자 (주문번호 number, 주문일시 date, ... )
partition by range(주문일시) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
, partition p200907 values less than(to_date('2009/08/01', 'yyyy/mm/dd'))
, partition p200908 values less than(to_date('2009/09/01', 'yyyy/mm/dd'))
, partition p200909 values less than(to_date('2009/10/01', 'yyyy/mm/dd'))
, partition p200910 values less than(to_date('2009/11/01', 'yyyy/mm/dd'))
);
create table 고객 (고객번호 number, 고객명 varchar2(20), ... )
partition by range(고객번호) INTERVAL (100000)
( partition p_cust1 values less than ( 100001 )
, partition p_cust2 values less than ( 200001 )
, partition p_cust3 values less than ( 300001 )
) ;