메뉴 건너뛰기

bysql.net

1. 테이블 파티셔닝

2011.06.21 23:12

suspace 조회 수:6810

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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^


번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.16 149417
59 3. 인덱스 파티셔닝 darkbeom 2011.06.20 53807
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.19 33798
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31051
56 2. 파티션 Pruning 실천하자 2011.06.22 26014
55 3. 뷰 Merging 실천하자 2011.05.15 23377
54 3. 해시 조인 file darkbeom 2011.03.21 21526
53 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19692
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 18084
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.09 16878
50 7. Sort Area 크기 조정 실천하자 2011.06.14 15059
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.24 14672
48 4. 조인 순서의 중요성 운영자 2011.03.28 14232
47 1. 인덱스 구조 [1] file 실천하자 2011.02.16 14184
46 1. 기본 개념 멋진넘 2011.06.28 13385
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.04 13260
44 9. 비트맵 인덱스 file 실천하자 2011.03.06 12335
43 1. 옵티마이저 file 실천하자 2011.04.18 11204
42 6. 히스토그램 실천하자 2011.04.25 10911
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.14 8852