06. 파티셔닝 (by jiginet) [2016.08.31]
01. 테이블 파티셔닝
- 파티셔닝 : 테이블이나 인덱스의 데이터를 파티션 단위로 나누어 저장하는 것
- 파티셔닝을 사용하는 이유
- 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경
- 성능적 측면 : 파티션 단위 조회 및 DML 수행
(1) 파티션 기본구조
| 수동 파티션(oracle 7.3 이전버전에서 사용) |
|---|
| {code:sql} -- 파티션 뷰를 정의할 때 사용할 Base 테이블을 만든다. create table p1 as select * from emp where deptno = 10; create table p2 as select * from emp where deptno = 20; create table p3 as select * from emp where deptno = 30; |
-- 체크 제약을 반드시 설정해야 함
alter table p1 add constraint c_deptno_10 check(deptno < 20);
alter table p2 add constraint c_deptno_20 check(deptno >= 20 and deptno < 30);
alter table p3 add constraint c_deptno_30 check(deptno >= 30 and deptno < 40);
create index p1_empno_idx on p1(empno);
create index p2_empno_idx on p2(empno);
create index p3_empno_idx on p3(empno);
analyze table p1 compute statistics;
analyze table p2 compute statistics;
analyze table p3 compute statistics;
-- 파티션 뷰를 정의한다.
create or replace view partition_view
as
select * from p1
union all
select * from p2
union all
select * from p3 ;
explain plan for
select * from partition_view
where deptno = :deptno ;
|
* 9i에서 나오는 결과
!쿼리1.png!
|| 파티션 테이블(오라클 8 이후부터 지원)||
|{code:sql}
-- 파티션 테이블
create table partition_table
partition by range(deptno) (
partition p1 values less than(20)
, partition p2 values less than(30)
, partition p3 values less than(40)
)
as
select * from emp ;
create index ptable_empno_idx on partition_table(empno) LOCAL;
|
- 오라클 버전별 지원 파티셔닝
(2) Range 파티셔닝
- 오라클 8버전부터 제공되었으며, 주로 날짜 컬럼을 기준으로 한다.
- 각 레코드를 파티션 키 컬럼에 따라 분할저장하며,
- 레코드 조회시에도 검색조건을 만족하는 파티션만 읽어 조회성능을 높인다.
- 파티션 키는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개까지 허용한다.
- maxvalue 파티션을 설정해야 데이터 입력 불가능한 상황을 막을 수 있다.
| 파티션 테이블 |
|---|
 {code:sql} – Range 파티셔닝 create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) ) partition by range(주문일자) ( partition p2009_q1 values less than('20090401') , partition p2009_q2 values less than('20090701') , partition p2009_q3 values less than('20091001') , partition p2009_q4 values less than('20100101') , partition p2010_q1 values less than('20100401') , partition p9999_mx values less than( MAXVALUE ) ); {code} |
(3) 해시 파티셔닝
- 오라클 8i부터 제공
- 파티션키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장하는 방식
- ID같은 변별력이 좋고 데이터 분포가 고른 컬럼을 선정해야 효과가 높다.
- 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 동작
- 오라클社에서는 파티션 개수는 2의 제곱으로 설정할 것을 권고함(2, 4, 8, 16등)
- 데이터가 모든 파티션에 고르게 분산돼 있다면 별령I/O 성능을 극대화 할 수 있음
- 동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 사용
| 해시 파티셔닝 |
|---|
 {code:sql} -- 해시 파티셔닝 create table 고객( 고객id varchar2(5), 고객명 varchar2(10) ) partition by hash(고객id) partitions 4 ; {code} |
(4) 리스트 파티셔닝
- 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할저장하는 방식
- 사용자는 각 파티션에 값이 고르게 분산되도록 설계해야 한다.
- 단일 컬럼으로만 파티션 키를 지정할 수 있다.
- default 파티션을 생성해 놓아야 데이터 입력 불가능 상황을 막을 수 있다.
| 리스트 파티셔닝 |
|---|
 {code:sql} -- 리스트 파티셔닝 create table 인터넷매물( 물건코드 varchar2(5), 지역분류 varchar2(4) ) partition by list(지역분류) ( partition p_지역1 values ('서울') , partition p_지역2 values ('경기', '인천') , partition p_지역3 values ('부산', '대구', '대전', '광주') , partition p_기타 values (DEFAULT) ) ; {code} |
(5) 결합 파티셔닝
- 주 파티션 키에 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정
| Range + 해시 결합 파티셔닝 |
|---|
 {code:sql} – Range + 해시 파티셔닝 create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) ) partition by range(주문일자) subpartition by hash(고객id) subpartitions 8 ( partition p2009_q1 values less than('20090401') , partition p2009_q2 values less than('20090701') , partition p2009_q3 values less than('20091001') , partition p2009_q4 values less than('20100101') , partition p2010_q1 values less than('20100401') , partition p9999_mx values less than( MAXVALUE ) ); {code} |
| Range + 리스트 결합 파티셔닝 |
|---|
 {code:sql} – Range + 리스트 파티셔닝 create table 판매 ( 판매점 varchar2(10), 판매일자 varchar2(8) ) partition by range(판매일자) subpartition by list(판매점) subpartition template ( subpartition lst_01 values ('강남지점', '강북지점', '강서지점', '강동지점') , subpartition lst_02 values ('부산지점', '대전지점') , subpartition lst_03 values ('인천지점', '제주지점', '의정부지점') , subpartition lst_99 values ( DEFAULT ) ) ( partition p2009_q1 values less than('20090401') , partition p2009_q2 values less than('20090701') , partition p2009_q3 values less than('20091001') , partition p2009_q4 values less than('20100101') ); {code} |
(6) 11g에 추가된 파티션 유형
- Reference 파티셔닝
- 부모-자식간 관계가 있는 테이블에서 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝 하는 방법
- 10g까지는 자식테이블의 반정규화(부모테이블의 파키션 컬럼을 자식테이블에 추가)하는 방식으로 사용
- 파티션 키로 사용할 컬럼에 NOT NULL조건과, FK제약이 설정되어 있어야 한다.
| Reference 파티셔닝 |
|---|
 {code:sql} – Reference 파티셔닝 create table 상품 ( 상품번호 number NOT NULL PRIMARY KEY , 상품명 varchar2(50) not null , 현재가격 number not null , 상품대분류 varchar2(4) not null , 등록일시 date not null ) partition by list(상품대분류) ( partition p1 values ('의류') , partition p2 values ('식품') , partition p3 values ('가전') , partition p4 values ('컴퓨터') ); |
create table 일별상품거래 (
상품번호 number NOT NULL
, 거래일자 varchar2(8)
, 판매가격 number
, 판매수량 number
, 판매금액 number
, constraint 일별상품거래_fk foreign key(상품번호) references 상품
)
partition by reference (일별상품거래_fk);
|
* Interval 파티셔닝
** interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동으로 추가됨
|| Interval 파티셔닝 ||
|{code:sql}
-- Interval 파티셔닝
-- 월에 한 번씩 파티셔닝
create table 주문일자 (주문번호 number, 주문일시 date, ... )
partition by range(주문일시) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
, partition p200907 values less than(to_date('2009/08/01', 'yyyy/mm/dd'))
, partition p200908 values less than(to_date('2009/09/01', 'yyyy/mm/dd'))
, partition p200909 values less than(to_date('2009/10/01', 'yyyy/mm/dd'))
, partition p200910 values less than(to_date('2009/11/01', 'yyyy/mm/dd'))
);
-- 고객이 10만명을 넘을 때마다 파티션이 추가됨
create table 고객 (고객번호 number, 고객명 varchar2(20), ... )
partition by range(고객번호) INTERVAL (100000)
( partition p_cust1 values less than ( 100001 )
, partition p_cust2 values less than ( 200001 )
, partition p_cust3 values less than ( 300001 )
) ;
|
02. 파티션 Pruning
- 'prune'은 '쓸데없는 가지를 치다.', '불필요한 부분을 제거한다'는 뜻임
- 하드파싱이나 실행 시점에 SQL조건절을 분석하여 필요한 파티션 세그먼트만을 읽는다는 뜻
(1) 기본 파티션 Pruning
- 정적(static) 파티션 Pruning : 파티션 키 컬럼을 상수 조건으로 조회하는 경우, 액세스할 파티션이 쿼리 최적화 시점에 결정
- 동적(dynamic) 파티션 Prunig : 파티션 키 컬럼을 바인드 변수로 조회하는 경우
| 파티션 Pruning기능에 따른 실행계획 비교 |
|---|
| {code: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 ; |
-- 상수 사용 : 정적 Pruning
explain plan for
select count(*) from t where no between 30 and 50;
@?/rdbms/admin/utlxpls
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--
| 0 | SELECT STATEMENT | 1 | 13 | 383 (21) | 00:00:05 | |||
| 1 | SORT AGGREGATE | 1 | 13 | |||||
| 2 | PARTITION RANGE ITERATOR | 199K | 2538K | 383 (21) | 00:00:05 | 3 | 5 | |
| TABLE ACCESS FULL | T | 199K | 2538K | 383 (21) | 00:00:05 | 3 | 5 |
--
-- 바인드 변수 사용 : 동적 Pruning
explain plan for
select count(*) from t where no between 30 and 50;
@?/rdbms/admin/utlxpls
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
---
| 0 | SELECT STATEMENT | 1 | 13 | 1026 (2) | 00:00:13 | |||
| 1 | SORT AGGREGATE | 1 | 13 | |||||
| FILTER | |||||||
| 3 | PARTITION RANGE ITERATOR | 2634 | 34242 | 1026 (2) | 00:00:13 | KEY | KEY | |
| TABLE ACCESS FULL | T | 2634 | 34242 | 1026 (2) | 00:00:13 | KEY | KEY |
---
– IN-List 조건을 사용해도 KEY(I)형태로 표시
explain plan for
select count(*) from t where no in (30, 50);
@?/rdbms/admin/utlxpls
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
| 0 | SELECT STATEMENT | 1 | 13 | 260 (22) | 00:00:04 | |||
| 1 | SORT AGGREGATE | 1 | 13 | |||||
| 2 | PARTITION RANGE INLIST | 22138 | 281K | 260 (22) | 00:00:04 | KEY(I) | KEY(I) | |
| TABLE ACCESS FULL | T | 22138 | 281K | 260 (22) | 00:00:04 | KEY(I) | KEY(I) |
create table n
as
select level no
from dual
connect by level <= 100;
– NL조인에서도 동적 Pruning이 일어남
explain plan for
select /*+ leading(n) use_nl(t) */ *
from n, t
where t.no = n.no;
@?/rdbms/admin/utlxpls
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--
| 0 | SELECT STATEMENT | 1053K | 46M | 10202 (1) | 00:02:03 | |||
| 1 | NESTED LOOPS | 1053K | 46M | 10202 (1) | 00:02:03 | |||
| 2 | TABLE ACCESS FULL | N | 100 | 1300 | 3 (0) | 00:00:01 | ||
| 3 | PARTITION RANGE ITERATOR | 10534 | 339K | 102 (1) | 00:00:02 | KEY | KEY | |
| TABLE ACCESS FULL | T | 10534 | 339K | 102 (1) | 00:00:02 | KEY | KEY |
--
-- 결합 파티션일 때 Pruning
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;
– Range파티션은 3 ~ 5번(3개), 해시파티션은 각 Range파티션당 16개씩 읽어 총 48개(=3*16)의 파티션을 읽었다.
explain plan for
select count(*) from t where no between 30 and 50;
@?/rdbms/admin/utlxpls
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--
| 0 | SELECT STATEMENT | 1 | 13 | 1010 (34) | 00:00:13 | |||
| 1 | SORT AGGREGATE | 1 | 13 | |||||
| 2 | PARTITION RANGE ITERATOR | 484K | 6146K | 1010 (34) | 00:00:13 | 3 | 5 | |
| 3 | PARTITION HASH ALL | 484K | 6146K | 1010 (34) | 00:00:13 | 1 | 16 | |
| TABLE ACCESS FULL | T | 484K | 6146K | 1010 (34) | 00:00:13 | 33 | 80 |
--
-- 바인드 변수를 사용할 경우 Range 파티션은 목록을 확정할 수 없어 KEY라고 표시되나,
-- 해시 파티션은 조건절을 사용하지 않아 16개 모두 읽는 것을 확인 할 수 있다.
explain plan for
select count(*) from t where no between :a and :b;
@?/rdbms/admin/utlxpls
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
---
| 0 | SELECT STATEMENT | 1 | 13 | 235 (5) | 00:00:03 | |||
| 1 | SORT AGGREGATE | 1 | 13 | |||||
| FILTER | |||||||
| 3 | PARTITION RANGE ITERATOR | 2627 | 34151 | 235 (5) | 00:00:03 | KEY | KEY | |
| 4 | PARTITION HASH ALL | 2627 | 34151 | 235 (5) | 00:00:03 | 1 | 16 | |
| TABLE ACCESS FULL | T | 2627 | 34151 | 235 (5) | 00:00:03 | KEY | KEY |
---
|
|| 파티션 Pruning 기능에 따른 I/O 수행량 비교 ||
|{code:sql}
-- 주 파티션과 서브 파티션에 Pruning이 작동하여 총 50개의 블럭을 읽음
select * from t where no = 1 and key = '000100'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 4 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 50 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 54 1 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=50 pr=0 pw=0 time=0 us cost=16 size=33 card=1)
1 PARTITION HASH SINGLE PARTITION: 6 6 (cr=50 pr=0 pw=0 time=0 us cost=16 size=33 card=1)
1 TABLE ACCESS FULL T PARTITION: 6 6 (cr=50 pr=0 pw=0 time=0 us cost=16 size=33 card=1)
-- 서브 파티션 키 컬럼을 함수로 가공하고 수행 시 16개의 파티션에서 777개의 블록을 읽음
select * from t where no = 1 and to_number(key) = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 40 12 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 631 777 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 631 817 12 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=777 pr=631 pw=0 time=0 us cost=322 size=5676 card=172)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=777 pr=631 pw=0 time=0 us cost=322 size=5676 card=172)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=777 pr=631 pw=0 time=0 us cost=322 size=5676 card=172)
-- 묵시적 형변환이 일어나는 경우에도 16개의 파티션에서 777개의 블록을 읽는 것을 확인 할 수 있음.
select * from t where no = 1 and key = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 16 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 777 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 793 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=777 pr=0 pw=0 time=0 us cost=322 size=5676 card=172)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=777 pr=0 pw=0 time=0 us cost=322 size=5676 card=172)
1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=777 pr=0 pw=0 time=0 us cost=322 size=5676 card=172)
-- 주파티션 키 컬럼을 함수로 가공했을 때, 160개의 파티션에서 4137개의 블록을 읽는 것을 확인할 수 있음.
select * from t where to_char(no) = '1' and key = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 32 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.18 0.18 2856 4137 11 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.18 0.18 2856 4169 11 1
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION RANGE ALL PARTITION: 1 10 (cr=4137 pr=2856 pw=0 time=0 us cost=235 size=3465 card=105)
1 PARTITION HASH ALL PARTITION: 1 16 (cr=4137 pr=2856 pw=0 time=0 us cost=235 size=3465 card=105)
1 TABLE ACCESS FULL T PARTITION: 1 160 (cr=4137 pr=2856 pw=0 time=0 us cost=235 size=3465 card=105)
|
(2) 서브쿼리 Pruning
- 조인에 사용되는 고급 파티션 Pruning
- 서브쿼리 Pruning(8i ~ )
- 조인필터 Pruning (11g ~ )
| 서브쿼리 Pruning이 작동하지 않는 경우 |
|---|
| {code:sql} |
create table dept as select * from scott.dept;
create table emp
partition by range (deptno)
(partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (MAXVALUE))
as
select * from scott.emp ;
– default, 데이터를 조회할 때의 비용이 1/20 (5%)를 초과하지 않을 때만 서브쿼리 Pruning을 사용하겠다는 의미
alter session set "_subquery_pruning_cost_factor" = 20;
– default, 드라이빙 테이블 전체 건수의 50%를 넘지 않아야 한다는 뜻
alter session set "_subquery_pruning_reduction" = 50;
set autotrace traceonly exp;
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 | |||
| HASH JOIN | 5 | 585 | 8 (13) | 00:00:01 | |||
| TABLE ACCESS FULL | DEPT | 1 | 30 | 3 (0) | 00:00:01 | ||
| 3 | PARTITION RANGE ALL | 14 | 1218 | 4 (0) | 00:00:01 | 1 | 4 | |
| 4 | TABLE ACCESS FULL | EMP | 14 | 1218 | 4 (0) | 00:00:01 | 1 | 4 |
-
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 | |||
| HASH JOIN | 5 | 585 | 8 (13) | 00:00:01 | |||
| 2 | PARTITION RANGE ALL | 14 | 1218 | 4 (0) | 00:00:01 | 1 | 4 | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 1218 | 4 (0) | 00:00:01 | 1 | 4 |
| TABLE ACCESS FULL | DEPT | 1 | 30 | 3 (0) | 00:00:01 |
-
|
|| 서브쿼리 Pruning이 작동하는 경우 ||
|{code:sql}
-- 서브쿼리 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이 적용됐다.
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 260 | 9 (12)| 00:00:01 | | |
|* 1 | HASH JOIN | | 5 | 260 | 9 (12)| 00:00:01 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 1 | 18 | 3 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | DEPT | 1 | 18 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE JOIN-FILTER| | 14 | 476 | 5 (0)| 00:00:01 |:BF0000|:BF0000|
| 5 | TABLE ACCESS FULL | EMP | 14 | 476 | 5 (0)| 00:00:01 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
3 - filter("D"."LOC"='DALLAS')
|
(3) 조인 필터 Pruning(책 648 ~ 651 페이지 참조)
- 블룸필터 알고리즘
- 집합 A = 175, 442, 618
- 집합 B = 175, 327, 432, 548
- 0번부터 9번까지 10개 비트를 할당하고 모두 0으로 설정
- 0부터 9까지 총 10개의 값을 리턴할 수 있는 두개의 함수를 정의(두 함수는 서로 다른 값을 리턴)
- 집합 A의 각 원소마다 차례로 두개의 함수를 적용하고, 각 해시 함수에서 리턴된 값에 해당하는 비트를 모두 1로 설정
- 집합 B의 각 원소마다 차례로 두개의 함수를 적용하고, 리턴된 값에 해당하는 비트를 모두 확인, 하나라도 0으로 설정돼 있으며, 검색에서 제외, 모두 있으면 집합A에 실제 있는지 확인
- 블룸 피터 알고리즘은 불필요한 검색을 제거하는 방식으로 일량을 줄임
- 더 많은 비트를 할당(더 많은 공간필요)거나, 더 많은 해시 함수를 사용(더 많은 시간 소비)하여 불필요한 검색을 더 많이 줄일 수 있음 |
| 조인 필터(=블룸 필터) Pruning |
|---|
 |
(4) SQL 조건절 작성 시 주의사항
| 검색조건에 따른 파티션 조회 개수차이 |
|---|
| {code:sql} 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; |
set autotrace traceonly exp;
– like 검색을 할 경우 9 ~ 10번 2개의 파티션을 읽는다.
select * from 고객
where 가입일 like '200910%';
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
-
| 0 | SELECT STATEMENT | 31 | 62651 | 4 (0) | 00:00:01 | |||
| 1 | PARTITION RANGE ITERATOR | 31 | 62651 | 4 (0) | 00:00:01 | 9 | 10 | |
| TABLE ACCESS FULL | 고객 | 31 | 62651 | 4 (0) | 00:00:01 | 9 | 10 |
-
– between 검색을 할 경우 10번 1개의 파티션만을 읽는다.
select * from 고객
where 가입일 between '20091001' and '20091031';
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
---
| 0 | SELECT STATEMENT | 31 | 62651 | 3 (0) | 00:00:01 | |||
| 1 | PARTITION RANGE SINGLE | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 | |
| TABLE ACCESS FULL | 고객 | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 |
---
-- 쿼리 수정 대신 파티션닝을 월단위로 바꿔 Like 검색도 효율적으로 사용할 수 있다.
create table 고객
partition by range(가입일)
( partition m01 values less than('200902')
, partition m02 values less than('200903')
, partition m03 values less than('200904')
, partition m04 values less than('200905')
, partition m05 values less than('200906')
, partition m06 values less than('200907')
, partition m07 values less than('200908')
, partition m08 values less than('200909')
, partition m09 values less than('200910')
, partition m10 values less than('200911')
, partition m11 values less than('200912')
, partition m12 values less than('201001'))
as
select rownum 고객ID
, dbms_random.string('a', 20) 고객명
, to_char(to_date('20090101', 'yyyymmdd') + (rownum - 1), 'yyyymmdd') 가입일
from dual
connect by level <= 365;
– Like 검색을 사용했음에도 10번 파티션만을 읽었다.
select * from 고객
where 가입일 like '200910%';
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
---
| 0 | SELECT STATEMENT | 31 | 62651 | 3 (0) | 00:00:01 | |||
| 1 | PARTITION RANGE SINGLE | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 | |
| TABLE ACCESS FULL | 고객 | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 |
---
– Between 검색은 이번에도 10번 파티션만을 읽었다. 저자는 between 검색을 강력추천한다.
select * from 고객
where 가입일 between '20091001' and '20091031';
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
---
| 0 | SELECT STATEMENT | 31 | 62651 | 3 (0) | 00:00:01 | |||
| 1 | PARTITION RANGE SINGLE | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 | |
| TABLE ACCESS FULL | 고객 | 31 | 62651 | 3 (0) | 00:00:01 | 10 | 10 |
---
|
h3. 03. 인덱스 파티셔닝
h4. (1) 인덱스 파티션 유형
!인덱스1.png!
* 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션 인덱스를 가질 수 있다.
* 파티션 테이블은 비파티션 인덱스, 글로벌 파티션 인덱스, 로컬 파티션 인덱스를 가질 수 있다.
h4. (2) 로컬 파티션 인덱스
!그림6-8.png!
* 테이블 파티션과 인덱스 파티션이 서로 1:1관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스
* 테이블 파티션 구성에 변경(drop, exchange, split 등)이 생기더라도 인덱스를 재생성할 필요가 없다.
h4. (3) 비파티션 인덱스
!그림6-9.png!
* 파티셔닝하지 않은 인덱스
* 테이블이 파티셔닝돼 있으면, 1:M관계가 됨
* 글로벌 비파티션 인덱스라고도 함
* 제약사항 : 기준 테이블의 파티션 구성에 변경(drop, exchange, split 등)이 생길 때마다 인덱스가 unusable 상태로 바뀜
h4. (4) 글로벌 파티션 인덱스
!그림6-10.png!
* 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것을 말함.
* 테이블은 파티셔닝돼 있지 않을 수도 있음.
* 제약사항 : 기준 테이블의 파티션 구성에 변경(drop, exchange, split 등)이 생길 때마다 인덱스가 unusable 상태로 바뀜
* 테이블 파티션과의 관계
** 오라클이 자동으로 관리주는 1:1관계가 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스이다.
** 인덱스를 테이블 파티션과 같은 키 컬럼으로 글로벌 파티셔닝한다면 파티션 기준 값을 어떻게 정의하느냐에 따라 1:M, M:1, M:M관계가 모두 가능
** 로컬 파티션 인덱스 처럼 테이블과 1:1관계가 되도록 수동으로 구성하더라도, 여느 글로벌 파티션과 마찬가지로 기준 테이블 구성에 변경이 발생할 때마다 인덱스 재생성이 필요
* 글로벌 해시 파티션 인덱스 : Hot 블록이 발생하는 인덱스의 경합을 분산할 목적으로 생성
h4. (5) Prefixed vs Nonprefixed
* Prefixed : 파티션 인덱스를 생성 시, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것
* NonPrefixed : 파티션 키 컬럼을 왼쪽 선두에 두지 않거나, 아예 없는 경우
!인덱스2.png!
* 인덱스 유형
** 비파티션 인덱스
** 글로벌 Prefixed 파티션 인덱스
** 로컬 Prefixed 파티션 인덱스
** 로컬 Nonprefixed 파티션 인덱스
h4. (6) 파티션 인덱스 구성 예시
!인덱스4.png!
|| 인덱스 파티셔닝 예제 ||
|{code:sql}
create table t ( gubun
, seq, seq_name, seq_cls
, seq2, seq2_name, seq2_cls
)
partition by range(seq) (
partition p1 values less than(100)
, partition p2 values less than(200)
, partition p3 values less than(300)
, partition p4 values less than(maxvalue)
)
as
select 1
, rownum, dbms_random.string('u', 10), 'A'
, rownum, dbms_random.string('l', 10), 'B'
from dual
connect by level <= 400 ;
-- 유니크 인덱스 생성시에는 파티션키 컬럼이 인덱스 컬럼에 포함되어야 한다.
create unique index t_idx1 on t(gubun, seq2) LOCAL ;
create unique index t_idx1 on t(gubun, seq2) LOCAL
*
1행에 오류:
ORA-14039: 열을 분할영역한 것은 UNIQUE 인덱스로 키 열의 부분 집합을 폼 합니다
-- 비파티션 인덱스는 에러없이 잘 만들어진다.
SQL> create unique index t_idx1 on t(gubun, seq2) ;
인덱스가 생성되었습니다.
-- seq가 포함되어 에러없이 생성됨 : 로컬 Nonprefixed 파티션 인덱스
SQL> create unique index t_idx2 on t(gubun, seq) LOCAL ;
인덱스가 생성되었습니다.
-- seq가 왼쪽 선두에 존재, 로컬 Prefixed 파티션 인덱스
SQL> create index t_idx3 on t(seq, gubun) LOCAL ;
인덱스가 생성되었습니다.
-- 로컬 Nonprefixed 파티션 인덱스
SQL> create index t_idx4 on t(seq_name, seq) LOCAL ;
인덱스가 생성되었습니다.
-- 글로벌 파티션 인덱스는 Nonprefixed 가 허용되지 않는다.
create index t_idx5 on t(seq_cls, seq) GLOBAL
partition by range(seq) (
partition p1 values less than(100)
, partition p2 values less than(200)
, partition p3 values less than(300)
, partition p4 values less than(maxvalue)
)
;
partition by range(seq) (
*
2행에 오류:
ORA-14038: GLOBAL로 분할영역된 인덱스는 접두사이어야 합니다
-- 정상적으로 글로벌 Prefixed 파티션 인덱스가 생성됨
create index t_idx5 on t(seq, seq_cls) GLOBAL
partition by range(seq) (
partition p1 values less than(100)
, partition p2 values less than(200)
, partition p3 values less than(300)
, partition p4 values less than(maxvalue)
) ;
인덱스가 생성되었습니다.
-- 글로벌 Prefixed 파티션 인덱스 : 각 인덱스 파티션이 두 개 테이블 파티션과 매핑된다.(1:M)
create index t_idx6 on t(seq, seq_name) GLOBAL
partition by range(seq) (
partition p1 values less than(200)
, partition p2 values less than(maxvalue)
) ;
인덱스가 생성되었습니다.
-- 글로벌 Prefixed 파티션 인덱스 : 두개의 인덱스 파티션이 1개의 테이블 파티션과 매핑된다.(M:1)
create index t_idx7 on t(seq, seq_name, seq_cls) GLOBAL
partition by range(seq) (
partition p1 values less than(50)
, partition p2 values less than(100)
, partition p3 values less than(150)
, partition p4 values less than(200)
, partition p5 values less than(250)
, partition p6 values less than(300)
, partition p7 values less than(350)
, partition p8 values less than(maxvalue)
) ;
인덱스가 생성되었습니다.
-- 테이블 파티션 키와 다른 키로 인덱스 파티션 키를 설정하였다. 인덱스 파티션과 테이블 파티션은 M:M관계로 매핑된다.
create index t_idx8 on t(seq2) GLOBAL
partition by range(seq2) (
partition p1 values less than(100)
, partition p2 values less than(200)
, partition p3 values less than(300)
, partition p4 values less than(maxvalue)
) ;
인덱스가 생성되었습니다.
-- 파티션 정보 조회
select i.index_name, i.uniqueness, p.locality
, p.alignment, i.partitioned, p.partition_count
from user_indexes i, user_part_indexes p
where i.table_name = 'T'
and p.table_name(+) = i.table_name
and p.index_name(+) = i.index_name
order by 1 ;
INDEX_NAME UNIQUENES LOCALI ALIGNMENT PAR PARTITION_COUNT
------------------------------ --------- ------ ------------ --- ---------------
T_IDX1 UNIQUE NO
T_IDX2 UNIQUE LOCAL NON_PREFIXED YES 4
T_IDX3 NONUNIQUE LOCAL PREFIXED YES 4
T_IDX4 NONUNIQUE LOCAL NON_PREFIXED YES 4
T_IDX5 NONUNIQUE GLOBAL PREFIXED YES 4
T_IDX6 NONUNIQUE GLOBAL PREFIXED YES 2
T_IDX7 NONUNIQUE GLOBAL PREFIXED YES 8
T_IDX8 NONUNIQUE GLOBAL PREFIXED YES 4
8 개의 행이 선택되었습니다.
|
(7) 글로벌 파티션 인덱스의 효용성
- 경합을 분산시키려고 글로벌 해시 파티셔닝하는 경우외에는 거의 사용되지 않음
- 차라리 글로벌 비파티션 인덱스를 만드는 것이 더 좋다.
(8) 로컬 Nonprefixed 파티션 인덱스의 효용성
| 로컬 Prefixed 파티션 인덱스와 비교 |
|---|
 {code:sql} create table 일별계좌별거래 ( 계좌번호 number , 거래일자 date , 거래량 number , 거래금액 number ) partition by range(거래일자)( partition p01 values less than(to_date('20090201', 'yyyymmdd')) , partition p02 values less than(to_date('20090301', 'yyyymmdd')) , partition p03 values less than(to_date('20090401', 'yyyymmdd')) , partition p04 values less than(to_date('20090501', 'yyyymmdd')) , partition p05 values less than(to_date('20090601', 'yyyymmdd')) , partition p06 values less than(to_date('20090701', 'yyyymmdd')) , partition p07 values less than(to_date('20090801', 'yyyymmdd')) , partition p08 values less than(to_date('20090901', 'yyyymmdd')) , partition p09 values less than(to_date('20091001', 'yyyymmdd')) , partition p10 values less than(to_date('20091101', 'yyyymmdd')) , partition p11 values less than(to_date('20091201', 'yyyymmdd')) , partition p12 values less than(maxvalue) ) ; |
declare
l_first_date date;
l_last_day number;
begin
for i in 1..12
loop
l_first_date := to_date('2009' || lpad(i, 2, '0') || '01', 'yyyymmdd');
l_last_day := to_number(to_char(last_day(l_first_date), 'dd'));
insert into 일별계좌별거래
select rownum 계좌번호
, l_first_date + mod(rownum, l_last_day) 거래일자
, round(dbms_random.value(100, 10000)) 거래량
, round(dbms_random.value(10000, 1000000)) 거래금액
from dual
connect by level <= 10000;
end loop;
end;
/
create index local_prefix_index on 일별계좌별거래(거래일자, 계좌번호) local;
create index local_nonprefix_index on 일별계좌별거래(계좌번호, 거래일자) local;
-- 로컬 prefix 파티션 인덱스를 사용한 경우, 읽어들일 블록수가 387개이다.
select /*+ index(t local_prefix_index) */ sum(거래량), sum(거래금액)
from 일별계좌별거래 t
where 계좌번호 = 100
and 거래일자 between to_date('20090115', 'yyyymmdd')
and to_date('20091215', 'yyyymmdd')
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 387 0 1
---
--
--
--
--
--
--
total 4 0.01 0.02 0 387 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=387 pr=0 pw=0 time=0 us)
11 PARTITION RANGE ALL PARTITION: 1 12 (cr=387 pr=0 pw=0 time=5320 us cost=153 size=528 card=11)
11 TABLE ACCESS BY LOCAL INDEX ROWID 일별계좌별거래 PARTITION: 1 12 (cr=387 pr=0 pw=0 time=0 us cost=153 size=528 card=11)
11 INDEX RANGE SCAN LOCAL_PREFIX_INDEX PARTITION: 1 12 (cr=376 pr=0 pw=0 time=0 us cost=6 size=0 card=1)(object id 77440)
-- 로컬 Nonprefix 파티션 인덱스를 사용한 경우, 읽어들인 블록수가 35개이다.
select /*+ index(t local_nonprefix_index) */ sum(거래량), sum(거래금액)
from 일별계좌별거래 t
where 계좌번호 = 100
and 거래일자 between to_date('20090115', 'yyyymmdd')
and to_date('20091215', 'yyyymmdd')
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 35 0 1
---
--
--
--
--
--
--
total 4 0.00 0.00 0 35 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=35 pr=0 pw=0 time=0 us)
11 PARTITION RANGE ALL PARTITION: 1 12 (cr=35 pr=0 pw=0 time=360 us cost=22 size=528 card=11)
11 TABLE ACCESS BY LOCAL INDEX ROWID 일별계좌별거래 PARTITION: 1 12 (cr=35 pr=0 pw=0 time=0 us cost=22 size=528 card=11)
11 INDEX RANGE SCAN LOCAL_NONPREFIX_INDEX PARTITION: 1 12 (cr=24 pr=0 pw=0 time=0 us cost=17 size=0 card=1)(object id 77453)
|
|| 글로벌 Prefixed 파티션 인덱스와 비교 ||
| !그림6-12.png!|
* 과거 파티션을 제거하고, 신규 파티션을 추가하는 등의 파티션 단위 작업시 매번 인덱스를 재생성해야 한다.(관리비용 상승)
* 비파티션 인덱스의 관리적인 부담은 글로벌 파티션과 동일하다(파티션 단위 작업 시 매번 인덱스 재생성 필요)
h4. (9) 액세스 효율을 고려한 인덱스 파티셔닝 선택 기준
* DW성 애플리케이션 환경 : 날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 많으므로, 로컬 파티션 인덱스가 좋은 선택이다.
* OLTP성 애플리케이션 환경 : 비파티션 인덱스가 대개 좋은 선택이다. 테이블이 파티션 되어 있는 경우라면, 로컬 인덱스의 Prefixed 파티션이든 Nonprefixed 파티션이든 검색 조건에 항상 사용되는 컬럼(대개 날짜 컬럼)을 파티션 키로 선정하는 게 더 중요하다.
h4. (10) 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계
* Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼야 한다.
* 글로벌 파티션 인덱스는 모두 Prefixed 파티션이다.
* 슈퍼/서브 타입 관계로 모델링할 경우, 구분자 컬럼을 물리 설계 단계에서 PK컬럼에 포함시키는 것이 좋다.