Partition-Wise 조인

Partition-Wise 조인은 파티션별로 수행되는 조인을 말한다. 병렬 쿼리에서 사용하는 것이 일반적이지만, 병렬 쿼리가 아니어도 Partition-Wise 조인을 수행할 수 있다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.  t1, t2는 비파티션, t3, t4는 파티션 테이블이다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1, CEIL (ROWNUM / 10000) AS c2, LPAD ('X', 500, 'X') AS c3
  FROM XMLTABLE ('1 to 30000');

CREATE TABLE t2 AS SELECT * FROM t1;

CREATE TABLE t3 (c1, c2, c3) PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (10001)
  , PARTITION p2 VALUES LESS THAN (20001)
  , PARTITION p3 VALUES LESS THAN (30001)) AS
SELECT * FROM t1;

CREATE TABLE t4 (c1, c2, c3) PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (10001)
  , PARTITION p2 VALUES LESS THAN (20001)
  , PARTITION p3 VALUES LESS THAN (30001)) AS
SELECT * FROM t1;

아래 쿼리는 비파티션 테이블인 t1, t2 테이블을 조인했다. a.c2 = 1 조건에 의해 t1 테이블은 만 건(A-Rows = 10000), t2 테이블은 3만 건(A-Rows = 30000)을 반환했다. PGA는 1629K를 사용했다.

-- 2
SELECT COUNT (*) FROM t1 a, t2 b WHERE a.c2 = 1 AND b.c1 = a.c1;

---------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |      1 |    4298 |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |    4298 |          |
|*  2 |   HASH JOIN         |      |      1 |  10000 |    4298 | 1629K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |  10000 |    2149 |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |  30000 |    2149 |          |
---------------------------------------------------------------------------

아래 쿼리는 파티션 테이블인 t3, t4 테이블을 조인했다. 실행 계획 2번 PARTITION RANGE ALL 오퍼레이션의 하위에 3번 HASH JOIN 오퍼레이션이 존재하며, 파티션 개수만큼 조인이 수행된 것(Starts = 3)을 확인할 수 있다. Partition-Wise 조인으로 수행된 것이다. 이로 인해 t4 테이블은 1번만 조회되었고(Starts = 1), 만 건을 반환했다. 2번 쿼리에 비해 블록 I/O와 PGA 사용량이 감소한 효과가 있다.^[각주:1]

-- 3-1
SELECT COUNT (*) FROM t3 a, t4 b WHERE a.c2 = 1 AND b.c1 = a.c1;

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | Pstart| Pstop | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |       |       |      1 |    2872 |          |
|   1 |  SORT AGGREGATE      |      |      1 |       |       |      1 |    2872 |          |
|   2 |   PARTITION RANGE ALL|      |      1 |     1 |     3 |  10000 |    2872 |          |
|*  3 |    HASH JOIN         |      |      3 |       |       |  10000 |    2872 | 1597K (0)|
|*  4 |     TABLE ACCESS FULL| T3   |      3 |     1 |     3 |  10000 |    2154 |          |
|   5 |     TABLE ACCESS FULL| T4   |      1 |     1 |     3 |  10000 |     718 |          |
--------------------------------------------------------------------------------------------

t4 테이블을 1번만 조회한 이유는 해시 조인의 특성 때문이다.^[각주:2] 해시 조인은 Build 테이블이 0건인 경우 Probe 테이블을 조회하지 않는다. 아래 쿼리는 t3 테이블의 p2 파티션만 조회했다. t4 테이블이 조회되지 않는 것을 확인할 수 있다. 

-- 3-2
SELECT /*+ OPT_PARAM('_BLOOM_PRUNING_ENABLED', 'FALSE') */
       COUNT (*)
  FROM t3 PARTITION (p2) a, t4 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | Pstart| Pstop | A-Rows | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |       |       |      1 |     720 |          |
|   1 |  SORT AGGREGATE          |      |      1 |       |       |      1 |     720 |          |
|*  2 |   HASH JOIN              |      |      1 |       |       |      0 |     720 |  176K (0)|
|   3 |    PARTITION RANGE SINGLE|      |      1 |     2 |     2 |      0 |     720 |          |
|*  4 |     TABLE ACCESS FULL    | T3   |      1 |     2 |     2 |      0 |     720 |          |
|   5 |    PARTITION RANGE ALL   |      |      0 |     1 |     3 |      0 |       0 |          |
|   6 |     TABLE ACCESS FULL    | T4   |      0 |     1 |     3 |      0 |       0 |          |
------------------------------------------------------------------------------------------------

아래 쿼리는 비파티션 테이블인 t1과 파티션 테이블인 t3를 조인했다. 실행 계획 3번 PART JOIN FILTER CREATE 오퍼레이션과 5번 PARTITION RANGE JOIN-FILTER 오퍼레이션에서 Bloom Filter에 의해 Partition Pruning이 동작한 것을 유추할 수 있다.^[각주:3] 

-- 4-1
SELECT COUNT (*) FROM t1 a, t3 b WHERE a.c2 = 1 AND b.c1 = a.c1;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | Pstart| Pstop | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |       |       |      1 |    2869 |          |
|   1 |  SORT AGGREGATE               |         |      1 |       |       |      1 |    2869 |          |
|*  2 |   HASH JOIN                   |         |      1 |       |       |  10000 |    2869 | 1629K (0)|
|   3 |    PART JOIN FILTER CREATE    | :BF0000 |      1 |       |       |  10000 |    2149 |          |
|*  4 |     TABLE ACCESS FULL         | T1      |      1 |       |       |  10000 |    2149 |          |
|   5 |    PARTITION RANGE JOIN-FILTER|         |      1 |:BF0000|:BF0000|  10000 |     720 |          |
|   6 |     TABLE ACCESS FULL         | T3      |      1 |:BF0000|:BF0000|  10000 |     720 |          |
--------------------------------------------------------------------------------------------------------

아래와 같이 _bloom_pruning_enabled 파라미터를 FALSE로 설정하면 Partition Pruning이 동작하지 않는 것을 확인할 수 있다.

-- 4-2
SELECT /*+ OPT_PARAM('_BLOOM_PRUNING_ENABLED', 'FALSE') */
       COUNT (*)
  FROM t1 a, t3 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | Pstart| Pstop | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |       |       |      1 |    4309 |          |
|   1 |  SORT AGGREGATE       |      |      1 |       |       |      1 |    4309 |          |
|*  2 |   HASH JOIN           |      |      1 |       |       |  10000 |    4309 | 1629K (0)|
|*  3 |    TABLE ACCESS FULL  | T1   |      1 |       |       |  10000 |    2149 |          |
|   4 |    PARTITION RANGE ALL|      |      1 |     1 |     3 |  30000 |    2160 |          |
|   5 |     TABLE ACCESS FULL | T3   |      3 |     1 |     3 |  30000 |    2160 |          |
---------------------------------------------------------------------------------------------

Partial Partition-Wise 조인은 병렬 쿼리에서만 동작한다. 파티션 테이블인 t3 테이블을 먼저 조회해야 t1 테이블을 동적 파티셔닝할 수 있다.

-- 6
SELECT /*+ LEADING(B) PARALLEL(A 2) PARALLEL(B 2) PQ_DISTRIBUTE(A NONE PARTITION) */
       COUNT (*)
  FROM t1 a, t3 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |       |       |        |      |            |
|   1 |  SORT AGGREGATE               |          |      1 |       |       |        |      |            |
|   2 |   PX COORDINATOR              |          |      1 |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10001 |      0 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE            |          |      0 |       |       |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN                |          |      0 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX PARTITION RANGE ALL  |          |      0 |     1 |     3 |  Q1,01 | PCWC |            |
|   7 |        TABLE ACCESS FULL      | T3       |      0 |     1 |     3 |  Q1,01 | PCWP |            |
|   8 |       PX RECEIVE              |          |      0 |       |       |  Q1,01 | PCWP |            |
|   9 |        PX SEND PARTITION (KEY)| :TQ10000 |      0 |       |       |  Q1,00 | P->P | PART (KEY) |
|  10 |         PX BLOCK ITERATOR     |          |      0 |       |       |  Q1,00 | PCWC |            |
|* 11 |          TABLE ACCESS FULL    | T1       |      0 |       |       |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

1. 파티션 별로 해시 맵을 생성하기 때문에 PGA를 효과적으로 사용할 수 있는 것으로 보인다. [본문으로]
2. 필터링에 의한 동작이라는 의견도 있지만 정확한 표현은 아니라고 생각된다. [본문으로]
3. Partial Partition-Wise 조인이라는 의견도 있지만 동적 파티셔닝이 발생하지 않았으므로 Partition Pruning으로 보는 것이 맞다. [본문으로]