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

ㅁ

ㅁ

ㅁ