Partition Pruning

xlwdn·2023년 8월 24일

Bloom Filter 프루닝 Partition Pruning 동적 파티션 프루닝 서브쿼리 파티션 프루닝 정적 파티션 프루닝

Partition Pruning?

SQL을 실행하는 시점에 파티션 세그먼트를 액세스 대상에서 제외시키는 기능

기본 파티션 Pruning

정적 파티션 프루닝

파티션 키 컬럼을 상수 조건(ex. where 등록일자 = to_date(’20230824’, ‘YYYYMMDD’))으로 조회할 때 작동. 실행계획의 Pstart와 Pstop 칼럼에 액세스할 파티션 번호가 출력된다.

정적 파티션 Pruning 예시

SQL> Select count(*) from t 
Where no between 30 and 50; 
--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |    13 |   287  (14)| 00:00:04 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   204K|  2590K|   287  (14)| 00:00:04 *|*     *3 |     5* |
|   3 |    TABLE ACCESS FULL      | T    |   204K|  2590K|   287  (14)| 00:00:04 *|     3 |     5* |
--------------------------------------------------------------------------------------------------

동적 파티션 프루닝

파티션 키 컬럼을 바인드 변수로 조회 시 실행 시점에 사용자가 입력한 값에 따라 액세스할 파티션을 정한다. 이때 실행계획에서는 Pstart와 Pstop에 ‘KEY’로 표시된다.

NL조인 시에도 Inner table이 조인 컬럼 기준으로 파티셔닝 되있다면 동적 Pruning이 작동한다.

동적 파티션 Pruning 예시

SQL>Select count(*) from t 
Where no between :a and :b;
---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    13 |   876   (7)| 00:00:11 |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |    13 |            |          |       |       |
|   2 |   FILTER                   |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |  2214 | 28782 |   876   (7)| 00:00:11 *|   KEY |   KEY |*
|   4 |     TABLE ACCESS FULL      | T    |  2214 | 28782 |   876   (7)| 00:00:11 *|   KEY |   KEY |*

서브쿼리 Pruning

서브 쿼리 Prunning

오라클이 Recursive 서브쿼리를 이용한 동적 파티션 프루닝을 고려하는 것이다.

서브쿼리 Pruning 작동 조건 및 예시

고려한다고 표현한 것처럼 오라클이 제거될 것으로 예상되는 파티션 개수가 일정 비율 이상이고, where 조건절을 가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을 때만 작동한다.

# 서브쿼리 Pruning이 작동하는 경우

alter session set "_subquery_pruning_cost_factor" = 1;
alter session set "_subquery_pruning_reduction" = 100;

select /*+ leading(d) use_hash(e) */ *
from   dept d, emp e
where  d.deptno = e.deptno
and    d.loc = 'DALLAS';

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     5 |   585 |     8  (13)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                |      |     5 |   585 |     8  (13)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS FULL       | DEPT |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE SUBQUERY|      |    14 |  1218 |     4   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
|   4 |    TABLE ACCESS FULL      | EMP  |    14 |  1218 |     4   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
--------------------------------------------------------------------------------------------------

select /*+ leading(e) use_hash(d) */ *
from   dept d, emp e
where  d.deptno = e.deptno
and    d.loc = 'DALLAS';

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     5 |   585 |     8  (13)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                |      |     5 |   585 |     8  (13)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE SUBQUERY|      |    14 |  1218 |     4   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
|   3 |    TABLE ACCESS FULL      | EMP  |    14 |  1218 |     4   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
|*  4 |   TABLE ACCESS FULL       | DEPT |     1 |    30 |     3   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------

위 예시에서는 서브쿼리 Pruning이 작동하여 emp를 조회할 때 연관된 DEPT가 dallas인 것만 조회하였다.

블룸 필터(Join filter) Pruning

블룸 필터를 활용해 프루닝을 수행한다.

Bloom Filter Algorithm

블룸 필터 알고리즘은 특정한 크기의 비트 배열과 서로 다른 k 가지의 hash 함수를 사용하여 구현된다. 여기서 hash 함수는 비트 배열에 대해 배열 내의 각 값을 균등한 확률로 출력해야만 한다.

예를 들어 10 크기의 비트 배열과 모듈러 10 연산을 수행하는 hash 함수를 가진다고 했을 때, 여기서 입력 값으로 34가 들어왔을 때 비트 배열의 4번째를 변경한다. 다음 입력값 37은 7번째 비트를 변경하게되고 다음 입력값 54는 34와 동일한 4번째 비트를 변경한다.

이때 실제로는 34와 54는 다르지만 bloom filter algorithm에선 둘 다 참으로 판단한다. 다만 이후 마지막 값 비교 연산에서는 거짓이 되며 이를 false positive(참인데 거짓)라고 부른다. 거짓인데 참인 경우(false negative)는 bloom filter algorithm에서 발생하지 않으며 bloom filter의 성능은 false positive가 많이 발생할 수록 저하된다.

Bloom filter 제어 및 작동 조건

블룸 필터는 Parallel Join과 Partition Table Join 시에 실제 조인 작업 수행 이전에 조인 대상의 건수를 줄여 성능을 최적화하는 역할을 한다. Bloom filter의 사용 여부를 제어하는 힌트로는 PX_JOIN_FILTER/NO_PX_JOIN_FILTER가 있으나 해당 힌트를 사용하여도 모든 SQL에 대해 Bloom Filter를 사용하는 것은 아니다. Bloom Filter를 사용하기 위해선 몇 가지 전제조건이 필요하며 그 조건은 다음과 같다.

Hash Join 또는 Merge Join
Partition Table Join이어야함.
Partition Table Join이 아닐 때에는 Parallel Query여야함.
Parallel Query도 아니고 Partition Query도 아닌 경우에도 Inline View 안에 Group by를 적절하게 포함한 경우엔 Bloom Filter를 사용할 수 있다. (이때에는 Join Column이 Group by 절에 사용되어야한다.)

Bloom filter 실행 계획

Select d.분기, o.주문일자, o.고객ID, o.상품ID, o.주문수량, o.주문금액주문금액
From   일자 d, 주문 o
Where o.주문일자 = d.일자
And d.분기 >= 'Q20071';

Rows     Row Source Operation
-------  ---------------------------------------------------
 480591  HASH JOIN (cr=3827 pr=0 pw=0 time=4946 us cost=655 size=2100270 ...)
     12   PART JOIN FILTER CREATE:BF0000 (cr=4 pr=0 pw=0 time=18 us cost=4 ...)
     12    TABLE ACCESS FULL 일자 (cr=4 pr=0 pw=0 time=6 us cost=4 size=10388 ...)
 480591   PARTITION RANGE JOIN-FILTER PARTITION: :BF0000 :BF0000 (cr=3823 pr=0 ...)
 480591    TABLE ACCESS FULL 주문 PARTITION:  :BF0000 :BF0000 (cr=3823 pr=0 ...)