파티셔닝 인덱스
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 파티셔닝 인덱스 종류[편집]
1.1 파티션 인덱스 와 비파티션 인덱스[편집]
1.2 LOCAL INDEX[편집]
- local index란 index를 생성한 table과 partitioned index가 equi-partition된 경우를 나타낸다.
- 즉, index와 table은 같은 컬럼에 의해 partition 되며, 하나의 index partition이 table partition 하나와 대응되며, 대응되는 index partition과 table partition은 각각 같은 범위를 갖게 된다.
- 결국 특정한 하나의 index에 포함된 모든 key들은 하나의 table partition 내의 data만을 가리키게 된다.
emoji_objects partitioned index는 prefixed 와 non-prefixed로 나누어진다.
1.3 LOCAL PREFIXED INDEX[편집]
- prefixed index는 index에서 맨 앞에 위치한 column에 의해 partition 되는 것
- non-prefixed index는 index에서 맨 앞의 컬럼을 제외한 다른 컬럼에 의해 partition 되는것
arrow_downward local prefixed index 생성 예시
CREATE TABLE dept
( deptno NUMBER NOT NULL
, loc VARCHAR2(10))
PARTITION BY RANGE (deptno)
(PARTITION part1 VALUES LESS THAN(30),
PARTITION part2 values less than (MAXVALUE));
CREATE INDEX dept_idx ON dept(deptno) LOCAL;
1.4 LOCAL NON-PREFIEX INDEX[편집]
- index columns들 중 맨 앞에 있는 column으로 partition되지 않은 경우는 non-prefixed index가 된다.
arrow_downward non-prefixed index 생성 예시
CREATE INDEX dept_locidx
ON dept(loc)
LOCAL;
- 이러한 non-prefixed index는 특히 historical한 data를 보관하는 table의 경우 유용하다.
- 즉, 날짜에 따라 table과 index의 partition은 이루어지고, 인덱스는 별도의 사원 번호나 제품 번호와 같이 key가 되는 것에 생성하는 경우이다.
1.5 파티션 로컬인덱스 생성[편집]
1.6 GLOBAL INDEX[편집]
- global index는 table 과 다르게 partition이 된다.
- table과 같은 column으로 partition되나 그 범위가 틀리거나, 혹은 다른 컬럼으로 partition이 이루어진다.
- global index는 prefixed global index만이 존재하며, non-prefixed global index는 생성이 불가능하다.
- global index 생성 예제
arrow_downward TABLE 생성시
CREATE TABLE emp
( empno NUMBER NOT NULL
, ename VARCHAR2(10)
, deptno NUMBER
)
-- 파티션 절
PARTITION BY RANGE (deptno)
( PARTITION part1 VALUES LESS THAN(30)
, PARTITION part2 VALUES LESS THAN (MAXVALUE)
)
;
arrow_downward INDEX 생성시
CREATE UNIQUE INDEX PK_EMP on emp(empno)
-- 파티션 절
GLOBAL PARTITION BY RANGE (empno)
( PARTITION p1 VALUES LESS THAN ("1000")
, PARTITION p2 VALUES LESS THAN ("2000")
, PARTITION p3 VALUES LESS THAN (MAXVALUE)
)
-- UNUSABLE 로 생성
;
-- UNIQUE INDEX 를 UNUSABLE로 생성 후 리빌드 (생성 성능 향상을 위해)
-- ALTER INDEX PK_EMP REBUILD PARTITION [파티션명];
1.6.1 GLOBAL INDEX 의 INDEX UNUSABLE[편집]
- non-partitioned index 나 partitioned index의 partition은 특정한 operation에 의해 Index Unusable(IU) 상태가 될 수 있다.
- 이렇게 IU 상태가 된 index나 index partition을 SELECT하거나 DML을 시도하면 오류가 발생하게 된다.
- partition이 IU 상태가 되면 그 partition을 사용하기 전에 rebuild하여야 한다.
- 그러나 IU partition을 제외한 다른 partition만을 읽거나 DML을 수행하는 작업은 IU partition을 access하지 않는 한 오류가 발생하지 않는다.
- 단, IU partition을 rebuild하기 전에 split이나 rename이 가능하며, IU 상태인 global index를 drop하는 것도 가능하다.
1.6.2 INDEX UNUSABLE 상태를 확인하고 해결하는 방법[편집]
1) IU 상태인 파티셔닝 테이블 확인
select index_owner, index_name, partition_name, status
from dba_ind_partitions
where status = 'UNUSABLE' ;
2) 인덱스 rebuild
alter index pk_emp rebuild partiton p1;
1.6.3 global partition index 인덱스는 인덱스 전체를 한번에 재생성 할수 없음.[편집]
- 해결 방법
1) 글로벌 파티션 인덱스 생성시 ORA-14086 오류 발생
alter index SALES_GPNK1 rebuild
"ORA-14086:분할영역된 인덱스는 전체를 다시 만들 수 없습니다." 와 같은 에러를 발생하게 된다.
2) 파티션 단위로 인덱스 REBUILD
-- 인덱스 정보 조회 dba_ind_partitions 에서 파티션 확인 후
-- 파티션별로 인덱스 리빌드
alter index SALES_GPNK1 rebuild partition SALES_GPI01 ;
실무에서 global partition index의 경우 non-partition index 이든 partition index 이든 파티션 테이블 관련 작업인 경우 재생성이 필수 이므로 파티션 테이블별로 global index의 rebuild 작업용 스크립트를 작성해 놓아야 당황하지 않고 빠른 시간안에 관련 인덱스를 재 생성 할 수 있다
- ORA-01502 오류 발생 시 )
ORA-01502: 인덱스 '인덱스명'또는 인덱스 분할영역은 사용할 수없는 상태이다.
원인 : 파티셔닝테이블은 파티션별로 ROWID를 다르게 갖게 됨으로 파티션의 변경이 발생하게 되면 ROWID의 변경이 발생하게 된다.
그래서 실제 파티션 테이블의 RowID 와 변경된 파티션 로컬 인덱스와 글로벌 인덱스의 RowID와 가 일치하지 않게 되어 인덱스 사용 중지 발생 즉, IU상태(INDEX UNUSABLE)가 발생하게 되어 인덱스의 사용 불가 및 재성성에 따른 운영 및 관리 상의 부하 , 재생성에 따른 시스템의 부하가 발생.
(쉽게 말해 테이블 파티션이 이동하거나 , 테이블 스페이스 이동 , 병합등 ...으로인해 인덱스 참조가 깨진 현상)