2. 파티션 Pruning
2011.06.22 00:42
2. 파티션 Pruning
- 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외 시키는 기능
- 파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심원리
(1) 기본 파티션 Pruning
- 정적(Static) 파티션 Pruning
- 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동
- 액세스 시 파티션이 쿼리 최적화 시점에 미리 결정
- 실행 계획의 Pstart(partition start)와 Pstop(partition stop) 컬럼 : 액세스할 파티션 번호 출력
- 동적(Dynamic) 파티션 Pruning
- 파티션 키 컬럼을 바인드 변수로 조회 시
1. 쿼리 최적화 시점에 액세스할 파티션을 미리 결정할 수 없음
2. 실행 시점이 되서야 사용자가 입력한 값에 따라 결정
3. 실행계획의 Pstart와 Pstop 컬럼에는 'KEY'라고 표시
- NL 조인 시
- Inner 테이블이 조인 컬럼 기준으로 파티셔닝 되어 있다면 동적 Pruning 작동
파티션 Pruning 기능에 따른 실행계획 비교
/*일반 파티션 테스트 환경*/
/*정적 테스트*/
/*동적 테스트*/
/*In-Lisst 테스트*/
/*테스트 환경*/
/*NL 테스트*/
/*결합 파티션 테스트 환경*/
/*정적 테스트*/
/*동적 테스트*/
파티션 Pruning 기능에 따른 I/O 수행량 비교
/* 주 파티션, 서브 파티션 Partition Pruning 작동 */
SQL> select * from t where no=1 and key ='000100'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.007 44 49 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.007 44 49 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=49 pr=0 pw=0 time=5915 us)
1 PARTITION HASH SINGLE PARTITION: 6 6 (cr=49 pr=0 pw=0 time=5859 us)
1 TABLE ACCESS FULL T PARTITION: 6 6 (cr=49 pr=0 pw=0 time=5724 us)
/* 서브 파티션 Pruning 작동 제어 (서브 파티션 키 컬럼을 함수로 가공 처리) */
SQL> select * from t where no=1 and to_number(key) = 100
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.063 1.056 528 776 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.063 1.056 528 776 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=776 pr=528 pw=0 time=1056056 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=776 pr=528 pw=0 time=1056027 us)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=776 pr=528 pw=0 time=1055868 us)
/* 묵시적 형변환 발생 */
SQL>select * from t where no = 1 and key = 100;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.955 528 776 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.078 0.955 528 776 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=776 pr=528 pw=0 time=954975 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=776 pr=528 pw=0 time=954945 us)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=776 pr=528 pw=0 time=954780 us)
/* 서브 파티션 키 컬럼 묵시적 형변환 발생 (주 파티션 키 컬럼 가공) */
SQL> select * from t where to_char(no) = '1' and key = 100
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 1.297 7.119 3588 4114 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 1.297 7.119 3588 4114 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE ALL PARTITION: 1 10 (cr=4114 pr=3588 pw=0 time=7118551 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=4114 pr=3588 pw=0 time=7118465 us)
1 TABLE ACCESS FULL T PARTITION: 1 160 (cr=4114 pr=3588 pw=0 time=7116955 us)
동적 파티션 Pruning 시 테이블 레벨 통계 사용
- 바인드 변수 사용 시
- 최적화 시점에 파티션을 확장할 수 없어 동적 파티션 Pruning 발생 ☞ 이와 같은 이유로 쿼리 최적화에 테이블 레벨 통계 사용
- 정적 파티션 Pruning 일 시 ☞ 파티션 레벨 통계 사용
- 테이블 레벨 통계는 파티션 레벨 통계보다 다소 부정확하기 때문에 옵티마이저가 가끔 잘못된 실행계획을 수립하는 경우 발생
☞ 바인드 변수 때문에 생기는 대표적인 부작용 중 하나
(2) 서브쿼리 Pruning
- 서브 쿼리 Pruning(8i~)
- 조인 필터 Pruning (11g~)
Select d.분기, o.주문일자, o.고객ID, o.상품ID, o.주문수량, o.주문금액
From 일자 d, 주문 o
Where o.주문일자 = d.일자
And d.분기 >= 'Q20071';
- NL조인 시, Inner 테이블이 조인 컬럼 기준으로 파티셔닝 되어있다면 동적 Pruning 작동
주문은 대용량 거래 테이블로 주문일자 기준으로 월별 Range 파티셔닝 되어있다고 가정
일자 테이블을 드라이빙해 NL 조인한다면 분기 >='Q20071'기간에 포함되는 주문 레코드만 읽음
☞ 대용량 주문 테이블을 Random 액세스 위주의 NL 방식으로 조인 할 시, 결코 좋은 성능을 기대하기 어려움
∵ 2007년 1분기 이후 주문 데이터만 필요한데도 주문 테이블로부터 모든 파티션을 읽어 조인하고서 나중에 분기 조건을 필터링해야 하기 때문
해결책 : 오라클은 recursive 서브쿼리를 이용한 동적 파티션 Pruning 고려 (즉, subquery Pruning이라고 불리는 매커니즘)
Select distinct TBL$OR$IDX$PART$NUM(주문, 0,1,0, a.일자)
From (select 일자 from 일자 were 분기 >= 'Q20071') a
Order by 1
☞ 쿼리 수행 시, 액세스 해야 할 파티션 번호 목록이 구해지며, 이를 이용해 필요한 주문 파티션만 스캔 가능
※ 참고) Pstart에 KEY(SQ)의 SQ : Subuery
※ 주의) 이 방식으로 파티션을 Pruning 하려면 드라이빙 테이블을 한번 더 읽게 되므로 경우에 따라 총 비용이 오히려 증가 할 수 있음
(3) 조인 필터 Pruning
- 서브 쿼리 Pruning은 드라이빙 테이블을 한 번 더 액세스하는 추가 비용 발생
- 블룸 필터(Bloom Filter) 알고리즘을 기반으로 한 조인 필터 pruning 방식 도입 (11g 이후)
블룸 필터(Bloom Filter)알고리즘
- 아래 처럼 A,B 두 개의 집합이 있고, 두 집합 간의 교집합을 찾으려고 한다.
집합 A = {175, 442, 68}
집합 B = {175, 442, 68}
- 이론
1. n 비트 Array를 할당하고 (1~n), 각 비트를 0으로 설정.
2. n개의 값 (1~n중 하나)을 리턴하는 m개의 해시 함수를 정의하며, 서로 다른 해시 알고리즘 사용.
m개의 해쉬 함수는 다른 입력 값에 대해 우연히 같은 출력 값을 낼 수 있음.
3.
4.
- 예시
`1. "0~9"번 까지 10개 비트를 할당하고 모두 0으로 설정.
`2. 0부터 9 까지 총 10개의 값을 리턴할 수 있는 두 개의 함수 (h1, 2)를 정의하여 서로 다르게 구현함.
`3.
`4.
볼룸 필터 알고리즘에서 false positive를 줄이는 방법
집합 B의 원소 548 false positive가 발생해 불필요하게 집합 A를 확인
☞
조인 필터(=볼륨 필터) Pruning
- 11g 부터 적용되기 시작
- 파티션 테이블과 조인 시, 읽지 않아도 되는 파티션을 제거해 줌
- '조인필터 Pruning' 또는 '볼륨 필터 Pruning'이라고 부름
(4) SQL 조건절 작성 시 주의사항
/* 테스트 환경 - 고객 테이블을 아래처럼 가입일 기준으로 Range 월 파티셔닝 */
ㅁ
ㅁ
ㅁ
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
60 | 진행기록 | 운영자 | 2011.08.23 | 3319 |
59 | 3. 병렬 조인 | 실천하자 | 2011.06.28 | 7147 |
58 | 1. 기본 개념 | 멋진넘 | 2011.06.28 | 13405 |
» | 2. 파티션 Pruning | 실천하자 | 2011.06.22 | 26021 |
56 | 1. 테이블 파티셔닝 | suspace | 2011.06.21 | 6814 |
55 | 3. 인덱스 파티셔닝 | darkbeom | 2011.06.20 | 53850 |
54 | 1장. 인덱스 원리와 활용 | 운영자 | 2011.06.15 | 5608 |
53 | 7장. 병렬 처리 | 운영자 | 2011.06.15 | 4611 |
52 | 6장. 파티셔닝 | 운영자 | 2011.06.15 | 3174 |
51 | 5장. 소트 튜닝 | 운영자 | 2011.06.15 | 3288 |
50 | 4장. 쿼리 변환 | 운영자 | 2011.06.15 | 3124 |
49 | 3장. 옵티마이저 원리 | 운영자 | 2011.06.15 | 3152 |
48 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.14 | 15080 |
47 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 실천하자 | 2011.06.14 | 8861 |
46 | 3. 데이터 모델 측면에서의 검토 | 멋진넘 | 2011.06.13 | 5876 |
45 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3356 |
44 | 4. 소트가 발생하지 않도록 SQL 작성 | 멋진넘 | 2011.06.13 | 7900 |
43 | 2. 소트를 발생시키는 오퍼레이션 | 휘휘 | 2011.06.12 | 3456 |
42 | 1. 소트 수행 원리 | 휘휘 | 2011.06.12 | 5736 |
41 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.08 | 5994 |