파티션 Pruning (by niceboyous) [2013.01.29]
h1.02. 파티션 Pruning
- 파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 엑세스 대상에서 제외시키는 기능.
h3.(1) 기본 파티션 Pruning
- 정적파티션 Pruning :
- 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동.
- 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다.
- 실행계획의 Pstart와 Pstop 컬럼에 액세스할 파티션 번호가 출력됨
- 동적파티션 Pruning :
- 파티션 키 컬럼을 바인드변수로 조회하면 쿼리 최적화 시점에 액세스할 피티션을 미리 결정할 수 없고, 실행시점에 입력값에 따라 결정된다.
- 실행계획의 Pstart와 Pstop 컬럼에 'KEY'라고 표시된다.
- NL 조인시에도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 되어 있다면 동적Pruning이 작동한다.
h4.파티션 Pruning 기능에 따른 실행계획 비교
SQL> create table t ( key, no, data )
partition by range(no)(
partition p01 values less than(11)
, partition p02 values less than(21)
, partition p03 values less than(31)
, partition p04 values less than(41)
, partition p05 values less than(51)
, partition p06 values less than(61)
, partition p07 values less than(71)
, partition p08 values less than(81)
, partition p09 values less than(91)
, partition p10 values less than(maxvalue)
)
as
select lpad(rownum, 6, '0'), mod(rownum, 100)+1, lpad(rownum, 10, '0')
from dual
connect by level <= 999999;
-- 상수값으로 입력
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* |
--------------------------------------------------------------------------------------------------
-- Bind변수로 입력
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 |*
---------------------------------------------------------------------------------------------------
* 'KEY'라고 표시된 것은 하드파싱 시점에 액세스할 파티션을 결정할 수 없기 때문이다.
-- In-List 이용
SQL> Select count(*) from t
Where no in (30, 50);
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 210 (22)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE INLIST| | 22428 | 284K| 210 (22)| 00:00:03 |{*}KEY(I) |KEY(I)* |
| 3 | TABLE ACCESS FULL | T | 22428 | 284K| 210 (22)| 00:00:03 |{*}KEY(I) |KEY(I) |*
------------------------------------------------------------------------------------------------
* IN-List 조건을 사용하면 상수 값이더라도 KEY(I)라고 표시된다.
--NL Join Test
SQL> create table n
as
select level no from dual connect by level <=100;
SQL> explain plan for
select /*+ leading(n) use_nl(t) */ *
from n, t
where t.no = n.no;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1097K | 48M | | |
| 1 | NESTED LOOPS | | 1097K | 48M | | |
| 2 | TABLE ACCESS FULL | N | 100 | 1300 | | |
| 3 | PARTITION RANGE ITERATOR| | 10975 | 353K | KEY | KEY |
| 4 | TABLE ACCESS FULL | T | 10975 | 353K | KEY | KEY |
----------------------------------------------------------------------------
-- 결합파티션
SQL > create table t ( key, no, data )
partition by range(no) subpartition by hash(key) subpartitions 16 (
partition p01 values less than(11)
, partition p02 values less than(21)
, partition p03 values less than(31)
, partition p04 values less than(41)
, partition p05 values less than(51)
, partition p06 values less than(61)
, partition p07 values less than(71)
, partition p08 values less than(81)
, partition p09 values less than(91)
, partition p10 values less than(maxvalue)
)
as
select lpad(rownum, 6, '0'), mod(rownum, 50)+1, lpad(rownum, 10, '0')
from dual
connect by level <= 999999;
SQL> select count(*) from t where no between 30 and 50;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | PARTITION RANGE ITERATOR | | 576K | 7323K | 3 | 5 |
| 3 | PARTITION HASH ALL | | 576K | 7323K | 1 | 16 |
| 4 | TABLE ACCESS FULL | T | 576K | 7323K | 33 | 80 |
----------------------------------------------------------------------------
SQL> select count(*) from t where no between :A and :B;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FILTER | | | | | |
|* 2 | PARTITION RANGE ITERATOR | | 2226 | 28938 | KEY | KEY |
| 3 | PARTITION HASH ALL | | 2226 | 28938 | 1 | 16 |
|* 4 | TABLE ACCESS FULL | T | 2226 | 28938 | KEY | KEY |
----------------------------------------------------------------------------
h4.파티션 Pruning 기능에 따른 I/O 수행량 비교
SQL> desc t; --결합파티션(range(no) + 16개 hash(key))
KEY VARCHAR2(24)
NO NUMBER
DATA VARCHAR2(40)
-- 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.062 0.144 0 22 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.092 44 50 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.062 0.236 44 72 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=50 pr=44 pw=0 time=27716 us)
1 PARTITION HASH SINGLE PARTITION: 6 6 (cr=50 pr=44 pw=0 time=27700 us)
1 TABLE ACCESS FULL T PARTITION: 6 6 (cr=50 pr=44 pw=0 time=27685 us)
-- Sub Partition Key Column에 Function을 이용해 Pruning 작동을 못하도록 함. (key를 형변환함)
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.031 0.036 0 121 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.031 1.051 587 777 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.062 1.087 587 898 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=777 pr=587 pw=0 time=362513 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=777 pr=587 pw=0 time=362497 us)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=777 pr=587 pw=0 time=1049983 us)
-- 암시적 형변환이 일어난 경우 (key를 형변환함)
SQL>select * from t
where no=1 and key = 100;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.031 0.034 0 121 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.031 0.037 0 777 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.062 0.071 0 898 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=777 pr=0 pw=0 time=12688 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=777 pr=0 pw=0 time=12665 us)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=777 pr=0 pw=0 time=34235 us)
-- 두 Partition Key에 모두 형변환을 사용할 경우
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.031 0.038 0 166 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.359 4.513 2859 4217 51 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.390 4.550 2859 4383 51 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 PARTITION RANGE ALL PARTITION: 1 10 (cr=4217 pr=2859 pw=0 time=13718 us)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=4217 pr=2859 pw=0 time=4481997 us)
1 TABLE ACCESS FULL T PARTITION: 1 160 (cr=4217 pr=2859 pw=0 time=4508829 us)
h4.동적 파티션 Pruning 시 테이블 레벨 통계 사용
- 바인드 변수를 사용 : 최적화 시점에 파티션을 확정할 수 없음. 동적 파티션 Pruning이 일어남. 쿼리 최적화에 테이블 레벨 통계가 사용
- 상수 조건 조회 시 : 정적 파티션 Pruning이 일어남. 파티션 레벨 통계가 사용됨.
- 테이블 레벨 통계는 파티션 레벨 통계보다 다소 부정확. 옵티마이저가 가끔 잘못된 실행계획을 수립하는 경우가 생기며, 이는 바인드 변수사용의 부작용 중 하나다.
h3.(2) 서브쿼리 Pruning
- 조인에 사용되는 파티션 Pruning기법
- 서브 쿼리 Pruning(8i)
- 조인 필터 Pruning (11g)
Select d.분기, o.주문일자, o.고객ID, o.상품ID, o.주문수량, o.주문금액
From 일자 d, 주문 o --주문table이 주문일자로 월별 파티셔닝 되어있다.
Where o.주문일자 = d.일자
And d.분기 >= 'Q20071';
- NL조인할 때 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.
- 주문은 대용량 거래 테이블이므로 주문일자 기준으로 월별 Range 파티셔닝 되어있고, 일자 테이블을 드라이빙해 NL 조인한다면 분기 >='Q20071'기간에 포함되는 주문 레코드만 읽을 수 있다.
- 2007년 1분기 이후 주문 데이터만 필요한데도 주문 테이블로부터 모든 파티션을 읽어 조인하고서 나중에 분기 조건을 필터링해야 하기 때문에
위와 같은 상황에서 대용량 주문 테이블을 Random 액세스 위주의 NL 방식으로 조인한다면 좋은 성능을 기대하기 어렵다. - 오라클은 recursive 서브쿼리를 이용한 동적 파티션 Pruning을 고려한다. 즉, subquery Pruning이라고 불리는 매커니즘 이다.
SQL> select distinct TBL$OR$IDX$PART$NUM(주문, 0,1,0, a.일자)
From (select 일자 from 일자 were 분기 >= 'Q20071') a
Order by 1
- 내부적으로 위와 같은 쿼리가 실행 된다. 이 쿼리를 수행하면 액세스 해야 할 파티션 번호 목록이 구해지며, 이를 이용해 필요한 주문 파티션만 스캔할 수 있다.
- Pstart에 KEY(SQ)는 SQ는 Subquery를 뜻한다
예)
---------------------------------------------------------------------------------------------------
| 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)|
---------------------------------------------------------------------------------------------------
- 서브쿼리 Pruning 적용 여부는 옵티마이저가 비용을 고려해 내부적으로 결정한다.
- Hidden파타미터표
- 제거될 것으로 예상되는 파티션 개수가 상당히(기본 값에 의하면 50%) 많고, where 조건절을 가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을때만 서브쿼리 Pruning이 작동한다.
- 옵티마이저에 의해 계산된 비용과 관계없이 항상 서브쿼리 Pruning을 실시하는 경우
alter session set "_subquery_pruning_cost_factor" = 1;
alter session set "_subquery_pruning_reduction" = 100;
h3.(3) 조인필터 Pruning
- 서브 쿼리 Pruning은 드라이빙 테이블을 한 번 더 액세스하는 추가 비용이 발생한다.
- 11g부터 오라클은 블룸 필터(Bloom Filter) 알고리즘을 기반으로 한 조인 필터 pruning 방식을 도입하였다.
(p648~653 예시참고)
- 파티션 테이블과 조인 시, 읽지 않아도 되는 파티션을 제거해 주는 것으로서 '조인필터 Pruning' 또는 '볼륨 필터 Pruning'이라고 부른다.
- 이 알고리즘을 사용하는 조인필터 Pruning도 조인 대상 집합을 확실히 포함하는 파티션을 찾는 게 아니라, 확실히 포함하지 않는 파티션을 찾는다. 그런 후 그 파티션 목록을 제외한 나머지 파티션만 스캔한다.
- 위 쿼리는 기본적으로 2007년 1월부터 현재까지의 파티션만 읽지만, false positive가 발생해 그 이전 파티션 중 일부를 불필요하게 읽을 가능성은 있다. 하지만 이런 고급 파티션 Pruning 기법을 활용하지 않았을 때 모든 파티션을 읽는 것에 비하면 훨씬 효율적이다.
- 서브쿼리 pruning과 비교해 보면, 드라이빙 테이블이 클수록 조인 필터 Pruning이 유리하다.
h3.(4) SQL 조건절 작성 시 주의사항
--고객 테이블을 가입일 기준으로 Range 월 파티셔닝
create table 고객
partition by range(가입일)
( partition m01 values less than('20090201')
, partition m02 values less than('20090301')
, partition m03 values less than('20090401')
, partition m04 values less than('20090501')
, partition m05 values less than('20090601')
, partition m06 values less than('20090701')
, partition m07 values less than('20090801')
, partition m08 values less than('20090901')
, partition m09 values less than('20091001')
, partition m10 values less than('20091101')
, partition m11 values less than('20091201')
, partition m12 values less than('20100101'))
as
select rownum 고객ID
, dbms_random.string('a', 20) 고객명
, to_char(to_date('20090101', 'yyyymmdd') + (rownum - 1), 'yyyymmdd') 가입일
from dual
connect by level <= 365;
SQL> set autotrace traceonly exp;
SQL> select * from 고객
2 where 가입일 like '200910%';
Execution Plan
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 62651 | 2 (0)| | |
| 1 | PARTITION RANGE ITERATOR| | 31 | 62651 | 2 (0)| 9 | 10 |
|* 2 | TABLE ACCESS FULL | 고객 | 31 | 62651 | 2 (0)| 9 | 10 |
--------------------------------------------------------------------------------------
=> 가입일 조회시 like연산을 사용한 경우 : m10파티션만 읽지 않고 m09 파티션까지 읽었다.
이유는, '200910','20091000' 등 '20091001'보다 작은 수많은 문자가 존재하고 이들이 입력된다면 m09에 저장되기 때문.
=> 따라서, like대신 between 연산자를 이용해 정확한 값 범위를 주자.
SQL> select * from 고객
2 where 가입일 between '20091001' and '20091031';
Execution Plan
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Psop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 62651 | 2 (0)| | |
| 1 | PARTITION RANGE SINGLE| | 31 | 62651 | 2 (0)| 10 |10 |
|* 2 | TABLE ACCESS FULL | 고객 | 31 | 62651 | 2 (0)| 10 |10 |
-----------------------------------------------------------------------------------