Techdata VINA 지식 공유

Oracle8의 새로운 기능인 partition 은 index에도 적용된다. 이 partitioned index는 생성되는 형태에 따라 몇 가지로 나누어지는데, 여기에서는 이 각각의 

종류에 대해 좀 더 자세히 설명한다. 그리고 table과 index가 partition됨에 따라 index가 unusable 상태가 되는 경우가 발생할 수 있는데 이러한 상태를 

유발시키는 경우와 조치 방법에 대해서도 살펴본다.

1. partitioned index의 종류

(1) 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만을 가리키게 된다.

partitioned index는 다시 다음과 같이 prefixed와 non-prefixed로 나누어진다.

(1)-1 local prefixed index

prefixed index는 index에서 맨 앞에 위치한 column에 의해 partition되는 것이며, non-prefixed index는 index에서 맨 앞의 컬럼을 제외한 다른 컬럼에 의해 partition된 경우이다.

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)-2 local non-prefixed index

index columns들 중 맨 앞에 있는 column으로 partition되지 않은 경우는 non-prefixed index가 된다. 다음과 같이 생성할 수 있다.

   CREATE INDEX dept_locidx ON dept(loc) LOCAL;

이러한 non-prefixed index는 특히 historical한 data를 보관하는 table의 경우 유용하다. 즉, 날짜에 따라 table과 index의 partition은 이루어지고, 인덱스는 별도의 사원 번호나 제품 번호와 같이 key가 되는 것에 생성하는 경우이다.

(2) GLOBAL INDEX

global index는 table과는 다르게 partition이 된다. 즉, table과 같은 column으로 partition되나 그 범위가 틀리거나, 혹은 다른 컬럼으로 partition이 이루어진다.

하나의 index partition에 있는 모든 index는 모두 하나의 table partition에 속하게 되지 않고, 두 개 이상의 partition에 나누어 있을 수 있다. 예를 들어 EMP table의 경우 많은 수의 사원을 각 부서 별로 partition을 구성할 수 있다. 특정 부서에 속한 사원에 대한 operation의 경우 이것은 매우 도움이 될 수 있다. 그러나 대부분 회사에 부서의 종류는 아주 많은 것이 아니어서 부

서 번호에 index를 거는 것은 드문 일이다. 그러나 사원 번호는 고유하기 때문에 primary key가 되거나 index를 생성하는 것이 일반적이다. 이 때 이 사원 번호에 부여된 index를 partitioning하게 되면, 이것이 global index가 되는 것이다.

global index는 prefixed global index만이 존재하며, non-prefixed global index는 생성이 불가능하다. 즉, global index는 항상 index의 맨 앞 컬럼 값만을 이용하여 partition된다.

위의 예를 이용하여 global index를 생성하면 다음과 같다.

   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));

   CREATE UNIQUE INDEX emp_pk 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));

2. 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하는 것도 가능하다.

partition을 Index Unusable 상태로 만들 수 있는 작업은 다음과 같이 6가지로 요약할 수 있다.

(1) direct path load 시

Direct path SQL*Loader 수행 후 index가 table의 해당 data보다 이전 것이면, IU 상태가 된다. (Oracle7에서는 Index가 Direct Load State가 되었다고 표현한다). 

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 상태로 된다.

3. unusable상태를 확인하고 해결하는 방법

   os> sqlplus system/manager

   SQL>select index_owner, index_name, partition_name, status 

       from dba_ind_partitions

       where status = "UNUSABLE" ;   

   rebuild하는 방법은 해당 user에서

   SQL>alter index emp_pk rebuild partiton p1;