행위

파티셔닝 인덱스

DB CAFE

thumb_up 추천메뉴 바로가기

1 파티셔닝 인덱스 종류[편집]

1.1 파티션 인덱스 와 비파티션 인덱스[편집]

worddav264d3fdd650c34c32cab2381bd3bfafc1.png

1.2 LOCAL INDEX[편집]

  1. local index란 index를 생성한 table과 partitioned index가 equi-partition된 경우를 나타낸다.
  2. 즉, index와 table은 같은 컬럼에 의해 partition 되며, 하나의 index partition이 table partition 하나와 대응되며, 대응되는 index partition과 table partition은 각각 같은 범위를 갖게 된다.
  3. 결국 특정한 하나의 index에 포함된 모든 key들은 하나의 table partition 내의 data만을 가리키게 된다.

 emoji_objects partitioned index는 prefixed 와 non-prefixed로 나누어진다.

1.3 LOCAL PREFIXED INDEX[편집]

  1. prefixed index는 index에서 맨 앞에 위치한 column에 의해 partition 되는 것
  2. 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[편집]

  1. 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[편집]

assignment 글로벌 인덱스
  1. 하나의 index partition에 있는 모든 index는 모두 하나의 table partition에 속하게 되지 않고, 두 개 이상의 partition에 나누어 있을 수 있다.
  2. 예를 들어
    1. EMP(사원) table의 경우 많은 수의 사원을 각 부서 별로 partition을 구성할 수 있다.
    2. 특정 부서에 속한 사원에 대한 operation의 경우 이것은 매우 도움이 될 수 있다.
    3. 그러나 대부분 회사에 부서의 종류는 아주 많은 것이 아니어서 부서 번호에 index를 거는 것은 드문 일이다.
    4. 그러나 사원 번호는 고유하기 때문에 primary key가 되거나 index를 생성하는 것이 일반적이다.
    5. 이 때 이 사원 번호에 부여된 index를 partitioning하게 되면, 이것이 global index가 되는 것이다.


  1. global index는 table 과 다르게 partition이 된다.
  2. table과 같은 column으로 partition되나 그 범위가 틀리거나, 혹은 다른 컬럼으로 partition이 이루어진다.
  3. 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[편집]

  1. non-partitioned index 나 partitioned index의 partition은 특정한 operation에 의해 Index Unusable(IU) 상태가 될 수 있다.
  2. 이렇게 IU 상태가 된 index나 index partition을 SELECT하거나 DML을 시도하면 오류가 발생하게 된다.
  3. partition이 IU 상태가 되면 그 partition을 사용하기 전에 rebuild하여야 한다.
  4. 그러나 IU partition을 제외한 다른 partition만을 읽거나 DML을 수행하는 작업은 IU partition을 access하지 않는 한 오류가 발생하지 않는다.
    단, IU partition을 rebuild하기 전에 split이나 rename이 가능하며, IU 상태인 global index를 drop하는 것도 가능하다.


assignment partition을 Index Unusable 상태로 만들 수 있는 작업(6가지로 요약)

(1) direct path load 시

Direct path SQL*Loader 수행 후 index가 table의 해당 data보다 이전 것이면, IU 상태가 된다. index가 table의 data보다 이전 상태라는 것은 data를 load 후 index를 생성 중에 space 부족 등의 원인으로 오류가 발생하였거나 SKIP_INDEX_MAINTENANCE option을 사용한 경우이다.


(2) ALTER TABLE MOVE PARTITION과 같이 ROWID를 변화시키는 작업. 

   영향받는 local index와 전체 global index를 IU 상태가 되게 한다.


(3) ALTER TABLE TRUNCATE PARTITION이나 DROP PARTITION과 같이 table의 row를 지우는 작업. 

   global index partition을 IU 상태로 만든다.


(4) ALTER TABLE SPLIT PARTITION은 local index의 partition definition은 변경시키지만, 자동으로 index를 새로운 definition에 맞게 rebuild하지 않기 때문에 영향 받는 local index partition을 IU 상태로 만든다. 또한 이것은 ROWID를 변경시키기 때문에 모든 global index partition을 IU 상태로 만든다.


(5) ALTER INDEX SPLIT PARTITION은 index의 definition은 변경시키지만, 영향 받은 partition은 rebuild시키지 않는다. 이 작업은 영향받는 index partition 부분을 IU 상태로 만든다. 그러나 global index의 경우는 그대로 usable 상태로 된다.



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)가 발생하게 되어 인덱스의 사용 불가 및 재성성에 따른 운영 및 관리 상의 부하 , 재생성에 따른 시스템의 부하가 발생.

(쉽게 말해 테이블 파티션이 이동하거나 , 테이블 스페이스 이동 , 병합등 ...으로인해 인덱스 참조가 깨진 현상)

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=파티셔닝_인덱스&oldid=11473"