• prune
• 쓸데없는 가지를 치다. 불필요한 부분을 제거한다
• pruning(=Elimination )
• 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능

(1) 기본 파티션 Pruning

두가지 기본 파티션 Pruning 기법

• 정적 (Static) 파티션 Pruning:
• 파티션 키 컬럼을  상수 조건으로 조회하는 경우 작동
• 쿼리 최적화 시점에 미리 결정
• 실행계획의 Pstart(Partition start)와 Pstop(partiton stop) 컬럼에는 액세스할 파티션 번호가 출력
• 동적(Dynamic) 파티션 Pruning:
• 파티션 키 컬럼을 바인드 변수로 조회(쿼리 최적화 시점에 액세스 파티션 결정 불가능)
• 실행 시점에 결정
• 실행계획읜 Pstart와 Pstop 컬럼에 ‘Key’ 라고 표시, NL 조인시도
  Inner테이블이 조인 컬럼 기준으로 파티션되어있다면 동적 Pruning이 작동

파티션 Pruning 기능에 따른 실행계획 비교

자료생성

SQL> set linesize 120
SQL> create table t (key, no,data)
 2  partition by range (no) (
 3      partition p01 values less than (11)
 4     ,partition p02 values less than (21)
       ,partition p03 values less than (31)
 5    6        ,partition p04 values less than (41)
 7     ,partition p05 values less than (51)
 8     ,partition p06 values less than (61)
       ,partition p07 values less than (71)
 9   10        ,partition p08 values less than (81)
11     ,partition p09 values less than (91)
12     ,partition p10 values less than (maxvalue)
13  )
14  as
select lpad(rownum,6,'0'), mod(rownum,100)+1, lpad(rownum,10,'0')
15   16  from dual
17  connect by level <=999999;



Table created.

상수 조건 ( between 30 and 50 )

SQL> SQL> SQL> set autot traceonly exp;
SQL> select count(*) from t where no between 30 and 50;

Execution Plan
----------------------------------------------------------
Plan hash value: 2010034966

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |    13 |   745  (46)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   299K|  3804K|   745  (46)| 00:00:06 |     3 |     5 |
|*  3 |    TABLE ACCESS FULL      | T    |   299K|  3804K|   745  (46)| 00:00:06 |     3 |     5 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("NO">=30 AND "NO"<=50)

Note
-----
  - dynamic sampling used for this statement (level=2)


∴ pstart 3, pstop 5
10개중 3개만 피티션 읽음

바인드 변수 (동적 Pruning)

SQL> variable a number;
SQL> variable b number;
SQL> exec :a:=30

PL/SQL procedure successfully completed.

SQL> exec :b:=50

PL/SQL procedure successfully completed.

SQL> select count(*) from t where no between :a and :b
 2  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2640844081

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     3 |  1395   (3)| 00:00:10 |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |     3 |            |          |       |       |
|*  2 |   FILTER                   |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      | 10001 | 30003 |  1395   (3)| 00:00:10 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | T    | 10001 | 30003 |  1395   (3)| 00:00:10 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
  4 - filter("NO">=TO_NUMBER(:A) AND "NO"<=TO_NUMBER(:B))

SQL>

∴
pstsrt,pstop 에 Key 라고 표시
하드 파싱 시점에 액세스할 파티션을 결정할수 없음

IN-List

SQL> select count(*) from t where no in (30,50);

Execution Plan
----------------------------------------------------------
Plan hash value: 3111010177

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |     3 |   279   (3)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE         |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE INLIST|      | 18770 | 56310 |   279   (3)| 00:00:02 |KEY(I) |KEY(I) |
|*  3 |    TABLE ACCESS FULL    | T    | 18770 | 56310 |   279   (3)| 00:00:02 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("NO"=30 OR "NO"=50)

SQL>

∴ 상수 값이더라도 KEY(I)라고 표시

NL 조인 테스트

SQL> create table n
 2  as
 3  select level no from dual connect by level <=100;

Table created.

SQL>


SQL> select /*+ leading (n) use_nl(t) */ *
 2  from n,t
 3  where t.no=n.no;

Execution Plan
----------------------------------------------------------
Plan hash value: 80465819

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |   999K|    32M| 13796   (2)| 00:01:36 |       |       |
|   1 |  NESTED LOOPS             |      |   999K|    32M| 13796   (2)| 00:01:36 |       |       |
|   2 |   TABLE ACCESS FULL       | N    |   100 |  1300 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ITERATOR|      | 10000 |   205K|   138   (2)| 00:00:01 |   KEY |   KEY |
|*  4 |    TABLE ACCESS FULL      | T    | 10000 |   205K|   138   (2)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - filter("T"."NO"="N"."NO")

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>


∴ n 테이블로부터 읽히는 값에 따라 t 테이블에 동적 Pruning이 일어남

결합 파티션



SQL> create table t (key, no,data)
 2  partition by range (no) subpartition by hash(key) subpartitions 16 (
 3      partition p01 values less than (11)
 4     ,partition p02 values less than (21)
 5     ,partition p03 values less than (31)
 6     ,partition p04 values less than (41)
 7     ,partition p05 values less than (51)
       ,partition p06 values less than (61)
 8    9        ,partition p07 values less than (71)
10     ,partition p08 values less than (81)
11     ,partition p09 values less than (91)
12     ,partition p10 values less than (maxvalue)
)
13   14  as
15  select lpad(rownum,6,'0'), mod(rownum,100)+1, lpad(rownum,10,'0')
16  from dual
17  connect by level <=999999;


Table created.

SQL> SQL>

∴ no 컬럼기준 range 파티셔닝, key 컬럼 기준 해시 파티셔닝

상수조건

SQL> select count(*) from t where no between 30 and 50;

Execution Plan
----------------------------------------------------------
Plan hash value: 548125637

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |    13 |   846  (51)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   398K|  5053K|   846  (51)| 00:00:06 |     3 |     5 |
|   3 |    PARTITION HASH ALL     |      |   398K|  5053K|   846  (51)| 00:00:06 |     1 |    16 |
|*  4 |     TABLE ACCESS FULL     | T    |   398K|  5053K|   846  (51)| 00:00:06 |    33 |    80 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - filter("NO">=30 AND "NO"<=50)

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>

∴
ID2: range 파티션에 대한 Prunin 정보
ID3: 해시 파티션에 대한 Pruning 정보 표시

range 파티션에서 3번, sub파티션에서 16개씩 총 48 (3*16)개 파티션 읽음

변수


SQL> variable a number;
SQL> variable b number;
SQL> exec :a:=30;

PL/SQL procedure successfully completed.


SQL> exec :b:=50;

PL/SQL procedure successfully completed.

SQL> select count(*) from t where no between :a and :b;

Execution Plan
----------------------------------------------------------
Plan hash value: 1813806711

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    13 |   304   (9)| 00:00:03 |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |    13 |            |          |       |       |
|*  2 |   FILTER                   |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |  1833 | 23829 |   304   (9)| 00:00:03 |   KEY |   KEY |
|   4 |     PARTITION HASH ALL     |      |  1833 | 23829 |   304   (9)| 00:00:03 |     1 |    16 |
|*  5 |      TABLE ACCESS FULL     | T    |  1833 | 23829 |   304   (9)| 00:00:03 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
  5 - filter("NO">=TO_NUMBER(:A) AND "NO"<=TO_NUMBER(:B))

Note
-----
  - dynamic sampling used for this statement (level=2)

∴
해시 서브 파티션 키 컬럼에 조건절을 사용하지 않아 파티션별로 16개씩 읽게됨
range 파티션에서는 목록을 확정할수 없어 ‘KEY’라고 표시

파티션  Pruning 기능에 따른 I/O 수행량 비교

파티션 Pruning 작동 여부에 따른 수행 일량 차이 확인

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from t where no=1 and key='000100';

KEY                  NO DATA
------------ ---------- --------------------
000100                1 0000000100



Plan Hash: 3551997908
select *
from
t where no=1 and key='000100'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.06          0          6          1           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         54          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.06          0         60          1           2

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  PARTITION RANGE SINGLE PARTITION: 1 1 (cr=27 pr=0 pw=0 time=0 us cost=11 size=33 card=1)
     1   PARTITION HASH SINGLE PARTITION: 6 6 (cr=27 pr=0 pw=0 time=0 us cost=11 size=33 card=1)
     1    TABLE ACCESS FULL T PARTITION: 6 6 (cr=27 pr=0 pw=0 time=0 us cost=11 size=33 card=1)


주파티션과 서브 파티션에 Pruning 이 작동해 하나의 서브파티션에서 27개 블록만 읽음

서브 파티션 Pruning 이 작동하지 못하도록 서브 파티션 키 컬럼을 함수로 가공

SQL> select * from t where no=1 and to_number(key)=100;

KEY                  NO DATA
------------ ---------- --------------------
000100                1 0000000100


SQL ID: acm5tw96xcur0
Plan Hash: 2673072502
select *
from
t where no=1 and to_number(key)=100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0         40         12           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.02       0.02        274        832          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.02       0.02        274        872         12           2

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  PARTITION RANGE SINGLE PARTITION: 1 1 (cr=416 pr=274 pw=0 time=0 us cost=223 size=3300 card=100)
     1   PARTITION HASH ALL PARTITION: 1 16 (cr=416 pr=274 pw=0 time=0 us cost=223 size=3300 card=100)
     1    TABLE ACCESS FULL T PARTITION: 1 16 (cr=416 pr=274 pw=0 time=0 us cost=223 size=3300 card=100)

********************************************************************************


16개의 서브 파티션에서 416개의 블록을 읽음
인덱스와 마찬가지로 파티션 키 컬럼역시 함부로 가공 불가

묵시적 형변환 테스트

SQL> select * from t where no=1 and key=100;

KEY                  NO DATA
------------ ---------- --------------------
000100                1 0000000100


********************************************************************************

SQL ID: 609cpj8wtbjrh
Plan Hash: 2673072502
select *
from
t where no=1 and key=100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0         16          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.01          0        832          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.02       0.02          0        848          0           2

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  PARTITION RANGE SINGLE PARTITION: 1 1 (cr=416 pr=0 pw=0 time=0 us cost=223 size=3300 card=100)
     1   PARTITION HASH ALL PARTITION: 1 16 (cr=416 pr=0 pw=0 time=0 us cost=223 size=3300 card=100)
     1    TABLE ACCESS FULL T PARTITION: 1 16 (cr=416 pr=0 pw=0 time=0 us cost=223 size=3300 card=100)

********************************************************************************


내부적인 형변환이 발생해 키컬럼을 가공했을때와 동일

주파티션 키 컬럼 가공, 서브 파티션 키 컬럼의 묵시적 형변환

SQL> select * from t where to_char(no)='1' and key=100;

KEY                  NO DATA
------------ ---------- --------------------
000100                1 0000000100

********************************************************************************

SQL ID: 3bz2gmhk7bnpt
Plan Hash: 3941597447
select *
from
t where to_char(no)='1' and key=100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0         32          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.88       0.88       3253       8474         75           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.89       0.89       3253       8506         75           2

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  PARTITION RANGE ALL PARTITION: 1 10 (cr=4312 pr=3253 pw=0 time=0 us cost=304 size=2409 card=73)
     1   PARTITION HASH ALL PARTITION: 1 16 (cr=4312 pr=3253 pw=0 time=0 us cost=304 size=2409 card=73)
     1    TABLE ACCESS FULL T PARTITION: 1 160 (cr=4312 pr=3253 pw=0 time=0 us cost=304 size=2409 card=73)

********************************************************************************

∴
10개의 주 파티션 마다 16개씩, 총 160개 파티션에서 4312개 블록을 읽음

동적 파티션 Pruning 시 테이블 레벨 통계 사용

• 동적 파티션 Pruning 일때는 최적화 시점에 파티션을 확정할수 없어 테이블 레벨 통계 사용
• 정적 파티션 Pruning일때는 파티션 레벨 통계 사용

∴ 테이블 레벨 통계는 파티션레벨 통계보다 부정확하여 가끔 잘못된 실행계획을 수립하는 경우발생
  (바인드 변수의 대표적인 부작용중 하나)



(2) 서브쿼리 Pruning

조인절에 사용되는 두가지 고급 파티션 (Pruning 기법)

• 서브쿼리 Pruning (8i ~)
• 조인 필터 Pruning (11g ~)

서브 쿼리 Pruning

SQL> drop table saledate;

Table dropped.

SQL> create table saledate (    sdate   ,       quarter )
as
select to_date('20010101','yyyymmdd')+rownum sdate,'Q'||'2011'||trim(to_char(round(rownum/90))) quarter from dual
connect by level<=365;  2    3    4

Table created.

SQL> alter table saledate add primary key  (sdate);

Table altered.

SQL> drop table tbl_order ;

Table dropped.

SQL> create table tbl_order (
 2     odate   ,
 3     cid     ,
 4     pid      )
 5  partition by range (odate) (
 6     partition p01 values less than (to_date('201101','yyyymm')),
 7     partition p02 values less than (to_date('201102','yyyymm')),
 8     partition p03 values less than (to_date('201103','yyyymm')),
 9     partition p04 values less than (to_date('201104','yyyymm')),
10     partition p05 values less than (to_date('201105','yyyymm')),
11     partition p06 values less than (to_date('201106','yyyymm')),
12     partition p07 values less than (to_date('201107','yyyymm')),
13     partition p08 values less than (to_date('201108','yyyymm')),
14     partition p09 values less than (to_date('201109','yyyymm')),
15     partition p10 values less than (to_date('201110','yyyymm')),
       partition p11 values less than (to_date('201111','yyyymm')),
16   17        partition p12 values less than (maxvalue)
18  )
19  as
20  select  to_date('20010101','yyyymmdd')+mod(rownum,365)+1,lpad(rownum,8,'0'),lpad(rownum,8,'0') from dual
21  connect by level<=365*20;

Table created.

SQL> alter session set optimizer_features_enable='10.2.0.5';

Session altered.

SQL>
alter session set "_subquery_pruning_enabled"=true;SQL>

Session altered.

SQL> alter session set "_subquery_pruning_cost_factor"=1;

Session altered.

SQL> alter session set "_subquery_pruning_reduction"=100;

Session altered.

SQL> set autot traceonly exp;
SQL> set linesize 120
SQL> select   d.quarter, o.odate,o.cid,o.pid
from saledate d, tbl_order o
where o.odate=d.sdate
and d.quarter >= 'Q20114';  2    3    4

• NL 조인시 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 되있으면 동적 Pruning이 작동
• 하지만 대용량의 테이블을 Random 액세스 위주의 NL방식으로 조인하면 좋은 성능을 기대하기 어려움

• 이경우 오라클은 Recursive 서브쿼리를 이요한 동적 파티션 Pruning을 고려
• 내부적으로 아래의 서브쿼리를 수행

SQL> select distinct TBL$OR$IDX$PART$NUM(tbl_order,0,1,0,a.sdate)
from (select sdate from saledate where quarter >='Q20114') a
order by 1;
 2    3
Execution Plan
----------------------------------------------------------
Plan hash value: 2211135992

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    51 |  1683 |     5  (40)| 00:00:01 |
|   1 |  SORT UNIQUE       |          |    51 |  1683 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| SALEDATE |    51 |  1683 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("QUARTER">='Q20114')

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>



액세스해야할 파티션 번호 목록을 구한후 이를 이용하여 필요한 주문 파티션만 스캔

Execution Plan
----------------------------------------------------------
Plan hash value: 4290169943

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |  1020 | 63240 |    18   (6)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                |           |  1020 | 63240 |    18   (6)| 00:00:01 |       |       |
|*  2 |   TABLE ACCESS FULL       | SALEDATE  |    51 |  1683 |     3   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE SUBQUERY|           |  7300 |   206K|    14   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
|   4 |    TABLE ACCESS FULL      | TBL_ORDER |  7300 |   206K|    14   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("O"."ODATE"="D"."SDATE")
  2 - filter("D"."QUARTER">='Q20114')

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>


KEY(SQ) : SQ=subquery

;  이방식은 드라이빙 테이블을 한번더 읽으므로 경우에 따라 총비용이 증가할수 있음

관련 Hidden 파라미터

• 제거될 것으로 예상되는 파티션 개수가 상당히 많고 ,
  where 조건절을 가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을때 서브쿼리  Pruning이 작동

(3) 조인 필터 Pruning

• 11g부터 서브쿼리 Pruning의 드라이빙 테이블의 중복 액세스 문제점을 해결하기 위해 도입

블룸 필터(Bloom Filter) 알고리즘

1. 0부터 9번가지 10개 비티를 할당하고 모두 0으로 설정

2.0부터 9까지 총 10개의 값을 리턴할 수 있는 두개의 함수 (h1, h2)를 정의

SQL> create or replace function h1(e number) return number
as
begin
       return mod(e,10);
end;
/
 2    3    4    5    6
Function created.

SQL>
SQL> create or replace function h2(e number) return number
as
begin
       return mod(ceil(e/10),10);
end;
 2    3    4    5    6
 7  /

Function created.

SQL> select rownum no,h1(rownum) r1,h2(rownum) r2
from dual
connect by level<=100;  2    3

       NO         R1         R2
---------- ---------- ----------
        1          1          1
        2          2          1
        3          3          1
        4          4          1
        5          5          1
        6          6          1
        7          7          1
        8          8          1
        9          9          1
       10          0          1
       11          1          2

       NO         R1         R2
---------- ---------- ----------
       12          2          2
       13          3          2


100 rows selected.

SQL>

3. 집합 A의 각 원소 (175,442,618) 마다 차례로 h1,h2 함수를 적용
각 해시 함수에서 리턴된 값(0~9 중하나)에 해당하는 비트를 모두 1로 설정

4. 집합 B (175,327,432,548) 의 각 원소마다 차례로 h1,h2 함수를 적용하고 리턴값에 해당하는 비트를 모두 확인

• 하나라도 0인경우는 A에 없는값
• 1인 경우는 집합 A에 포함도리 가능성이 있는 값이므로 A를 찾아가 실제 같은 값인지 비교

• 교집합을 찾는 것이 아니고 교집합이 아닌것이 확실한 원소를 찾고 나머지에 대해서 확인하여 실제 교집합인지 가림

블룸 필터 알고리즘에서 false positive 를 줄이는 방법

• 더 많은 Array 비트를 할당(공간)하거나 더많은 해시 함수(시간)를 사용하면 false positive 의 발생가능성이 줄어듬

조인 필터(=블룸 필터) Pruning

SQL> select   d.quarter, o.odate,o.cid,o.pid
from saledate d, tbl_order o
where o.odate=d.sdate
and d.quarter >= 'Q20114';  2    3    4

Execution Plan
----------------------------------------------------------
Plan hash value: 3423200257

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |  1020 | 63240 |    18   (6)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                   |           |  1020 | 63240 |    18   (6)| 00:00:01 |       |       |
|   2 |   PART JOIN FILTER CREATE    | :BF0000   |    51 |  1683 |     3   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL         | SALEDATE  |    51 |  1683 |     3   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE JOIN-FILTER|           |  7300 |   206K|    14   (0)| 00:00:01 |:BF0000|:BF0000|
|   5 |    TABLE ACCESS FULL         | TBL_ORDER |  7300 |   206K|    14   (0)| 00:00:01 |:BF0000|:BF0000|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("O"."ODATE"="D"."SDATE")
  3 - filter("D"."QUARTER">='Q20114')

Note


-----
  - dynamic sampling used for this statement (level=2)

SQL>

• id2
• 블룸 필터를 생성
• id4
• 블룸 필터를 이용해 파티션 Pruning하는 단계

• 모든 파티션을 읽는 것에 비해 효율적
• 서브쿼리 Pruning과 비교할경우 드라이빙 테이블이 클수록 조인 필터 Pruning이 유리

• 조인 필터 Pruning기능을 비활성화
• _bloom_pruning_enabled = flase
  

(4) SQL 조건절 작성 시 주의사항

SQL> create table custom
partition by range ( edate)
 2    3  (      partition m01 values less than ('20090201')
 4     ,partition m02 values less than ('20090301')
 5     ,partition m03 values less than ('20090401')
 6     ,partition m04 values less than ('20090501')
 7     ,partition m05 values less than ('20090601')
       ,partition m06 values less than ('20090701')
 8    9        ,partition m07 values less than ('20090801')
10     ,partition m08 values less than ('20090901')
       ,partition m09 values less than ('20091001')
11   12        ,partition m10 values less than ('20091101')
13     ,partition m11 values less than ('20091201')
14     ,partition m12 values less than ('20100101')
15  ) as
16  select rownum cusid
17     ,dbms_random.string('a',20) cname
       ,to_char(to_date('20090101','yyyymmdd')+(rownum-1),'yyyymmdd') edate
18   19  from dual
20  connect by level <=365;

Table created.

SQL> select * from custom
 2  where edate like '200910%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3417110337

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |    31 | 62651 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|        |    31 | 62651 |     3   (0)| 00:00:01 |     9 |    10 |
|*  2 |   TABLE ACCESS FULL      | CUSTOM |    31 | 62651 |     3   (0)| 00:00:01 |     9 |    10 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("EDATE" LIKE '200910%')

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>

• 2009 년 10월 에 가입한 고객을 조회하였는데 pstart 9 를 스캔
• 20091001 이전값이 있을경우 200909에 저장 되므로 09번 파티션까지 스캔
• between 을 사용하여 정확한 값 범위를 주고 쿼리하면 비효율 해소

SQL> select * from custom
 2  where edate between '20091001' and '20091031';

Execution Plan
----------------------------------------------------------
Plan hash value: 2834537591

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |    31 | 62651 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|        |    31 | 62651 |     2   (0)| 00:00:01 |    10 |    10 |
|*  2 |   TABLE ACCESS FULL    | CUSTOM |    31 | 62651 |     2   (0)| 00:00:01 |    10 |    10 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("EDATE"<='20091031')

Note
-----
  - dynamic sampling used for this statement (level=2)

SQL>

• sql 수정이 곤란할경우 연월 (yyyymm)로 파티션값을 정의해주면 원하는 결과를 얻을수 있음
• 파티션 설계와 상관없이 옵티마이저가 효율적인 선택을 할수 있도록 하려면 between 연산자를 사용

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