ORA-14086: a partitioned index may not be rebuilt as a whole

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

에러 : ORA-14086: a partitioned index may not be rebuilt as a whole

파티션 테이블의 파티션 인덱스를 rebuild 하려고 할때 발생한 메세지

SQL> alter index PT_IX01 rebuild;
alter index PT_IX01 rebuild
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

 

 

해결 방법 : 파티션 인덱스의 경우 개별 인덱스를 rebuild 진행

파티션 인덱스의 경우 개별 인덱스를 rebuild 진행해줘야함

 

 

현재 해당 인덱스 파티션 조회

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> set lines 200 pages 1000 col index_owner for a10 col index_name for a20 col partition_name for a20 select index_owner, index_name, partition_name, status from dba_ind_partitions where index_name in (select index_name from dba_part_indexes where table_name ='PTABLE');    INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS ---------- -------------------- -------------------- -------- IMSI       PT_IX01              P202101              UNUSABLE IMSI       PT_IX01              P202102              UNUSABLE IMSI       PT_IX01              P202103              UNUSABLE IMSI       PT_IX01              P202104              UNUSABLE IMSI       PT_IX01              P202105              UNUSABLE IMSI       PT_IX01              P202106              UNUSABLE IMSI       PT_IX01              P202107              UNUSABLE IMSI       PT_IX01              P202108              UNUSABLE IMSI       PT_IX01              P202109              UNUSABLE IMSI       PT_IX01              P202110              UNUSABLE IMSI       PT_IX01              P202111              UNUSABLE IMSI       PT_IX01              P202112              UNUSABLE IMSI       PT_IX01              P_MAX                UNUSABLE   13 rows selected.

모두 unusable 상태임

 

 

파티션별로 인덱스 리빌드 진행

1 2 3

SQL> alter index PT_IX01 rebuild partition P_MAX;   Index altered.

 

 

파티션 인덱스 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> set lines 200 pages 1000 col index_owner for a10 col index_name for a20 col partition_name for a20 select index_owner, index_name, partition_name, status from dba_ind_partitions where index_name in (select index_name from dba_part_indexes where table_name ='PTABLE');    INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS ---------- -------------------- -------------------- -------- IMSI       PT_IX01              P202101              UNUSABLE IMSI       PT_IX01              P202102              UNUSABLE IMSI       PT_IX01              P202103              UNUSABLE IMSI       PT_IX01              P202104              UNUSABLE IMSI       PT_IX01              P202105              UNUSABLE IMSI       PT_IX01              P202106              UNUSABLE IMSI       PT_IX01              P202107              UNUSABLE IMSI       PT_IX01              P202108              UNUSABLE IMSI       PT_IX01              P202109              UNUSABLE IMSI       PT_IX01              P202110              UNUSABLE IMSI       PT_IX01              P202111              UNUSABLE IMSI       PT_IX01              P202112              UNUSABLE IMSI       PT_IX01              P_MAX                USABLE   13 rows selected.

정상적으로 usable 상태로 변함

 

 

나머지 인덱스의 파티션들도 모두 rebulid 진행
구문 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> set lines 200 pages 1000 col sql for a70 select 'alter index '|| index_owner || '.' || index_name ||' rebuild partition '|| partition_name || ' online;' "SQL" from dba_ind_partitions where index_name in (select index_name from dba_part_indexes where table_name ='PTABLE') and status = 'UNUSABLE';   SQL ---------------------------------------------------------------------- alter index IMSI.PT_IX01 rebuild partition P202101 online; alter index IMSI.PT_IX01 rebuild partition P202102 online; alter index IMSI.PT_IX01 rebuild partition P202103 online; alter index IMSI.PT_IX01 rebuild partition P202104 online; alter index IMSI.PT_IX01 rebuild partition P202105 online; alter index IMSI.PT_IX01 rebuild partition P202106 online; alter index IMSI.PT_IX01 rebuild partition P202107 online; alter index IMSI.PT_IX01 rebuild partition P202108 online; alter index IMSI.PT_IX01 rebuild partition P202109 online; alter index IMSI.PT_IX01 rebuild partition P202110 online; alter index IMSI.PT_IX01 rebuild partition P202111 online; alter index IMSI.PT_IX01 rebuild partition P202112 online;   12 rows selected.

 

 

해당 쿼리 실행

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> alter index IMSI.PT_IX01 rebuild partition P202101 online; alter index IMSI.PT_IX01 rebuild partition P202102 online; alter index IMSI.PT_IX01 rebuild partition P202103 online; alter index IMSI.PT_IX01 rebuild partition P202104 online; alter index IMSI.PT_IX01 rebuild partition P202105 online; alter index IMSI.PT_IX01 rebuild partition P202106 online; alter index IMSI.PT_IX01 rebuild partition P202107 online; alter index IMSI.PT_IX01 rebuild partition P202108 online; alter index IMSI.PT_IX01 rebuild partition P202109 online; alter index IMSI.PT_IX01 rebuild partition P202110 online; alter index IMSI.PT_IX01 rebuild partition P202111 online; alter index IMSI.PT_IX01 rebuild partition P202112 online;

 

 

파티션 인덱스 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> set lines 200 pages 1000 col index_owner for a10 col index_name for a20 col partition_name for a20 select index_owner, index_name, partition_name, status from dba_ind_partitions where index_name in (select index_name from dba_part_indexes where table_name ='PTABLE');    INDEX_OWNE INDEX_NAME           PARTITION_NAME       STATUS ---------- -------------------- -------------------- -------- IMSI       PT_IX01              P202101              USABLE IMSI       PT_IX01              P202102              USABLE IMSI       PT_IX01              P202103              USABLE IMSI       PT_IX01              P202104              USABLE IMSI       PT_IX01              P202105              USABLE IMSI       PT_IX01              P202106              USABLE IMSI       PT_IX01              P202107              USABLE IMSI       PT_IX01              P202108              USABLE IMSI       PT_IX01              P202109              USABLE IMSI       PT_IX01              P202110              USABLE IMSI       PT_IX01              P202111              USABLE IMSI       PT_IX01              P202112              USABLE IMSI       PT_IX01              P_MAX                USABLE   13 rows selected.

모두 정상화됨

 

 

원인 : 파티션 인덱스를 개별 인덱스 레벨로 rebuild 하지 않고 전체 인덱스를 rebuild 하려해서 발생한 문제

파티션 인덱스를 개별 인덱스 레벨로 rebuild 하지 않고 전체 인덱스를 rebuild 하려해서 발생한 문제

파티션 인덱스의 경우 개별 인덱스를 rebuild 진행해줘야함

 

근본적으로 unusable 상태로 빠진 인덱스들은

인덱스가 글로벌 파티션 인덱스로 생성된 상태에서 파티션 구조 변경이 발생한것이 원인임
글로벌 파티션 인덱스의 경우 파티션 구조변경이 일어났을때 인덱스가 unusable 상태로 변함
이 경우 인덱스를 제대로 사용하지 못함
파티션 작업시 항상 글로벌 파티션 인덱스가 존재하는지 확인 후 작업을 진행해야함

 

 

글로벌 파티션 인덱스 여부 확인

SQL>
select di.index_name, di.uniqueness, di.partitioned, dpi.locality, dpi.alignment 
from dba_indexes di, dba_part_indexes dpi
where di.table_name= 'PTABLE'
and dpi.table_name(+) = di.table_name
and dpi.index_name(+) = di.index_name
order by 1;
 
INDEX_NAME           UNIQUENES PAR LOCALI ALIGNMENT
-------------------- --------- --- ------ ------------
PT_IX01              NONUNIQUE YES GLOBAL PREFIXED

locality 컬럼이 GLOBAL임=> 글로벌 파티션 인덱스

 

 

참조 : 

오라클 19c 파티션 테이블 add 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/999 )

오라클 19c 파티션 테이블 drop 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1000 )

오라클 19c 파티션 테이블 exchange 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1001 )

오라클 19c 파티션 테이블 merge 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1002 )

오라클 19c 파티션 테이블 move 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1003 )

오라클 19c 파티션 테이블 rename 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1004 )

오라클 19c 파티션 테이블 split 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1005 )

오라클 19c 파티션 테이블 truncate 작업 시 인덱스 영향도 확인 ( https://positivemh.tistory.com/1006 )