2. 파티션 Pruning
2011.06.19 22:35
용어
- 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 파라미터
파라미터명 | 기본값 | 설명 |
_subquery_pruning_cost_factor | 20 | 파티션 테이블이 recurisive 서브쿼리 대상 테이블보다 20배이상 클때 이방식사용 파티션테이블의 5%를 초과하지 않아야 수행 (1/20=0.05) |
_subquery_pruning_reduction | 50 | 드라이빙 테이블 조건절에 의해 액세스되는 데이터가 드라이빙 테이블전체 건수의 50%를 넘지 않아야함 |
_subquery_pruning_enabled | true | 서브쿼리 Pruning을 할성화 |
- 제거될 것으로 예상되는 파티션 개수가 상당히 많고 ,
where 조건절을 가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을때 서브쿼리 Pruning이 작동
(3) 조인 필터 Pruning
- 11g부터 서브쿼리 Pruning의 드라이빙 테이블의 중복 액세스 문제점을 해결하기 위해 도입
블룸 필터(Bloom Filter) 알고리즘
1. 0부터 9번가지 10개 비티를 할당하고 모두 0으로 설정
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 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로 설정
입력값 | h1 출력 | h2 출력 값 |
175 | 5 | 8 |
442 | 2 | 5 |
618 | 8 | 2 |
비트값 확인
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
4. 집합 B (175,327,432,548) 의 각 원소마다 차례로 h1,h2 함수를 적용하고 리턴값에 해당하는 비트를 모두 확인
- 하나라도 0인경우는 A에 없는값
- 1인 경우는 집합 A에 포함도리 가능성이 있는 값이므로 A를 찾아가 실제 같은 값인지 비교
입력값 | h1 출력값 | h2 출력값 | 집합 A확인 여부 | 설명 |
175 | 5 (1) | 8(1) | o | A에 값 확인 |
327 | 7(0) | 3(0) | x | 모두 0, false negative 불가능 |
432 | 2(1) | 4(0) | x | 값중 한개 0 집합A에없음 |
548 | 8(0) | 5(1) | o | A에 값 확인, 확인결과 없는 값 (false positive) |
- 교집합을 찾는 것이 아니고 교집합이 아닌것이 확실한 원소를 찾고 나머지에 대해서 확인하여 실제 교집합인지 가림
블룸 필터 알고리즘에서 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하는 단계
(일자 값에 해당하는 파티션번호를 찾아 n개의 해시함수에 입력후 비트설정)
2. tbl_order 테이블의 파티션 번호별로 비트값을 확인하여 읽지 않아도되는 파티션 목록을 취합
3. 취합된 파티션을 제외한 나머지 파티션만 읽어서 조인
- 모든 파티션을 읽는 것에 비해 효율적
- 서브쿼리 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
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
15 |
5. Outer 조인
![]() | 휘휘 | 2011.03.28 | 17670 |
14 |
6. 스칼라 서브쿼리를 이용한 조인
![]() | balto | 2011.03.27 | 18959 |
13 |
1. Nested Loops 조인
![]() | 오라클잭 | 2011.03.23 | 23031 |
12 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.23 | 3308 |
11 | 3. 해시 조인 | 휘휘 | 2011.03.21 | 6495 |
10 | 2. 소트 머지 조인 | 오예스 | 2011.03.21 | 7913 |
9 | 8. 인덱스 설계 | AskZZang | 2011.03.09 | 5727 |
8 | 9. 비트맵 인덱스 | 휘휘 | 2011.03.07 | 5015 |
7 |
7. 인덱스 스캔 효율
![]() | 휘휘 | 2011.03.06 | 8451 |
6 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15603 |
5 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9504 |
4 |
4. 테이블 Random 액세스 부하
![]() | 휘휘 | 2011.02.26 | 6946 |
3 | 1장. 인덱스 원리와 활용 | 휘휘 | 2011.02.21 | 6798 |
2 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18847 |
1 |
Front Page
![]() | 운영자 | 2011.02.16 | 114630 |