파티션 테이블 인덱스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 파티션 인덱스 (Partitioned Index)[편집]
- Partitioned Index는 Partitioned Table에서만 쓰이는것으로 잘못된 생각임.
- Partitioned Index는 Partitioned Table과 별개의 것이며 단지 많은 상호 연관을 가지고 있을 뿐이다.
- Partitioned Index는 말 그대로 Index가 Partitioning 된 것으로 Base Table이 Partitioned Table이든 Non-Partitioned Table이든 상관없이 Partitioned Index를 만들 수 있다.
- 일반테이블로 파티션 인덱스 만드는 예시)
EMP테이블의 Size가 상당히 크고 Non-Partitioned Table(그냥 일반 Table)일 경우 Index를 Partitioned Index로 만들고자 한다면 다음과 같이 할 수 있다
CREATE INDEX EMP_IDX1
ON EMP (DEPTNO) GLOBAL PARTITION BY RANGE (DEPTNO)
(PARTITION PAR_10 VALUES LESS THAN ('20') TABLESPACE TBS1,
PARTITION PAR_20 VALUES LESS THAN ('30') TABLESPACE TBS2,
PARTITION PAR_30 VALUES LESS THAN ('40') TABLESPACE TBS3,
PARTITION PAR_40 VALUES LESS THAN ('50') TABLESPACE TBS4,
PARTITION PAR MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TBS5)
- 위 인덱스는 'Global Prefixed Partitioned Index’라 부른다.
2 Partitioned Index 와 Non-Partitioned Index[편집]
2.1 차이점[편집]
- Partitioned Table과 Non-Partitioned Table의 차이점과 동일.
- Index의 경우 Indexed Column과 Rowid 순으로 Value가 Sorting이 되어 있는데, 이런 특성은 Partitioning과는 무관하다.
- Partitioned Index는 전체 Table에 대하여 Value의 Sorting이 보장되지 않는다라고 생각하고 있지만 이것은 사실과 다르며,
- Global Partitioned Index의 경우는 Value가 전체 Table에 대하여 Sorting이 보장 되어 있으며 Index에 대한 관리도 Partition별로 독립적으로 할 수 있다.(이것은 Base Table의 Partitioning과는 무관한 것이다.)
2.2 Non-Partitioned Index와 Partitioned Index의 구조[편집]
- 그림설명
- 위의 그림에서 Non-Partitioned Index는 하나의 Root Node에서 Leaf Node까지 전체적인 Balance를 유지하는 구조
- Partitioned Index는 Partition별로 독립적인 Root Node와 Leaf Node를 가지고 각각의 Balance를 유지하는 구조
- 따라서 대용량의 Table에서 Global Index의 Depth는 굉장히 깊어질 수 있다.
- Partitioned Index는 각각의 Partition별 Depth가 Non-Partitioned Index보다 얕아지고 Index의 관리도 Partition별로 할 수 있어 Parallel Processing에 의한 Index Management에 매우 효과적이다.
- Partitioned Index의 경우 Index Partition Key가 해당 검색 조건에 들어올 때만이 해당 Index Partition을 Search하여 효과를 볼 수 있고 만약, Partitioned Index에 Index Partition Key가 해당 검색 조건에 들어오지 않으면 전체의 Partition에 대해서 Index를 Lookup할 수 밖에 없다.
- 따라서 Local Index의 경우는 Index Partition Key와 Base Table의 Partition Key가 동일하므로 검색 조건에 이 Index Partition Key가 들어오지 않으면 전체의 Partition에 대해서 반복적인 Lookup작업이 일어나게 되어 Performance 저하를 일으킬 수 있다.
- Global Partitioned Index의 경우는 Index의 첫 번째 컬럼(leading Column)이 Index Partition Key가 되는 Prefixed Index 즉, Global Prefixed Partitioned Index로만 생성이 되기 때문에 (그 이유는 밑에서 설명) 조회 검색 조건에 이 Index를 사용하려면 첫 번째 컬럼인 Index Partition Key가 들어오지 않을 수 없다.
- 따라서 해당 Index Partition을 지정해서 Index Search를 할 수 있는 것이다.
- 이것은 대용량 데이터 환경에서 Non-Partitioned Global Index의 Index Depth가 깊어진다는 점에서 Global Partitioned Index가 성능 면에서 우수할 수 있고
- 또한, 인덱스 Rebuild시에도 각각의 Partition별로 Parallel Processing을 할 수 있는 장점을 가지는 것이다.
assignment 파티션 인덱스 종류
- 로컬 인덱스(Local index) : table partition과 동일한 index partition을 사용.
- Prefixed : index column의 left column을 사용하여 index partition을 구성.
- Non-Prefixed : index column의 left column 이외의 column을 사용하여 index partition을 구성.
- 글로벌 인덱스(Global index) : 하나의 index partition에서 둘 이상의 table partition을 지정.
3 글로벌 인덱스 와 로컬 인덱스[편집]
- 'Partition이 되었는가? 아니면 Partition이 되어있지 않은가?’차이로 구분하는것은 잘못된 생각
- 일반 테이블의 Index도 Partitioning을 할 수 있으며 Partition별로 관리를 할 수 있다.
- Global Index와 Local Index의 가장 근본적인 차이점은 정렬의 차이이다.
- 즉, Global Index는 테이블 전체에 대하여 Indexed Column과 Rowid 순으로 Sorting이 보장된 (Optimizer가 보장하는) Index이고,
- Local Index는 테이블 전체에 대하여 Indexed Column과 Rowid 순으로 Sorting이 보장되지 않는 대신, 각각의 해당 Partition 내에서만 Sorting이 보장된 Index이다.
- 이것이 Global Index와 Local Index의 가장 큰 차이점이다.
3.1 로컬 인덱스 (Local Index)[편집]
- Local Index는 지역적인 Index로 Default가 Base Table의 Partition Key로 Partitioning된 Index이다.
- 인덱스를 생성한 인덱스와 파티션된 인덱스가 동일하게 파티션된 형태.
- Index의 구성 컬럼에 Base Table의 Partition Key가 포함이 되는가의 여부에 상관없이 Local Index의 Partition Key는 Base Table의 Partition Key가 되는 Equipartitioning된 Index인 것이다.
- 인덱스와 테이블은 같은 칼럼에 의해 파티션되며, 하나의 인덱스 파티션이 테이블 파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 됨.
- 결국 특정한 하나의 인덱스에 포함된 모든 Key들은 하나의 테이블 파티션 내의 데이타만 가리킴.
- Local Index에 반드시 Partition Key가 포함 되어야만 검색조건에 Partition Key가 들어왔을 때 지정된 Partition을 사용하는게 아니라 ,
- Local Index에 Partition Key가 포함이 되지 않아도 검색조건에 Partition Key가 들어오면 해당 Partition을 지정하게 된다.
3.1.1 Local Prefixed Index[편집]
- 인덱스의 맨 앞에 있는 컬럼에 의해 파티션되는 방식
- Local Prefixed Index에서 칼럼은 Unique/Non-Unique를 모두 허용
- Base Table 의 파티션이 변경되면 Local Index의 관련 파티션만 변경 됨.
CREATE TABLE DEPT
(
DEPT NUMBER NOT NULL,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(
PARTITION PART_1 VALUES LESS THAN (30),
PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX DEPT_N1 ON DEPT(DEPTNO) LOCAL;
3.1.2 Local Non-Prefixed Index[편집]
- Index의 첫번째 Column이 Partition Key가 아닌 형태로 Base Table과 동일한 Partition 구조를 가진 Index(equi-partitioned)
- 빠른 Access가 요구될 때 유용(Base Table의 Partition Key는 제외)
- Partition 단위로 관리할 수 있으므로 Global Index에 비해 운영상 편리합니다.
- OLAP 측면에서 Global Index보다 조회 속도가 저하.
CREATE TABLE DEPT
(
DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(
PARTITION PART_1 VALUES LESS THAN (30),
PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX DEPT_N2 ON DEPT(LOC) LOCAL;
3.2 글로벌 인덱스 (Global Index)[편집]
- Global Index는 테이블과 다르게 파티션되는 경우
- Global Index는 전역적인 Index로 Default가 Non-Partitioned Index이다.
- Global Index를 Partitioning해서 사용을 할 생각이 없는 듯 하다.
- 대용량의 테이블에서 Index 관리의 효율성을 높이고 Index Search의 Performance를 향상시키기 위하여 Partitioning작업을 하면 효과를 볼 수 있다.
- Global Index는 Base Table의 Partition Key와는 무관하게 Partitioning을 하는 것이고
- 설사 Base Table의 Partition Key로 Global Index를 Partitioning 했다 하더라도 Local Index처럼 Equipartitioning이 된 개념이 아니므로 Table DDL시 전체의 Index를 Rebuild해야 한다.
- 그림 설명
- Global Prefixed Partitioned Index 임.
- “Prefixed”란 Index의 Partition Key (DEPTNO)가 Index 첫 번째 컬럼(DEPTNO)이 된다는 것.
- Global Index의 경우 모든 Indexed Value가 Sorting이 되어 있는데 이는 각각의 Index Partition의 Root Block에 들어가는 Value값들이 Index Partition에 따라 정렬됨을 의미.
- 그러면 자연적으로 Leaf Bock에 들어가는 모든 Value들은 정렬이 되기 때문이다. Global Non-Prefixed Partitioned Index는 개념상 Index Partition Key로 각 Index Partition의 Root Block을 만들지 않는 것이기 때문에 전체 데이터에 대해 Sorting을 보장 받을 수 없다. 따라서 현재 지원되지 않는다.
- Global Index를 Partitioning할 때는 Local Index와는 다르게 Range Partitioning밖에 지원 되지 않는다. 그 이유는 바로 ‘정렬’ 때문인 것이다.
- Range Partition은 Partitioning Key자체를 Sorting에 의해 생성하기 때문이고 다른 Partitioning은 정렬과 상관이 없는 Partitioning 방법이기 때문이다.
3.2.1 Global Prefixed Index[편집]
- Base Table과 비교하여 not equi-partitioned 상태
- Oracle은 only Index structure만 관리합니다. (Partition은 관리안함)
- 최종 Partition에는 Maxvalue값이 반드시 기술되어야 함.
- Local index보다 관리하기 힘듭니다.
- 기준 Table의 Partition이 변경되면 global index의 모든 Partition에 영향을 미칩니다.
- - (Global Index 재생성 해야함,19c부터는 update 옵션 사용으로 Index Unusable 되는 문제 개선됨.)
-- 테이블 생성
CREATE TABLE SALES_DATA_2008
(
COLUMN_1 NUMBER NOT NULL,
COLUMN_2 VARCHAR2(4),
COLUMN_3 VARCHAR2(4),
COLUMN_4 VARCHAR2(2),
COLUMN_5 VARCHAR2(2),
COLUMN_6 NUMBER
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4 )
(
PARTITION P_200801 VALUES LESS THAN ('04', '01'),
PARTITION P_200802 VALUES LESS THAN ('07', '01'),
PARTITION P_200803 VALUES LESS THAN ('10', '01'),
PARTITION P_200804 VALUES LESS THAN ('12', MAXVALUE)
);
-- Global Prefixed Index
CREATE UNIQUE INDEX RANGE2_GPX8 ON SALES_DATA_2008(COLUMN_2, COLUMN_1)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 10
STORAGE( INITIAL 100K NEXT 100K PCTINCREASE 0 )
GLOBAL PARTITION BY RANGE ( CODE )
(
PARTITION P_2008_P1 VALUES LESS THAN ('2000'),
PARTITION P_2008_P2 VALUES LESS THAN ('3000'),
PARTITION P_2008_P3 VALUES LESS THAN (MAXVALUE)
);
3.2.2 Non-Partitioned Index[편집]
- 파티션과는 아무런 상관없는 Normal Index를 말함.