엑시엄이 보는 DB 세상

파티션 테이블 변경 시 파티션 인덱스 관리

기업에서 관리하는 데이터는 시간이 갈수록 증가하고 있으며, 그 중 몇몇의 테이블은 데이터가 지속적으로 빠르게 증가하고 있다. 이렇게 빠르게 증가하는 대용량 테이블을 효과적으로 관리하기 위해 우리는 테이블을 작은 단위의 파티션으로 나누어 관리하는 파티션 테이블을 사용한다. 이때, 파티션 인덱스의 역할도 중요하다. 이에 대한 관리방법을 알아보자.

파티션 테이블의 장점으로는 첫째, 성능 관점에서 굉장한 이점이 존재한다는 것이다. 파티션 테이블은 데이터를 액세스 할 때 필요한 파티션만 액세스하여 액세스 범위를 줄여주며, 이는 시스템의 메모리를 효율적으로 사용할 수 있게 하고 결론적으로 성능향상을 가져온다. 둘째, 가용성의 향상이다. 물리적인 파티셔닝으로 인한 전체 데이터의 훼손 가능성이 줄어 들고 , 동일 테이블에서 UNUSABLE 된 파티션은 다른 파티션에 영향을 주지 않는다.

셋째, 관리의 용이성이다. 보관주기가 존재하는 대용량 테이블에 대해서 파티션 단위로 변경이 가능하기 때문에 보관주기가 도래한 파티션에 대해서는 파티션 단위 삭제기능을 사용하여 대용량데이터의 관리할 수 있다. 하지만, 이렇게 파티션 테이블은 세분화된 파티션에 의한 장점이 있는 반면 세분화된 파티션에 의해 세심한 관리가 요구된다. 파티션테이블은 다양한 명령어를 통해 파티션의 추가, 삭제, 분할, 병합 등의 변경이 가능하며, 상황에 맞게 다양한 방법을 사용하여 파티션 오브젝트를 관리하게 된다.

이렇게 파티션테이블을 변경한 후 테이블을 액세스 하려고 할 때 , 우리는 가끔

'ORA-01502 : 인덱스 분할영역을 사용할 수 없습니다'

라는 오라클 에러를 접하게 된다.

이번 기고문에서는 이러한 에러메시지가 발생하는 이유와 파티션테이블 변경에 따른 파티션인덱스의 관리 방법에 대해서 알아보고자 한다. 파티션테이블은 파티션별로 ROWID를 다르게 가지며 파티션의 변경이 발생하면 ROWID 또한 변경이 발생하게 된다. 이에 따라 변경된 파티션테이블의 ROWID와 해당 테이블의 로컬인덱스와 글로벌인덱스의 ROWID가 일치하지 않으면 인덱스는 IU상태(INDEX UNUSABLE)가 되어 더 이상 해당 인덱스를 사용할 수 없게 된다. 이러한 상황에서 해당 인덱스를 액세스하고자 할 때 ‘ORA-01502 : 인덱스 분할영역을 사용할 수 없습니다’와 같은 에러 메시지가 발생하는 것이다.

옵티마이저는 실행계획 수립 시 인덱스 UNUSABLE 상태를 체크하지 않으므로 UNUSABLE된 인덱스를 사용한 실행계획이 수립되면 해당 SQL은 정상적으로 수행되지 않는다. 물론, SKIP_UNUSABLE_INDEXES 파라미터 설정을 통해 인덱스 상태가 UNUSABLE 일 때, 테이블 FULL SCAN을 유도하여 어플리케이션에서 발생하는 에러는 피할 수 있다.

하지만 테이블 FULL SCAN으로 인한 성능 저하와 파티션 변경이 아닌 다른 이유로 발생한 IU에 대한 원인을 찾기 힘들어질 수도 있음을 주의해야 한다. <표 1>은 파티션테이블 변경작업에 따른 인덱스 상태를 테스트한 결과다. <표 1>을 통해 파티션변경 작업 별 인덱스 재생성 필요여부에 대해 알 수 있다.



이 중 UNUSABLE 인덱스에 대해서는 파티션 변경작업 이후, 해당 인덱스 재생성을 통해 정상적으로 인덱스 액세스가 가능하게 해줘야 한다. UNUSABLE 인덱스 조회는 다음과 같다.

SELECT INDEX_OWNER,INDEX_NAME, PARTITIOIN_NAME, STATUS
FROM DBA_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE' ;

UNUSABLE 파티션 인덱스를 리빌드하는 명령은 다음과 같다.

ALTER INDEX [인덱스명] REBUILD PARTITION [파티션명];

하지만, 인덱스재생성 작업은 운영시스템에 부하를 주기 때문에 작업시간을 고려하지 않을 수 없다. 그렇다면 파티션 변경작업은 언제 해야 할까 파티션 변경 자체로도 운영시스템에 부하를 주는 경우도 있으며, 위에서 언급했듯이 파티션 변경 후에 인덱스 재생성을 해줘야 하는 경우에도 운영시스템에 부하를 주기 때문에 보통은 운영시스템을 가장 적게 사용하는 시간대인 새벽 시간에 JOB으로 등록하여 작업한다. 사이트마다 운영 정책에 따라 다르겠지만, 1개월 또는 6개월 또는 1년 주기로 파티션 변경 JOB이 수행된다. 이때 파티션의 변경작업 후에 반드시 인덱스 상태를 체크하여 재생성해주는 로직을 추가해야 한다.

만일 인덱스 상태체크 로직을 추가하지 않고 단순 파티션 변경 작업만 등록하여 파티션을 관리 한다면 우리는 어느 날 새벽 장애발생으로 인해 자다 말고 정신 없이 회사에 출근해야 하는 비극을 맞이하게 될 것이다. 지금까지 이야기한 내용은 파티션테이블을 사용함에 있어 아주 기본적인 인덱스 관리에 대한 내용이었다. 하지만 이러한 기본적인 부분을 간과했을 시 운영DB에 발생할 장애는DBA라면 누구나 알 수 있을 것이다. 이렇게 장점이 많은 파티션테이블의 안정적인 사용을 위해 기본적인 부분부터 세심한 관리와 주의를 기울여 예측할 수 있는 장애로부터 자유로운 DBA가 되길 바란다.