Oracle 19c ORA-01502: UNUSABLE 인덱스를 진단하고 안전하게 REBUILD하는 순서

Oracle 19c에서 배치 직후 ORA-01502가 발생했다면, 먼저 애플리케이션 SQL을 바꾸기보다 어떤 인덱스가 UNUSABLE인지 확인해야 한다. 직접 경로 적재나 일부 DDL 뒤에는 인덱스 또는 인덱스 파티션이 사용 불가 상태가 될 수 있으며, 이때는 영향 범위를 나눠 확인한 뒤 해당 인덱스만 재구축하는 편이 안전하다.

이 글은 Oracle Database 19c의 일반 B-tree 인덱스를 기준으로 한다. UNIQUE 제약을 지지하는 인덱스, 파티션 인덱스, 대량 적재가 아직 끝나지 않은 경우는 조치 순서가 달라질 수 있으므로, 오류가 난 세션에서 곧바로 ALTER INDEX ... REBUILD를 실행하지 않는다.

적용 조건과 영향 범위

UNUSABLE이 된 이유부터 분리한다

UNUSABLE은 손상 진단명과 같지 않다. Oracle은 직접 경로 적재나 DDL 같은 작업 뒤 인덱스를 사용 불가로 표시할 수 있다. 대량 적재 성능을 위해 의도적으로 인덱스를 사용 불가로 만든 뒤 적재 후 다시 만드는 운영 방식도 있다. 반대로 인덱스 생성·재구축이 실패한 경우에도 상태가 남을 수 있다. 따라서 첫 질문은 “어떤 SQL이 느린가”가 아니라 “어떤 작업이 어떤 인덱스를 이 상태로 만들었는가”다.

SKIP_UNUSABLE_INDEXES=TRUE는 기본값이며, 일반적인 사용 불가 인덱스를 옵티마이저가 무시하도록 한다. 이것이 SELECT가 모두 안전하다는 뜻은 아니다. 힌트로 해당 인덱스를 강제하면 ORA-01502가 날 수 있고, UNIQUE 제약을 지지하는 인덱스는 DML 유지가 필요하므로 오류를 건너뛰지 않는다. 파라미터를 시스템 전체에서 바꾸어 급한 오류만 없애는 방식은 원인과 데이터 품질 위험을 가린다.

최소 재현: 상태를 읽고 범위를 정하는 방법

아래 예제는 교육용 스키마에서 인덱스 상태를 확인하는 쿼리다. 운영에서는 소유자와 인덱스 이름을 바인드 또는 배포 변수로 전달하고, 결과를 변경 요청 기록에 남긴다.

SELECT index_name,
       table_name,
       status,
       uniqueness
FROM   user_indexes
WHERE  status = 'UNUSABLE'
ORDER BY table_name, index_name;

파티션 테이블이라면 전체 인덱스의 상태만 보고 끝내지 않는다. 로컬 인덱스는 일부 파티션만 UNUSABLE일 수 있으므로 파티션 뷰를 함께 확인한다.

SELECT index_name,
       partition_name,
       status
FROM   user_ind_partitions
WHERE  status = 'UNUSABLE'
ORDER BY index_name, partition_position;

실행 전 확인: 재구축보다 먼저 볼 것

SELECT c.constraint_name,
       c.constraint_type,
       c.status,
       c.index_name
FROM   user_constraints c
WHERE  c.table_name = 'SALES_STAGE'
AND    c.constraint_type IN ('P', 'U')
ORDER BY c.constraint_type, c.constraint_name;

복구 SQL: 필요한 단위만 재구축한다

비파티션 인덱스가 UNUSABLE이고 작업 창에서 DML 중단이 가능하다면 가장 단순한 복구는 전체 인덱스 재구축이다. 아래 이름은 예시이므로 실제 객체명으로 바꾸기 전에 앞선 조회 결과와 반드시 대조한다.

ALTER INDEX sales_stage_ix1 REBUILD;

로컬 파티션 인덱스에서 특정 파티션만 UNUSABLE이면 그 파티션만 재구축하는 것이 범위를 줄인다.

ALTER INDEX sales_stage_lix
  REBUILD PARTITION p202608;

업무 DML을 허용해야 한다는 이유만으로 ONLINE을 자동 선택하면 안 된다. Oracle 19c에서 온라인 인덱스 재구축 중 병렬 DML은 지원되지 않는다. 배치가 ALTER SESSION ENABLE PARALLEL DML 또는 PARALLEL 힌트를 사용한다면, 해당 세션·작업 창을 분리하거나 온라인 옵션을 쓰지 않는 계획을 정해야 한다. 온라인 작업의 잠금·TEMP·병렬 DML 경계는 Oracle 19c ONLINE 인덱스 생성의 운영 절차에서 이어서 확인할 수 있다.

ALTER INDEX sales_stage_ix1
  REBUILD ONLINE;

실행 후 검증과 롤백 경계

SELECT index_name,
       status
FROM   user_indexes
WHERE  index_name = 'SALES_STAGE_IX1';

재구축은 데이터 적재의 롤백이 아니다. 이미 커밋된 대량 적재가 잘못되었다면 인덱스를 다시 만든 뒤에도 데이터는 남는다. 데이터 복구는 배치의 업무 키, 커밋 경계, 백업·복구 정책에 따라 별도로 결정해야 한다. 원본 행이 중복되어 배치 자체가 실패했다면 MERGE 원본 중복을 안전하게 찾는 방법처럼 적재 원인을 별도 진단한다. 인덱스 REBUILD 실패 시에도 무작정 DROP부터 하지 말고 실패 원인, 공간, 대상 객체, 제약조건 연결을 다시 확인한다. CREATE 문을 정확히 재현할 수 없거나 힌트·압축·로깅 속성을 잃을 위험이 있다면 DROP/CREATE보다 REBUILD가 보수적인 선택일 때가 많다.

자주 하는 실수

• ORA-01502가 난 SQL만 수정한다. 강제 INDEX 힌트, UNIQUE 제약, 실제 UNUSABLE 범위를 함께 확인하지 않으면 같은 오류가 다른 경로에서 다시 난다.
• 모든 인덱스를 재구축한다. 파티션 하나의 문제를 전체 작업으로 넓히면 I/O와 잠금 위험만 커진다.
• SKIP_UNUSABLE_INDEXES로 끝낸다. 일반 인덱스 접근 경로를 바꿀 뿐, 인덱스 유지·제약·재구축 책임을 없애지 않는다.
• 온라인 REBUILD를 무조건 안전하다고 본다. 온라인 인덱스 작업은 병렬 DML과 함께 쓸 수 없고, 작업 중인 배치와의 순서 조정이 필요하다.

마무리

ORA-01502 대응의 순서는 단순하다. 상태를 읽고, 파티션·제약조건·적재 커밋 범위를 분리한 다음, 필요한 인덱스 또는 파티션만 재구축하고 다시 조회한다. 이 순서를 지키면 “인덱스가 깨졌다”는 막연한 대응 대신, 데이터 적재와 인덱스 복구를 서로 다른 위험으로 다룰 수 있다.

공식 출처

• Oracle Database Error Messages: ORA-01502
• Oracle Database 19c Administrator’s Guide: Managing Indexes
• Oracle Database 19c Reference: SKIP_UNUSABLE_INDEXES
• Oracle Database 19c SQL Language Reference: ALTER INDEX
• Oracle Database 19c Administrator’s Guide: Direct-Path INSERT