4.파티션 활용 (by xsoft) [2013.11.21]
목차
1. 파티션 개요
2. 파티션 유형
3. 파티션 Pruning
4. 인덱스 파티셔닝
1. 파티션 개요
- 파티셔닝(Partitioning)은 테이블 또는 인텍스 데이터를 파티션 (Partition) 단위로 나누어 저장하는 것
- 테이블을 파티셔냥하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장하며, 인텍스도 마찬가지임
h3, 가) 파티션 목적
| 관리적 측면 | 파티션 단위 백업, 추가, 삭제, 변경 |
| 성능적 측면 | 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산 |
2. 파티션 유형
아래는 Oracle이 지원하는 파티션 유형
가) Range 파티셔닝
- 파티션 커 값의 범위 (Range)로 분할
- 파티셔닝의 가장 일반적인 형태이며 주로 날짜 칼럼을 기준으로 함예) 판매 데이터를 월별로 분할
나) Hash 파티셔닝
- 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
- 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리
- 각 로우의 저장 위치 예측 불가
- 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적
- 예) 고객변호， 주문일련번호 등
- 병렬처리 시 성능효과 극대화
- DML 경합 분산에 효과적
다) List 파티셔닝
- 불연속적인 값의 목록을 각 파티션에 지정
- 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장
- 예) 판매 데이터를 지역별로 분할
라) Composite 파티셔닝
- Range나 List 파티션 내에 또 다른 서브 파티션 (Range, Hash, List) 구성
- 예) Range + List 또는 List + Hash 등
- Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점
마) Oracle 버전별 파티션 지원 유형
- SQL Server는 2005 버전부터 파티셔닝을 지원하기 시작했고, 현재 2008 벼전까지는 Range 단일 파티션만 지원함
바) Oracle 파티셔닝 방법
- 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 ) -- 주문일자 >= '20100401'
)
;
- Range+Hash 파티셔닝
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 )
)
;
사) SQL Server 파티셔닝 방법
| 1 | 파일 그룹을 생성(선택) |
| 2 | 파일을 파일 그룹에 추가(선택) |
| 3 | 파티션 함수(Partition Function)를 생성(필수) -> 분할 방법과 경계 값을 지정 |
| 4 | 파티션 구성표(Partition Schema)를 생성(필수) -> 파티션 함수에서 정의한 각 파티션의 위치(파일 그룹)를 지정 |
| 5 | 파티션 테이블을 생성 -> 파티션 하고자 하는 테이블을 파티션 구성표에 생성 |
3. 파티션 Pruning
- 파티션 Pruning은 옴티마이저가 SQL의 대상 태이블과 조건절을 분석하여 불필요한 파티션을 액세스 대상에서 제외하는 기능이며, 이를 통해 액세스 조건과 관련된 파티션에서만 작업을 수행할 수 있게 됨
- 기본 파티션 Pruning에는 정적 Pruning과 동적 Pruning이 있고, DBMS 별로 서브쿼리 Pruning, 조인 필터(또는 블룸 펼터) Pruning 같은 고급 Pruning 기법을 사용함
가) 정적(Static) 파티션 Pruning
- 파티션 키 값을 상수로 제공한 경우
SELECT *
FROM SALES_RANGE
WHERE SALES_DATE >= '20060301'
AND SALES_DATE <= '20060401'
;
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 25440 | 3443K (1)| 11:28:41 | | |
| 1 | PARTITION RANGE ITERATOR| | 80 | 25440 | 3443K (1)| 11:28:41 | 40 | 41 |
| 2 | TABLE ACCESS FULL | SALES_RANGE | 80 | 25440 | 3443K (1)| 11:28:41 | 40 | 41 |
----------------------------------------------------------------------------------------------------------------------
나) 동적(Dynamic) 파티션 Pruning
- 파티션 키 값을 바인드 변수로 제공한 경우
SELECT *
FROM SALES_RANGE
WHERE SALES_DATE >= :1
AND SALES_DATE <= :2
;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5719K| 1734M| 105M (1)|350:45:20 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 5719K| 1734M| 105M (1)|350:45:20 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | SALES_RANGE | 5719K| 1734M| 105M (1)|350:45:20 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:1)<TO_DATE(:2))
3 - filter("SALES_DATE">=:1 AND "SALES_DATE"<:2)
4. 인덱스 파티셔닝
가) Local 파티션 인덱스 vs Global 파티션 인덱스
- Local 파티션 인덱스 : 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스(그림 Ⅲ-5-11 참조). 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해 줌. SQL Server에선 '정렬된(aligned) 파티션 인덱스'라고 부름
- Global 파티션 인덱스 : 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스(그림 Ⅲ-5-12 참조). SQL Server에선 '정렬되지 않은(un-aligned) 파티션 인덱스'라고 부름
나) Prefixed 파티션 인덱스 vs NonPrefixed 파티션 인덱스
- Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두는 것을 의미함
- Nonprefixed : 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두지 않는 것을 희미함. 파티션 키가 인덱스 칼럼에 아예 속하지 않을 때도 여기에 속함
- 비파티션까지 포함된 인덱스 유형은 총 5가지
- Local Prefixed 파티션 인덱스
- Local NonPrefixed 파티션 인덱스
- Global Prefixed 파티션 인덱스
- Global NonPrefixed 파티션 인덱스 -- Oracle Not Support
- 비파티션(NonPartitioned) 인덱스
다) 인덱스 파티셔닝 가이드
- 인덱스 파티션은 파티션 테이블과 마찬가지로 성능, 관리 편의성, 가용성, 확장성 등을 제공함.
- 테이블에 종속적인 Local 파티션, 테이블과 독립적인 Global 파티션 모두 가능하지만, 관리적인 측면에서는 Local 인덱스가 훨씬 유용함.
- 테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문(중요)
5. 책 이외 참고 자료
가) 파티션 설명 #1(by 강정식)
나) 파티션 설명 #2(by 이신재)
다) Oracle 10g Partitioning 가이드(by 오동규 수석 컨설턴트)
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2013년 11월 30일
- 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*