메뉴 건너뛰기

bysql.net

1. 테이블 파티셔닝

2011.06.21 14:12

suspace 조회 수:6873

1. 테이블 파티셔닝

 

-       파티셔닝이 필요한 이유

▶  관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경

▶  성능적 측면 : 파티션 단위 조회 및 DML 수행

 

(1)   파티션 기본 구조

수동 파티셔닝 (7.3버전에서 파티션 뷰를 통해 파티션 기능 구현)
- 파티션 뷰 : 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽는다 (파티션 Pruning)

 

-- 파티션 뷰를 정의할 때 사용할 Base 테이블을 만든다.

create table p1 as select * from emp where deptno = 10;

create table p2 as select * from emp where deptno = 20;

create table p3 as select * from emp where deptno = 30;

 

-- 체크 제약을 반드시 설정해야 함

alter table p1 add constraint c_deptno_10 check(deptno < 20);

alter table p2 add constraint c_deptno_20 check(deptno >= 20 and deptno < 30);

alter table p3 add constraint c_deptno_30 check(deptno >= 30 and deptno < 40);

 

create index p1_empno_idx on p1(empno);

create index p2_empno_idx on p2(empno);

create index p3_empno_idx on p3(empno);

 

analyze table p1 compute statistics;

analyze table p2 compute statistics;

analyze table p3 compute statistics;

 

-- 파티션 뷰를 정의한다.

create or replace view partition_view

as

select * from p1

union all

select * from p2

union all

             select * from p3 ;


▶ :deptno  변수에 10을 입력하면 p1 테이블만 읽는다.

SQL> select * from partition_view

      where  deptno = :deptno ;

 

Execution Plan

----------------------------------------------------------------------------

0        SELECT STATEMENT  Optimizer=CHOOSE
1    0     VIEW OF ‘ PARTITION_VIEW ‘

2    1        UNION-ALL (PARTITION)

3    2         FILTER   → :deptno < 20

4    3            TABLE ACCESS (FULL) OF ‘P1’

5    4         FILTER   → 20 <= :deptno < 30

6    5            TABLE ACCESS (FULL) OF ‘P2’

7    6         FILTER   → 30 <= :deptno < 40

8    7            TABLE ACCESS (FULL) OF ‘P3’


* 10g나 11g 에서는 바인드 변수를 사용할 때 파티션 Pruning이 제대로 작동하지 않음
(9i에서 기본 값이 false지만 partition_view_enabled 파라미터가
10g에서 hidden 파라미터 _partition_view_enabled로 바뀐 것과 무관하지 않아 보임)


▶ :deptno 에 10을, :empno 에 7839를 입력하면, p1 테이블의 p1_empno_idx 인덱스를
이용해 테이블 액세스 (체크 제약을 설정하였기 때문에 파티션 Pruning 이 가능)

 SQL> select * from partition_view

       where  deptno = :deptno

       and    empno = :empno ;

 

Execution Plan

----------------------------------------------------------------------------

0        SELECT STATEMENT  Optimizer=CHOOSE
1    0     VIEW OF ‘ PARTITION_VIEW ‘

2    1        UNION-ALL (PARTITION)

3    2         FILTER

4    3            TABLE ACCESS (BY INDEX ROWID) OF ‘P1’

5    4               INDEX (RANGE SCAN) OF ‘P1_EMPNO_IDX’

5    4         FILTER

6    5            TABLE ACCESS (FULL) OF ‘P2’

5    4               INDEX (RANGE SCAN) OF ‘P2_EMPNO_IDX’

7    6         FILTER

8    7            TABLE ACCESS (FULL) OF ‘P3’

5    4               INDEX (RANGE SCAN) OF ‘P3_EMPNO_IDX’

 


파티션 테이블 (8 이상) ? 몇 가지 옵션 지정으로 간편하게 파티션 정의

- 파티셔닝은, 내부에 몇 개의 세그먼트를 생성하고 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것

create table partition_table

partition by range(deptno) (

 partition p1 values less than(20)

, partition p2 values less than(30)

, partition p3 values less than(40)

) as

select * from emp ;

 

create index ptable_empno_idx on partition_table(empno) LOCAL;

▶ partition by 절 : 파티션 뷰의 base 테이블에 체크 제약을 설정과 같은 역할

▶ local 옵션 : 각 파티션별로 개별적인 인덱스가 만들어져, 파티션 뷰 base 테이블에 각각 인덱스 만든 것과 동일 

 

- 내부 물리적 구조는 파티션 뷰와 별반 다르지 않음

- 테이블 파티션 유형

* Range 파티셔닝 (8 이상)

* 해시 파티셔닝 (8i 이상)

* 리스트 파티셔닝 (9i 이상)

* Range-해시 파티셔닝 (8i 이상)

* Range-리스트 파티셔닝 (9i 이상)

* Range-Range 파티셔닝 (11g 이상)

* 리스트-해시 파티셔닝 (11g 이상)

* 리스트-리스트 파티셔닝 (11g 이상)

* 리스트-Range 파티셔닝 (11g 이상)

 

파티션 유형

단일 파티셔닝

결합 파티셔닝

해시

리스트

Range

Range

8 이상

8i 이상

9i 이상

11g 이상

해시

8i 이상

x

x

x

리스트

9i 이상

11g 이상

11g 이상

11g 이상

 

(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 )

             );


- 파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장

(검색 조건을 만족하는 파티션만 읽어 이력성 데이터 조회 시 성능 향상)
- 파티션 키 : 하나 이상의 컬럼 지정 (최대 16개까지 허용)
- 데이터 관리 작업(백업, 삭제 등)을 효율적이고 빠르게 수행

- maxvalue 파티션을 반드시 생성해 둔다.

- 11g 이상은 Range 파티션을 생성할 때 interval 기준을 정의함으로써 파티션 자동 추가 가능

 

(3)   해시 파티셔닝 (8i 이상)
- 파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식.
- 변별력 좋고, 데이터 분포가 고른 컬럼을 기준으로 선정해야 효과적

-- 고객id 기준으로 해시 파티셔닝

create table 고객( 고객id varchar2(5), 고객명 varchar2(10) )

partition by hash(고객id) partitions 4 ;


- 검색할 때 조건절 비교 값에 해시 함수를 적용해 읽어야 할 파티션을 결정(해시 알고리즘 특성상 등치(=)조건 또는 IN-List 조건으로 검색할 때만 파티션 pruning 작동)

- 테이블 파티셔닝 여부 결정 : 데이터가 얼마나 고르게 분산될 수 있느냐가 중요.
(파티션 개수만 사용자가 결정하고 데이터를 분산시키는 해싱 알고리즘은 오라클에 의해 결정되기 때문에 파티션 키를 잘못 선정하면 데이터가 고르게 분산되지 않아 파티셔닝의 이점이 사라질 수도 있다.)

- 파티션 개수를 2의 제곱으로 설정 : 특정 파티션에 데이터가 몰리지 않는다.
(그래도 분산되지 않을 경우, 리스트 파티션을 이용해 파티션 기준을 사용자가 수동으로 결정)

병렬 쿼리 성능 향상
- 데이터가 고르게 분산돼 있고 각 파티션이 서로 다른 디바이스에 저장돼 있다면 성능 극대화, 그렇지 않을 때 병렬 쿼리 효과 반감

DML 경합 분산
- 동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로 사용.

둘 모두 트랜젝션이 많이 발생하는 대용량 거래 테이블일 때 효과 (단일 해시 파티셔닝보다 Range-해시 파티셔닝을 주로 사용하게 되는 이유)

(4)   리스트 파티셔닝 (9i 이상)
- 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식.

-- 지역분류 기준으로 인터넷매물 테이블을 리스트 파티셔닝

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) 

             ) ;


- 순서와 상관없이 불연속적인 값의 목록으로써 저장할 파티션이 결정 (Range 파티션은 값의 순서에 따라 결정)
- 사용자가 정의한 논리적인 그룹에 따라 분할 (해시 파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할)
- 파티션 키 : 단일 컬럼으로만 지정

- Default 파티션을 생성해두어야 안전.

(5)   결합 파티셔닝
- 서브 파티션마다 세그먼트를 하나씩 할당, 서브 파티션 단위로 데이터를 저장. (주 파티션 키에 따라 1차적으로 데이터를 분배, 서브 파티션 키에 따라 저장할 위치(세그먼트)를 결정)

Range + 해시 결합 파티셔닝

-- 주문일자 기준으로 주문 테이블을 분기별 Range 파티셔닝, 그 안에서 다시 고객 ID 기준으로 해시 파티셔닝

create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) )

partition by range(주문일자)

subpartition by hash(고객id) subpartitions 8

( 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 )

             );

 

** select * from 주문 where 주문일자 between ‘20090701’ and ‘20090930’
   ▶ Range 파티션 p2009_q3에 속한 8개 서브 파티션을 탐색

 

** select * from 주문 where 고객id = :custid
   ▶ 각 Range 파티션당 하나씩 총 6개 서브 파티션을 탐색

 


=> Range 파티셔닝과 해시 파티셔닝의 장점 모두 적용

Range + 리스트 결합 파티셔닝
- 초대형 이력성 테이블을 Range 파티셔닝하고, 각 파티션을 업무적으로 다시 분할하고자 할 때 주로 사용
- 예1) 상품테이블은 상품대분류 기준으로 리스트 파티셔닝하고, 주문과 주문상세 테이블 등은 주문일자와 상품대분류 기준으로 Range + 리스트 형태로 파티셔닝
- 예2) 전국 초중고 학생의 학사 및 수험정보를 관리하는 시스템.
학생정보는 시도별로 리스트 파티셔닝하고, 수험정보 같은 기간별 데이터는 수험일시와 시도 기준으로 Range + 리스트 형태의 결합 파티셔닝

기타 결합 파티셔닝
- Range-Range (11g 이상)

- 리스트-해시 (11g 이상)

- 리스트-리스트 (11g 이상)

- 리스트-Range (11g 이상)

(6)   11g에 추가된 파티션 유형

Reference 파티셔닝
- 10g : 상품에 있는 상품대분류 컬럼을 일별상품거래 테이블에 반정규화
- 11g : 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능 도입 (Reference 파티션)
- Reference 파티셔닝 기능을 사용하려면 일별상품거래 테이블의 상품번호 컬럼에 not null 과 FK제약이 설정돼 있어야 한다.

 

create table 상품 (

  상품번호   number       NOT 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 p4 values ('컴퓨터')

);

 

create table 일별상품거래 (

  상품번호   number  NOT NULL

, 거래일자   varchar2(8)

, 판매가격   number

, 판매수량   number

, 판매금액   number

, constraint 일별상품거래_fk foreign key(상품번호) references 상품

)

           partition by reference (일별상품거래_fk);


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'))

);

-- 고객 수가 10만 명을 넘을 때마다 고객 테이블에 파티션 추가
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 )

             ) ;

 

 



  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 이주영 (suspace)
  • 최초작성일: 2011년 06월 21 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^


이 게시물을
목록
목록
쓰기
태그