오라클 성능 고도화 원리와 해법 II (2012년)
03.인덱스 파티셔닝
(1) 인덱스 파티션 유형
- 비파티션 인덱스(Non-Partitioned Index)
- 글로벌 파티션 인덱스(Global Partitioned Index)
- 로컬 파티션 인덱스(Local Partitioned Index)
 |
- 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션 인덱스를 가질 수 있다.
- 파티션 테이블은 비파티션 인덱스, 글로벌 파티션 인덱스, 로컬 파티션 인덱스를 가질 수 있다.
- 비파티션 테이블에 대한 비트맵 인덱스는 파티셔닝이 허용되지 않고, 파티션 테이블에 대한 비트맵 인덱스는 로컬 파티셔닝만 허용한다.
(2) 로컬 파티션 인덱스
- 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다. (p.659 그림 6-8 참고)
- 파티션 키를 사용자가 따로 정의하지 않아도 오라클이 자동으로 관리
- 테이블 파티션과 1:1 관계가 되도록 사용자가 수동으로 인덱스 파티션을 구성하더라도 이를 로컬 파티션 인덱스라고 부르지 않는다.
- 테이블이 결합 파티셔닝 돼 있다면 인덱스도 같은 단위로 파티셔닝
- 테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없다.
(3) 비파티션 인덱스
- 파티셔닝하지 않은 인덱스
- 1:M 관계 (p.660 그림 6-9 참고)
- 하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계
- '글로벌 비파티션 인덱스'라 부르기도 한다.
(4) 글로벌 파티션 인덱스
- 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것.(p.660 그림 6-10 참고)
- 테이블은 파티셔닝돼 있지 않을 수도 있다.
- 기준 테이블의 파티션 구성에 변경(drop, exchange, split등)이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 인덱스를 재생성해야 한다. (비파티션 인덱스일 때도 동일)
- 9i부터 update global indexes 옵션을 주면 파티션 DDL 작업에 의해 영향을 받는 인덱스 레코드를 자동으로 갱신해 준다. (unusable 상태로 빠지지 않는다)
alter table ...
split partition ...
update global indexes;
- 파티션 DDL로 인해 영향 받는 레코드가 전체의 5% 미만일 때만 유용
테이블 파티션과의 관계
- "오라클이 자동으로 관리해 주는 1:1 관계"가 아닌 파티션 인덱스는 모두 글로벌 파티션 인덱스
- 하나의 인덱스 파티션이 여러 테이블 파티션과 관계를 갖고, 반대로 하나의 테이블 파티션이 여러 인덱스 파티션과 관계를 갖는다.
- 로컬 파티션 인덱스처럼 테이블과 1:1 관계가 되도록 수동으로 구성하더라도 글로벌 파티션과 마찬가지로 기준 테이블 구성에 변경이 발생할 때마다 인덱스를 재생성해야 한다.
- 인덱스를 테이블 파티션과 다른 키 컬럼으로 글로벌 파티셔닝(테이블은 주문일자, 인덱스는 배송일자)할 수도 있는데, 이때는 테이블 파티션과 인덱스 파티션 간에는 항상 M:M 관계가 형성.
글로벌 해지 파티션 인덱스
- 9i까지는 글로벌 Range 파티션만 가능
- 10g부터는 글로벌 해시 파티션도 가능.
- 테이블과 독립적으로 인덱스만 해시 키 값에 따라 파티셔닝할 수 있다.
- 글로벌 해시 파티션 인덱스는 Right Growing 인덱스처러 Hot 블록이 발생하는 인덱스의 경?을 분산할 목적으로 주로 사용
- 글로벌 결합 인덱스 파티셔닝은 여전히 불가능
(5) Prefixed vs. Nonprefixed
- 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 위치하는지에 따른 구분
- Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것
- Nonprefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 주지 않는 것. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.
- 글로벌 파티션 인덱스는 Prefixed 파티션만 지원
인덱스 유형
- 비파티션 인덱스
- 글로벌 Prefixed 파티션 인덱스
- 로컬 Prefixed 파티션 인덱스
- 로컬 Nonprefixed 파티션 인덱스
(6) 파티션 인덱스 구성 예시
 |
인덱스 파티셔닝 예제
SQL >
l create table t ( gubun
2 , seq, seq_name, seq_cls
3 , seq2, seq2_name, seq2_cls
4 )
5 partition by range(seq) (
6 partition p1 values less than(100)
7 , partition p2 values less than(200)
8 , partition p3 values less than(300)
9 , partition p4 values less than(maxvalue)
10 )
11 as
12 select 1
13 , rownum, dbms_random.string('u', 10), 'A'
14 , round(dbms_random.value(1, 400)), dbms_random.string('l', 10), 'B'
15 from dual
16 connect by level <= 400 ;
테이블이 생성되었습니다.
- seq 컬럼 기준으로 Range 파티셔닝
SQL >
l create unique index t_idx1 on t(gubun, seq2) LOCAL ;
create unique index t_idx1 on t(gubun, seq2) LOCAL
*
1행에 오류:
ORA-14039: 열을 분할영역한 것은 UNIQUE 인덱스로 키 열의 부분 집합을 폼 합니다
- Unique 파티션 인덱스를 만들 때는 파티션 키 컬럼이 인덱스 컬럼에 포함돼 있어야 한다.
SQL >
l create unique index t_idx1 on t(gubun, seq2) ;
인덱스가 생성되었습니다.
- 비파티션 인덱스를 만들 때는 그런 제약이 없다.
SQL >
l create unique index t_idx2 on t(gubun, seq) LOCAL ;
인덱스가 생성되었습니다.
- 파티션 키를 인덱스 구성 컬럼에 포함시켜 unique 인덱스 생성
SQL >
l create index t_idx3 on t(seq, gubun) LOCAL ;
인덱스가 생성되었습니다.
- 로컬 prefixed 파티션 인덱스 생성
SQL >
l create index t_idx4 on t(seq_name, seq) LOCAL ;
인덱스가 생성되었습니다.
- 로컬 Nonprefixed 파티션 인덱스 생성
SQL >
l create index t_idx5 on t(seq_cls, seq) GLOBAL
2 partition by range(seq) (
3 partition p1 values less than(100)
4 , partition p2 values less than(200)
5 , partition p3 values less than(300)
6 , partition p4 values less than(maxvalue)
7 )
8 ;
partition by range(seq) (
*
2행에 오류:
ORA-14038: GLOBAL로 분할영역된 인덱스는 접두사이어야 합니다
- 글로벌 파티션 인덱스에는 Nonprefixed 인덱스가 허용되지 않는다.
SQL >
l create index t_idx5 on t(seq, seq_cls) GLOBAL
2 partition by range(seq) (
3 partition p1 values less than(100)
4 , partition p2 values less than(200)
5 , partition p3 values less than(300)
6 , partition p4 values less than(maxvalue)
7 ) ;
인덱스가 생성되었습니다.
- 글로벌 Prefixed 파티션 인덱스 생성
- 테이블 파티션과 100% 같게 정의하더라도 이를 '로컬 파티션 인덱스'라고 부르지 않는다.
SQL >
l create index t_idx6 on t(seq, seq_name) GLOBAL
2 partition by range(seq) (
3 partition p1 values less than(200)
4 , partition p2 values less than(maxvalue)
5 ) ;
인덱스가 생성되었습니다.
- 각 인덱스 파티션이 두 개 테이블 파티션과 매핑
SQL >
l create index t_idx7 on t(seq, seq_name, seq_cls) GLOBAL
2 partition by range(seq) (
3 partition p1 values less than(50)
4 , partition p2 values less than(100)
5 , partition p3 values less than(150)
6 , partition p4 values less than(200)
7 , partition p5 values less than(250)
8 , partition p6 values less than(300)
9 , partition p7 values less than(350)
10 , partition p8 values less than(maxvalue)
11 ) ;
인덱스가 생성되었습니다.
- 두 개 인덱스 파티션이 한 개 테이블 파티션과 매핑되는 구조
SQL >
l create index t_idx8 on t(seq2) GLOBAL
2 partition by range(seq2) (
3 partition p1 values less than(100)
4 , partition p2 values less than(200)
5 , partition p3 values less than(300)
6 , partition p4 values less than(maxvalue)
7 ) ;
인덱스가 생성되었습니다.
- 테이블과 다른 컬럼으로 파티셔닝할 때는 항상 M:M 관계 형성
SQL >
l select i.index_name, i.uniqueness, p.locality
2 , p.alignment, i.partitioned, p.partition_count
3 from user_indexes i, user_part_indexes p
4 where i.table_name = 'T'
5 and p.table_name(+) = i.table_name
6 and p.index_name(+) = i.index_name
7 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) 글로벌 파이션 인덱스의 효용성
- 글로벌 파티션 인덱스는 경합을 분산시키려고 글로벌 해시 파티셔닝하는 경우외에는 거의 사용하지 않는 실정이다.
- 테이블을 파티셔닝하지 않을 정도로 중소형급 테이블이면 굳이 인덱스만을 따로 파티셔닝 할 이유는 없다.
파티션 테이블에 대한 글로벌 파티션 인덱스'의 효용성
1) 테이블과 같은 컬럼으로 파티셔닝하는 경우
- 테이블은 날짜 컬럼 기준으로 월별 파티셔닝하고, 인덱스는 분기별 파티셔닝하는 경우
- 글로벌 파티션 인덱스에는 Prefixed 파티션만 허용되므로 날짜 컬럼을 선두로 둬야 한다.
- 날짜 조건은 대개 범위검색 조건이 사용되므로 인덱스 스캔 효율 면에서 불리하다.
- NL 조인에서 Inner 테이블 액세스를 위해 자주 사용되는 인덱스라면 비효율이 더 크게 작용.
- 다른 '=' 조건 컬럼을 선두에 둘 수 있다는 측면에선 로컬 Nonprefixed 파티션 인덱스가 유리
- 두 달 이상의 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 사용될 때는 로컬 Nonprefixed 파티션 인덱스에도 비효율이 발생(조인 액세스가 일어나는 레코드마다 여러 인덱스 파티션을 탐색해야 하기 때문)
- NL 조인에서 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 자주 사용된다면 비파티션 인덱스가 가장 좋은 선택.
2) 테이블과 다른 컬럼으로 파티셔닝하는 경우
- 테이블 파티션 기준인 날짜 이외 컬럼으로 인덱스를 글로벌 파티셔닝 할 수 있는데, 그런 구성은 대개 인덱스를 적정 크기로 유지하려는 데에 목적.
- 로컬 파티션 인덱스 때문에 무색해진다.
(8) 로컬 Nonprefixed 파티션 인덱스의 효율성
- 로컬 Nonprefixed 파티션 인덱스는 이력성 데이터를 효과적으로 관리할 수 있게 해 주고, 인덱스 스캔 효율성을 높이는 데에도 유리.
- 로컬 Prefixed 파티션 인덱스와 비교* (p.699 그림 6-11 참고)
- 로컬 Prefixed 파티션 인덱스는 계좌번호 조건을 만족하지 않는 거래 데이터까지 모두 스캔
- 로컬 Nonprefixed 파티션 인득스는 각 인덱스 파티션마다 필요한 최소 범위만 스캔.
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;
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 Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.023 0 387 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.023 0 387 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: KJWON (ID=64)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=387 pr=0 pw=0 time=22846 us)
11 PARTITION RANGE ALL PARTITION: 1 12 (cr=387 pr=0 pw=0 time=16366 us)
11 TABLE ACCESS BY LOCAL INDEX ROWID 일별계좌별거래 PARTITION: 1 12 (cr=387 pr=0 pw=0 time=22704 us)
11 INDEX RANGE SCAN LOCAL_PREFIX_INDEX PARTITION: 1 12 (cr=376 pr=0 pw=0 time=8009 us)(Object ID 52990)
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 Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.001 0 35 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.001 0 35 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: KJWON (ID=64)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=35 pr=0 pw=0 time=544 us)
11 PARTITION RANGE ALL PARTITION: 1 12 (cr=35 pr=0 pw=0 time=532 us)
11 TABLE ACCESS BY LOCAL INDEX ROWID 일별계좌별거래 PARTITION: 1 12 (cr=35 pr=0 pw=0 time=433 us)
11 INDEX RANGE SCAN LOCAL_NONPREFIX_INDEX PARTITION: 1 12 (cr=24 pr=0 pw=0 time=232 us)(Object ID 53003)
글로벌 Prefixed 파티션 인덱스와 비교
- Prefixed 파티션만 혀용되므로 로컬 Prefixed 파티션과 마찬가지로 인덱스 스캔 효율이 나쁘다.
- 파티션 단위 작업 시 매번 인덱스를 재생성해야 하므로 관리적 부담.
비파티션 인덱스와 비교
- 관리적 부담은 글로벌 파티션과 동일
- 로컬 Nonprefixed 파티션 인덱스는, 두 달 이상의 걸친 넓은 범위의 거래일자 조건으로 조회할 때 여러 인덱스를 (수직적) 탐색해야 하는 비효율이 있다.
- 비파티션 인덱스는 여러 달에 걸친 거래일자로 조회하더라도 인덱스 스캔 상 비효율은 없다.
- 아주 넓은 범위의 거래일자로 조회하거나 계좌번호만으로 조회 할 때는 테이블 Random 액세스 부하 발생.
- 로컬 Nonprefixed 파티션 인덱스는 여러 병렬 프로세스가 각각 하나의 인덱스 세그먼트를 스캔하도록 함으로써 응답속도를 향상시킬 수 있다.
일 단위 파티셔닝
- 테이블이 일파티션되어 있다면, 계좌번호만으로 로컬 Nonprefixed 파티션 인덱스를 생성함으로써 인덱스 저장 공간을 줄이는 효과를 얻을 수 있다. (p.673 그림 6-13 참고)
select 계좌번호, count(*)
from 일별계좌별집계
where 거래일자 between '20090101' and '20090115'
group by 계좌번호;
- 계좌번호만으로 인덱스를 생성하면 거래일자를 읽기 위한 테이블 액세스가 발생하므로 불리
(9) 엑세스 효율을 고려한 인덱스 파티셔닝 선택 기준
DW성 애플리케이션 환경
- 날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 많다.
- 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인덱스가 좋은 선택
- 로컬 Nonprefixed 파티션 인덱스가 성능 면에서 유리
OLTP성 애플리케이션 환경
- 비파티션 인덱스가 대개 좋은 선택
- 동시 Insert 경합을 분산할 목적으로 해시 파티셔닝하는 경우가 아니라면 글로벌 파티션 인덱스는 효율성이 낮다.
- 테이블이 파티셔닝돼 있다면 인덱스 파티셔닝을 고려
- 파티션 키 컬럼이 검색 조건에서 빠졌을 때
- 로컬 Nonprefixed 인덱스가 모든 인덱스 파티션을 스캔. 비효율은 있지만 잘 사용됨.
- 로컬 Prefixed 파티션 인덱스는 사용 않됨.
- 비효율 문제라면 비파티션 인덱스가 대안.
- 파티션 키 컬럼이 검색 조건으로 사용됐을 때
- '='조건의 경우 Prefixed 와 Nonprefixed 차이는 없다.
- 범위검색 조건의 경우 인덱스 스캔면에서 Nonprefixed 파티션이 낫다.
- 파티션 키 컬럼만으로 주로 조회한다면, 로컬 Prefixed 파티션 인덱스 생성.
- 로컬 인덱스를 선택했다면 Prefixed 파티션이든 Nonprefixed 파티션이든 검색 조건에 항상 사용되는 컬럼을 파티션 키로 선정.
- 파티션 키가 범위검색 조건으로 자주 사용된다면 Nonprefixed 인덱스가 유리
- NL 조인에서 파티션 키에 대한 넓은 범위검색 조건을 가지고 Inner 테이블 액세스 용도로 사용된다면 비파티션 인덱스를 사용.
(10) 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계
- Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼어야 한다.
- 글로벌 파티션 인덱스는 Prefixed 파티션이어야 한다.
- 인덱스를 통해 액세스할 데이터량이 아주 많아 빠른 성능을 내기 어렵고, Full Table Scan으로 처리하기에는 너무 많은 양을 읽어야 할 때 주로 파티셔닝을 실시.
- 파티셔닝은 인덱스 전략 수립과 병행
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3307
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.