인덱스 파티셔닝 (by niceboyous) [2013.01.29]
h1.03.인덱스 파티셔닝
h3.(1) 인덱스 파티션 유형
- 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션인덱스를 가질 수 있다.
- 파티션 테이블은 비파티션 인덱스와 글로벌 파티션인덱스, 로컬 파티션 인덱스를 가질 수 있다.
h3.(2) 로컬 파티션 인덱스
- 각 인덱스 파티션이 테이블 파티션과 1:1대응 관계를 갖고, 파티션키를 따로 정의하지 않아도 테이블 파티션 속성을 그대로 상속받음
(단, 인덱스 구성에 table 파티션key가 포함되어야 한다.) - 테이블이 결합 파티셔닝 되어 있다면 인덱스도 같은 단위로 파티셔닝 됨
- 로컬 파티션 인덱스의 장점은 관리편의성이다. 테이블 파티션 구성에 변경이 생겨도 인덱스를 재생성할 필요가 없다.
h3.(3) 비파티션 인덱스
- 비파티션 인덱스는 파티셔닝하지 않은 인덱스이다.
- 테이블이 파티션 되어있다면, 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관련을 갖는다.
- 글로벌 비파티션 인덱스라고 하기도함.
h3.(4) 글로벌 파티션 인덱스
- 글로벌 파티션 인덱스는 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝을 하는것을 말함.(테이블은 파티셔닝 안돼어 있을 수 있다.)
- 제약사항이 있어 효용성이 낮다.
- 기준 테이블의 파티션 구성에 변경이 생길 때마다 인덱스가 unusable 상태가 되고, 그때마다 인덱스를 재생성 해야함.
- 9i부터 'update global indexes' 옵션을 주면 파티션DDL 작업에 영향받는 인덱스를 자동으로 갱신해 주므로 인덱스가 unusable 상태가 되지 않는다.
하지만, 파티션DDL 작업에 영향받는 인덱스가 5%가 넘으면 인덱스를 재생성하는 것보다 오히려 늦다.
alter table ...
split partition ...
update global indexes;
h4.글로벌 해시 파티션 인덱스
- 글로벌 파티션 인덱스의 경우, 9i까지는 글로벌 Range파티션만 가능. 10g부터 글로벌 hash파티션도 가능.
- 테이블과 독립적으로 인덱스만 해시키 값에 따라 파티셔닝 가능
- Right Growing 인덱스처럼 Hot블록이 발생하는 인덱스의 경합을 분산할 목적으로 주로 사용.
(Right Growing 인덱스란, '일련번호'나 '입력일시'처럼 순차적으로 증가하는 컬럼에 생성한 인덱스로 항상 우측 블록으로만 값이 입력되는 특징을 갖는 인덱스이다.) - 글로벌 결합 인덱스 파티셔닝은 불가능함
h3.(5) Prefixed vs. Nonprefixed
- Prefixed : 파티션 인덱스를 생성할 때, 인덱스 파티션 키 컬럼을 인덱스 키 컬럼 선두에 두는것.
- Nonprefixed : 파티션 인덱스를 생성할 때, 인덱스 파티션 키 컬럼을 인덱스 키 컬럼 선두에 두지 않는것, 파티션 키가 인덱스 컬럼에 속하지 않을때도 여기에 속함.
- * 글로벌 파티션인덱스는 Prefixed만 가능함. 로걸 파티션 인덱스는 Prefixed, Nonprefixed 다 가능하다.
h3.(6) 파티션 인덱스 구성 예시
- 1.글로벌 Prefixed 파티션 인덱스의 키는 테이블파티션키와 독립적인 구성 가능하다.
- 2.글로벌 Prefixed 파티션 인덱스는 인덱스 파티션키를 선두로하는 Prefixed만 지원한다.
- 3.로컬 파티션 인덱스는 인덱스 파티션키가 테이블파티션키와 동일하다.
- 4.로컬 파티션 인덱스 키 컬럼은 prefixed, Nonprefixed 모두 지원한다.
h4.인덱스 파티셔닝 예제
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 ;
Table created.
-- seq 컬럼으로 range 파티셔닝한 테이블 생성
--1. 로컬인덱스 생성 시도
SQL> create unique index t_idx1 on t(gubun, seq2) LOCAL ;
create unique index t_idx1 on t(gubun, seq2) LOCAL
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
--에러 원인
--로컬인덱스인데 파티션키가 인덱스 구성에 미포함
--2. 위와 동일한 구성인데 LOCAL을 빼고 실행, 비파티션인덱스 생성시도
SQL> create unique index t_idx1 on t(gubun, seq2) ;
Index created.
--3. Nonprefixed, Prefixed 로컬 인덱스 생성시도
SQL> create unique index t_idx2 on t(gubun, seq) LOCAL ;
Index created.
SQL> create index t_idx3 on t(seq, gubun) LOCAL ;
Index created.
--4. 글로벌 파티션 인덱스 생성 시도
SQL> 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)
)
;
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed
--에러원인
--글로벌 파티션인덱스는 prefixed만 지원함.
SQL> 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)
) ;
Index created.
--글로벌 Prefixed 인덱스 생성 성공
--그런데, 위 인덱스가 테이블 파티션과 100% 같게 정의하더라도 위 인덱스는 글로벌 파티션인덱스이다.
create index t_idx10 on t(seq, seq_cls) LOCAL ; 와 다르다.
--5. 파티션 정보 조회쿼리
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 개의 행이 선택되었습니다.
h3.(7) 글로벌 파티션 인덱스의 효용성
- 결론은, 글로벌 파티션 인덱스는 경합을 분산시키려고 글로벌 해시 파티셔닝하는 경우 외에는 거의 사용되지 않는다.
- 1)비파티션 테이블에 글로벌 파티션 인덱스 생성 : 테이블을 파티셔닝 하지 않을 정도로 중소형급인데 인덱스만 파티셔닝할 이유는 별로 없다.
2)파티션 테이블에 대한 글로벌 파티션 인덱스 생성 : 로컬 파티션 인덱스를 주로 사용한다. 이유는 아래와 같다.
h4.①테이블과 같은 컬럼으로 파티셔닝하는 경우 - 테이블은 날짜 컬럼 기준으로 월별 파티셔닝하고, 인덱스는 분기별 파티셔닝
=> 글로벌 파티션 인덱스에는 Prefixed 파티션만 허용되므로 날짜 컬럼을 선두로 둬야 함
=> 날짜 조건은 대개 범위검색 조건이 사용되므로 인덱스 스캔 효율 면에서 불리하다.
=> NL 조인에서 Inner 테이블 액세스를 위해 자주 사용되는 인덱스라면 비효율이 더 크게 작용.
=> 다른 '=' 조건 컬럼을 선두에 둘 수 있다는 측면에선 로컬 Nonprefixed 파티션 인덱스가 유리
=> 두 달 이상의 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 사용될 때는 로컬 Nonprefixed 파티션 인덱스에도 비효율이 발생
(조인 액세스가 일어나는 레코드마다 여러 인덱스 파티션을 탐색해야 하기 때문)
결론 : NL 조인에서 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 자주 사용된다면 비파티션 인덱스가 가장 좋은 선택.
h4.②테이블과 다른 컬럼으로 파티셔닝하는 경우
- 테이블 파티션 기준인 날짜 이외 컬럼으로 인덱스를 글로벌 파티셔닝 할 수 있는데, 그런 구성은 대개 인덱스를 적정 크기로 유지하려는 데에 목적.
- 로컬 파티션 인덱스가 위 목적에 더 적합함.
h3.(8) 로컬 Nonprefixed 파티션 인덱스의 효용성
- 이력성 데이터를 효과적으로 관리할 수 있다.
- 인덱스 스캔효율을 높일 수 있다.
- 예)일별계좌별거래 : pk는 일자, 계좌번호, 주문매체, 거래유형 등 다수
파티션키는 날짜컬럼(거래일자) Range 파티셔닝
h4.로컬 Prefixed 파티션 인덱스와 비교
SQL> create table 일별계좌별거래 (
2 계좌번호 number
3 , 거래일자 date
, 거래량 number
4 5 , 거래금액 number
6 )
7 partition by range(거래일자)(
partition p01 values less than(to_date('20090201', 'yyyymmdd'))
8 9 , partition p02 values less than(to_date('20090301', 'yyyymmdd'))
10 , partition p03 values less than(to_date('20090401', 'yyyymmdd'))
11 , partition p04 values less than(to_date('20090501', 'yyyymmdd'))
12 , partition p05 values less than(to_date('20090601', 'yyyymmdd'))
13 , partition p06 values less than(to_date('20090701', 'yyyymmdd'))
14 , partition p07 values less than(to_date('20090801', 'yyyymmdd'))
15 , partition p08 values less than(to_date('20090901', 'yyyymmdd'))
16 , partition p09 values less than(to_date('20091001', 'yyyymmdd'))
17 , partition p10 values less than(to_date('20091101', 'yyyymmdd'))
18 , partition p11 values less than(to_date('20091201', 'yyyymmdd'))
19 , partition p12 values less than(maxvalue)
20 ) ;
Table created.
SQL> declare
2 l_first_date date;
3 l_last_day number;
begin
4 5 for i in 1..12
6 loop
l_first_date := to_date('2009' || lpad(i, 2, '0') || '01', 'yyyymmdd');
7 8 l_last_day := to_number(to_char(last_day(l_first_date), 'dd'));
9 insert into 일별계좌별거래
10 select rownum 계좌번호
11 , l_first_date + mod(rownum, l_last_day) 거래일자
12 , round(dbms_random.value(100, 10000)) 거래량
13 , round(dbms_random.value(10000, 1000000)) 거래금액
14 from dual
connect by level <= 10000;
15 16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> create index local_prefix_index on 일별계좌별거래(거래일자, 계좌번호) local;
Index created.
SQL> create index local_nonprefix_index on 일별계좌별거래(계좌번호, 거래일자) local;
Index created.
SQL> select /*+ index(t local_prefix_index) */ sum(거래량), sum(거래금액)
from 일별계좌별거래 t
where 계좌번호 = 100
and 거래일자 between to_date('20090115', 'yyyymmdd')
and to_date('20091215', 'yyyymmdd')
;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 485 (1)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 48 | | | | |
| 2 | PARTITION RANGE ALL | | 21 | 1008 | 485 (1)| 00:00:06 | 1 | 12 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| 일별계좌별거래 | 21 | 1008 | 485 (1)| 00:00:06 | 1 | 12 |
|* 4 | INDEX RANGE SCAN | LOCAL_PREFIX_INDEX | 483 | | 436 (1)| 00:00:06 | 1 | 12 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("거래일자">=TO_DATE(' 2009-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "계좌번호"=100 AND
"거래일자"<=TO_DATE(' 2009-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("계좌번호"=100)
--로컬 Prefixed 파티션 인덱스는 계좌번호 조건을 만족하지 않는 거래 데이터까지 모두 스캔
SQL> select /*+ index(t local_nonprefix_index) */ sum(거래량), sum(거래금액)
from 일별계좌별거래 t
where 계좌번호 = 100
and 거래일자 between to_date('20090115', 'yyyymmdd')
and to_date('20091215', 'yyyymmdd')
;
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 14 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 48 | | | | |
| 2 | PARTITION RANGE ALL | | 21 | 1008 | 14 (0)| 00:00:01 | 1 | 12 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| 일별계좌별거래 | 21 | 1008 | 14 (0)| 00:00:01 | 1 | 12 |
|* 4 | INDEX RANGE SCAN | LOCAL_NONPREFIX_INDEX | 21 | | 13 (0)| 00:00:01 | 1 | 12 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("계좌번호"=100 AND "거래일자">=TO_DATE(' 2009-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "거래일자"<=TO_DATE('
2009-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
--로컬 Nonprefixed 파티션 인덱스는 각 인덱스 파티션마다 필요한 최소 범위만 스캔.
h4.글로벌 Prefixed 파티션 인덱스와 비교
- Prefixed 파티션만 혀용되므로 거래일자가 선두컬럼인 로컬 Prefixed 파티션과 마찬가지로 인덱스 스캔 효율이 나쁘다.
- 파티션단위 삭제(Rolling-out), 추가(Rolling-in) 작업이 있을 시 인덱스를 재생성해야 하므로 관리적 부담이 크다.
h4.비파티션 인덱스와 비교
- 관리적 부담은 글로벌 파티션 인덱스와 동일함.
- 비파티션 인덱스에는 병렬쿼리가 허용되지 않는다.
로컬 Nonprefixed 파티션 인덱스라면 여러 병렬 프로세스가 각각 하나의 인덱스 세그먼트를 스캔하도록 해서 응답속도를 향상시킬수 있다.
h4.일 단위 파티셔닝
- 1.테이블이 일단위 파티션 되어 있다.
2.로컬 Nonprefixed 인덱스를 계좌번호 단독컬럼으로 생성하던지, 계좌번호+거래일자 로 생성하던지 인덱스 스캔 효율은 같다.
<참고쿼리문>
1. select 계좌번호, count(*)
from 일별계좌별집계
where 계좌번호 = :acnt_no
h3.(9) 액세스 효율을 고려한 인덱스 파티셔닝 선택 기준
h4.DW성 애플리케이션 환경
- DW는 날짜 컬럼 기준으로 파티셔닝 된 이력성 대용량 테이블이 많다.
- 따라서, 관리적인 측면 + 병렬 쿼리 활용 측면에서도 로컬파티션인덱스는 좋은선택이다.
- 로컬 인덱스 중에서는 Nonprefixed 파티션 인덱스가 성능 면에서 유리할 때가 많다.
h4.OLTP성 애플리케이션 환경
- OLTP성은 비파티션 인덱스가 대개 좋은 선택이다.
- OLTP라도 테이블이 파티셔닝이 돼어 있다면 인덱스 파티셔닝을 고려할 수 있는데, 로컬 파티션 인덱스가 가용성 측면에서 유리하다.
- 로컬 인덱스 중 Prefixed가 더 유리하다고 오라클 메뉴얼에 명시되어 있는데, 필자는 항상 유리하지 않으므로 때에 따라 사용할것을 권함
=> Prefixed 나 Nonprefixed나 검색조건에 항상 사용되는 컬럼을 파티션 키로 선정하려고 노력해야함.
h3.(10) 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계
- 제약1. Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼어야 한다.
그렇지 않으면, 인덱스 키 값을 변경할때마다 중복값 체크를 위해 많은 인덱스 파티션을 탐색해야 하므로 성능이 저하된다. - 제약2. 글로벌파티션인덱스는 Prefixed 파티션이어야 한다.