메뉴 건너뛰기

bysql.net

제4절_파티션_활용

2012.06.26 08:16

오예스 조회 수:5753

1. 파티션 개요

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것을 말한다. 테이블을 파티셔닝하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장하며, 인덱스도 마찬가지다. 파티셔닝이 필요한 이유를 관리적 측면과 성능적 측면으로 나누어 볼 수 있다.

  • 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경
  • 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산

 파티셔닝은 우선 관리적 측면에서 많은 이점을 제공한다.

 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 일은 데이터베이스 관리자들의 일상적인 작업인데, 만약 파티션 없이 대용량 테이블에 이런 작업들을 수행하려면 시간도 오래 걸리고 비효율적이다.

 대용량 테이블에 인덱스를 새로 생성하거나 재생성할 때도 파티션 기능을 이용하면 효과적이다. 성능적 측면의 효용성도 매우 높다.

 데이터를 빠르게 검색할 목적으로 데이터베이스마다 다양한 저장구조와 검색 기법들이 개발되고 있지만, 인덱스를 이용하는 방법과 테이블 전체를 스캔하는 두 가지 방법에서 크게 벗어나지는 못하고 있다.

 인덱스를 이용한 Random 액세스 방식은 일정량을 넘는 순간 Full Table Scan보다 오히려 성능이 나쁘다.

 그렇다고 초대용량 테이블을 Full Scan 하는 것은 매우 비효율적이다.

 이런 경우 테이블을 파티션 단위로 나누어 관리하면, Full Table Scan이라 하더라도 일부 세그먼트만 읽고 작업을 마칠 수 있다. 테이블이나 인덱스를 파티셔닝하면 DBMS는 내부적으로 2개 이상(생성 초기에 하나일 수는 있으나 계속 하나를 유지한다면 파티셔닝은 불필요)의 저장영역을 생성하고, 그것들이 논리적으로 하나의 오브젝트임을 메타정보로 관리한다.

  파티션되지 않은 일반 테이블일 때는 테이블과 저장영역(Oracle의 세그먼트)이 1:1 관계지만 파티션 테이블일 때는 1:M 관계다. 인덱스를 파티셔닝할 때도 마찬가지다.

2. 파티션 유형

Oracle이 지원하는 파티션 유형은 다음과 같다.

1) Range 파티셔닝

  • 파티션 키 값의 범위(Range)로 분할
  • 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 칼럼을 기준으로 함예) 판매 데이터를 월별로 분할

2) Hash 파티셔닝

  • 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
  • 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리→ 각 로우의 저장 위치 예측 불가
  • 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적예) 고객번호, 주문일련번호 등
  • 병렬처리 시 성능효과 극대화
  • DML 경합 분산에 효과적

3) List 파티셔닝

  • 불연속적인 값의 목록을 각 파티션에 지정
  • 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장예) 판매 데이터를 지역별로 분할

4) Composite 파티셔닝

  • Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성예) Range + List 또는 List + Hash 등
  • Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점

Oracle 버전별 파티션 지원 유형을 요약하면 [표 Ⅲ-5-2]와 같다.

SQL Server는 2005 버전부터 파티셔닝을 지원하기 시작했고, 현재 2008 버전까지는 Range 단일 파티션만 지원하고 있다.

Oracle에서 Range 파티셔닝하는 방법([그림 Ⅲ-5-9] 참조)을 간단히 예시하면 다음과 같다.

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 ) → 주문일자 >= '20100401'
) ;

Oracle에서 [Range + Hash]로 파티셔닝하는 방법([그림 Ⅲ-5-10] 참조)을 간단히 예시하면 다음과 같다.

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

 

3. 파티션 Pruning

파티션 Pruning은 옵티마이저가 SQL의 대상 테이블과 조건절을 분석하여 불필요한 파티션을 액세스 대상에서 제외하는 기능을 말한다. 이를 통해 액세스 조건과 관련된 파티션에서만 작업을 수행할 수 있게 된다. 파티션 테이블에 조회나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리가 바로 파티션 Pruning에 있다. 기본 파티션 Pruning에는 정적 Pruning과 동적 Pruning이 있고, DBMS별로 서브쿼리 Pruning, 조인 필터(또는 블룸 필터) Pruning 같은 고급 Pruning 기법을 사용한다. 여기서는 기본 파티션 Pruning에 대해서만 살펴보기로 하자.

가. 정적(Static) 파티션 Pruning

액세스할 파티션을 컴파일 시점(Compile-Time)에 미리 결정하며, 파티션 키 칼럼을 상수 조건으로 조회하는 경우에 작동한다.

select *
 from sales_range
where sales_date >= '20060301'
    and sales_date <= '20060401'
 -----------------------------------------------------
| Id | Operation | Name | Pstart | Pstop |
-----------------------------------------------------
 | 0 | SELECT STATEMENT | | | |
 | 1 | PARTITION RANGE ITERATOR | | 3 | 4 |
 |* 2 | TABLE ACCESS FULL | SALES_RANGE | 3 | 4 |
 -----------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
2 - filter("SALES_DATE">='20060301' AND "SALES_DATE"<='20060401')

나. 동적(Dynamic) 파티션 Pruning

액세스할 파티션을 실행 시점(Run-Time)에 결정하며, 파티션 키 칼럼을 바인드 변수로 조회하는 경우가 대표적이다. NL Join할 때도 Inner 테이블이 조인 칼럼 기준으로 파티셔닝 돼 있으면 동적 Pruning이 작동한다.

select *
 from sales_range
where sales_date >= :a and sales_date <= :b
 --------------------------------------------------------
| Id | Operation | Name | Pstart | Pstop |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
|* 1 | FILTER | | | |
| 2 | PARTITION RANGE ITERATOR | | KEY | KEY |
|* 3 | TABLE ACCESS FULL | SALES_RANGE | KEY | KEY |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:A<=:B)
3 - filter("SALES_DATE">=:A AND "SALES_DATE"<=:B)

파티션 Pruning은 SQL에 사용된 조건절과 파티션 구성을 분석해 DBMS가 지능적으로 수행하는 메커니즘이므로 사용자가 굳이 신경 쓰지 않아도 된다.

다만, 파티션 키 칼럼에 대한 가공이 발생하지 않도록 주의해야 한다.

사용자가 명시적으로 파티션 키 칼럼을 가공했을 때는 물론, 데이터 타입이 묵시적으로 변환될 때도 정상적인 Pruning이 불가능해지기 때문이다. 인덱스 칼럼을 조건절에서 가공하면 해당 인덱스를 사용할 수 없게 되는 것과 같은 이치다.

4. 인덱스 파티셔닝

지금까지 테이블 파티션 위주로만 설명했는데, 테이블 파티션과 인덱스 파티션은 구분할 줄 알아야 한다. 인덱스 파티션은 테이블 파티션과 맞물려 다양한 구성이 존재한다.

가. Local 파티션 인덱스 vs. Global 파티션 인덱스
  • Local 파티션 인덱스 : 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스([그림 Ⅲ-5-11] 참조). 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해 줌.

  • Global 파티션 인덱스 : 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스([그림 Ⅲ-5-12] 참조).

나. Prefixed 파티션 인덱스 vs. NonPrefixed 파티션 인덱스

인덱스 파티션 키 칼럼이 인덱스 구성상 왼쪽 선두 칼럼에 위치하는지에 따른 구분이다.

  • Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두는 것을 말한다.
  • Nonprefixed : 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두지 않는 것을 말한다. 파티션 키가 인덱스 칼럼에 아예 속하지 않을 때도 여기에 속한다.

Local과 Global, Prefixed와 Nonprefixed를 조합하면 아래 4가지 구성이 나온다.

비파티션까지 포함에 인덱스를 총 5가지 유형으로 구분할 수 있다.

  • Local Prefixed 파티션 인덱스
  • Local NonPrefixed 파티션 인덱스
  • Global Prefixed 파티션 인덱스
  • Global NonPrefixed 파티션 인덱스 (→ Oracle Not Support)
  • 비파티션(NonPartitioned) 인덱스

Oracle은 이 중에서 Global NonPrefixed 파티션 인덱스를 허용하지 않는다.

다. 인덱스 파티셔닝 가이드

인덱스 파티션은 파티션 테이블과 마찬가지로 성능, 관리 편의성, 가용성, 확장성 등을 제공한다. 테이블에 종속적인 Local 파티션, 테이블과 독립적인 Global 파티션 모두 가능하지만, 관리적인 측면에서는 Local 인덱스가 훨씬 유용하다. 테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문이다. 인덱스를 다시 사용할 수 있게 하려면 인덱스를 Rebuild 하거나 재생성해 주어야 한다. 성능 측면에서는 [표 Ⅲ-5-3]과 같이 다양한 적용기준을 고려해야 하므로 잘 숙지하기 바란다.

번호 제목 글쓴이 날짜 조회 수
58 Week1_박우창 [1] balto 2012.07.17 9892
57 Week1_이주영 suspace 2012.07.17 5025
56 Week1_승대수 보라빛고양이 2012.07.17 3053
55 Week1_이진우 [5] ljw 2012.07.16 46262
54 Week1_위충환 [1] 실천하자 2012.07.16 9272
53 문제작성 실천하자 2012.07.16 5794
52 제5절_배치_프로그램_튜닝 보라빛고양이 2012.07.04 6906
» 제4절_파티션_활용 오예스 2012.06.26 5753
50 제3절_DML_튜닝 balto 2012.06.13 9073
49 제1절_고급_SQL_활용 실천하자 2012.06.11 6285
48 제1절 고급 SQL 활용 실천하자 2012.06.11 8397
47 제4절_고급_조인_기법 suspace 2012.06.05 31231
46 제2절_소트_튜닝 file ljw 2012.06.04 12024
45 제3절 조인 기본 원리 보라빛고양이 2012.05.30 6980
44 제1절_인덱스_기본_원리 오예스 2012.05.22 7100
43 제2절_인덱스_튜닝 balto 2012.05.18 9904
42 제2절_쿼리변환 ljw 2012.05.14 5613
41 제1절_옵티마이저 실천하자 2012.05.12 7185
40 제3절_동시성_제어 운영자 2012.05.08 6036
39 제1절_Lock balto 2012.05.05 25258