[Oracle] ORA-01502 문제 해결

[Oracle] ORA-01502 문제 해결

문제발생 : 

ORA-01502: 인덱스 ''''인덱스명''''또는 인덱스 분할영역은 사용할 수없는 상태이다.

원인 :

파티션은 파티션별로 RowID를 다르게 갖게 됨으로 파티션의 변경이 발생하게 되면 RowID의 변경이 발생하게 된다.

그래서 실제 파티션 테이블의 RowID와 변경된 파티션 로컬 인덱스와 글로벌 인덱스의  RowID와 가 일치하지 않게 되어 인덱스 사용 중지 즉, IU상태(INDEX UNUSABLE)가 발생하게 되어 인덱스의 사용 불가 및 재 성성에 따른 운영 및 관리상의 부하 및 재 생성에 따른 시스템의 부하가 발생하게 된다.

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

Partition Index를 Unusable상태로 만들 수 있는 명령.(Oracle9i 기준).  --퍼옴--

작업 대상	작업유형	인덱스	Unusable 상태 변경
파티션 테이블	ADD	LOCAL	새로 생성되므로 상관없다.
		GLOBAL	파티션만 추가되므로 상관없다.
	DROP	LOCAL	같이 삭제되므로 상관없다.
		GLOBAL	모든 GLOBAL INDEX가 Unusable
	SPLIT	LOCAL	SPLIT된 파티션 인덱스 Unusable
		GLOBAL	모든 GLOBAL INDEX가 Unusable
	MERGE	LOCAL	머지되어 남는 파티션 인덱스 Unusable
		GLOBAL	모든 GLOBAL INDEX가 Unusable
	RENAME	LOCAL	실제 변경이 없으므로 상관없다.
		GLOBAL	실제 변경이 없으므로 상관없다.
	MOVE	LOCAL	MOVE된 파티션 인덱스 Unusable
		GLOBAL	모든 GLOBAL INDEX가 Unusable
	TRUNCATE	LOCAL	남은 로우가 없으므로 상관없다.
		GLOBAL	모든 GLOBAL INDEX가 Unusable
	EXCHANGE	LOCAL	EXCHANGE한 파티션 인덱스 Unusable
		GLOBAL	모든 GLOBAL INDEX가 Unusable

(표를 보면 알겠지만 Unusable상태가 되는 것은 실제 파티션 테이블 데이터의 RowID를 변경시킨 경우(파티션 단위의 변경이 발생시에도 해당 파티션에 실제 로우 데이터가 있어서 RowID 변경이 발생된 경우)에 해당된다.)

해결 :

가장 간단한 방법!!!!!!!!!!!!

인덱스를 사용하지 않고 해당 파티션을 TABLE FULL SCAN 하면 에러가 발생 하지 않는다.
....이랬다간 아마 상사한테 엄청 깨질것이다..

옵티마이져가 실행계획 수립시에 INDEX UNUSABLE 상태 여부를 체크하지 않으므로 해당 인덱스를 사용하는 실행계획이 수립되고 실제 수행시 오류가 발생하게 됨으로 이미 운용중인 어플리케이션이나 SQL이 정상 수행되지 않는다.

이런 경우에는 skip_unusable_indexes = TRUE 파라메터를 지정하면 수행시에 해당 인덱스가 UNUSABLE이면 이를 사용하지 않고 TABLE FULL SCAN 등을 한다. 경우에 따라서 유용하게 사용 가능하므로 고려 해 볼 수 있다.

위의 내용은 그냥 [참고] 만 하는게 좋을거 같다..

궁극적인 해결방법은 

partition table관련 작업을 한 후에는 table에 걸려 있는 local(partitioned) index 나 global index를 반드시 rebuild해 주어야 한다.

왠만하면 그냥 index Rebuild로 문제를 해결할 수 있다.

global index를 rebuild하려면 non-partition index 인덱스인 경우는 단순히 Rebuild를 실행하면 되지만 global partition index 인덱스는 인덱스 전체를 한번에 재생성 할 수 없다.
다음과 같은 상황이다. 

SQL>alter index SALES_GPNK1 rebuild

"ORA-14086:분할영역된 인덱스는 전체를 다시 만들 수 없습니다." 와 같은 에러를 발생하게 된다.

이에 대한 해결 방법은 

SQL>alter index SALES_GPNK1 rebuild partition SALES_GPI01 ;

위와 같이 인덱스 파티션 단위로 재 생성 해주어야만 한다.

global partition index의 경우 non-partition index 이든 partition index 이든 파티션 테이블 관련 작업인 경우 재 생성이 필수 이므로 파티션 테이블별로 global index의 rebuild 작업용의 스크립트를 작성해 놓아야 빠른 시간안에 관련 인덱스를 재 생성 할 수 있다

출처 : http://ariswear.com/new_blog/archive/20070329