메뉴 건너뛰기

bysql.net

2. 파티션 Pruning

2011.06.21 16:12

실천하자 조회 수:25672


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 월 파티셔닝 */





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

번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.15 149196
59 3. 인덱스 파티셔닝 darkbeom 2011.06.19 53386
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.18 33506
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.29 30475
» 2. 파티션 Pruning 실천하자 2011.06.21 25672
55 3. 뷰 Merging 실천하자 2011.05.15 23037
54 3. 해시 조인 file darkbeom 2011.03.20 21206
53 2. 서브쿼리 Unnesting darkbeom 2011.05.15 19292
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 17818
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.08 16596
50 7. Sort Area 크기 조정 실천하자 2011.06.13 14702
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.23 14343
48 1. 인덱스 구조 [1] file 실천하자 2011.02.16 13932
47 4. 조인 순서의 중요성 운영자 2011.03.27 13918
46 1. 기본 개념 멋진넘 2011.06.28 13063
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.03 12955
44 9. 비트맵 인덱스 file 실천하자 2011.03.05 12068
43 1. 옵티마이저 file 실천하자 2011.04.18 10930
42 6. 히스토그램 실천하자 2011.04.24 10669
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.13 8589