[Oracle] 온라인 인덱스 작업이 불가능한 상황

운영 DB 인덱스 생성, 리빌드의 한줄기 빛과 희망은 'ONLINE' 옵션이다.

DML이 실시간으로 발생하더라도 테이블 LOCK 없이 INDEX 생성 또는 REBUILD가 가능하다.

 

-- 일반적으로 사용하는 인덱스 생성 DDL
CREATE INDEX 스키마.인덱스이름
ON 스키마.테이블이름 (컬럼들)
TABLESPACE 테이블스페이스
STORAGE
(
  INITIAL 64K
  NEXT 1M
)
NOLOGGING 
ONLINE      -- 온라인 옵션
;

ALTER INDEX 스키마.인덱스이름 LOGGING;

 

그런데, ONLINE 인덱스 작업이 불가능한 상황이 있다.

 

VARCHAR(4000)인 컬럼의 인덱스를 생성하는데,

일반적인 데이터의 길이는 20Bytes 이내로 끝나는 경우가 많고 distinct가 좋아 

인덱스 생성 중이었는데 아래와 같은 오류가 발생했다.

 

ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다

ORA-01450: 키의 최대 길이(3215)를 초과했습니다

 

원인은 컬럼의 길이가 너무 길어 online 작업이 불가했다.

8K block size를 이용하고 있었는데 

아래 조건을 만족해야 ONLINE INDEX 작업이 가능했다.

If 8K block size then maximum index key length=3218 If 16K block size then maximum index key length=6498 How the maximum index key length is measured by? Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

 

그래서 결국 서비스 트랜잭션이 거의 없는 시간대에

PARALLEL로 ONLINE이 아닌 인덱스 생성을 진행했다.

 

테이블 크기가 크지 않아 PARALLEL 8로 생성했을때 1초 이내 생성됐고 동시간대 DML이 없어 

서비스 영향도는 적었지만, 

 

트랜잭션이 더 많은 TABLE에

VARCHAR2(4000) 컬럼의 인덱스 생성시에는 REDEFINITION하는 방식을 고려해야겠다. 

 

* 참고 자료 : 

https://docs.oracle.com/database/121/CCAPP/GUID-731485C4-BEAB-4044-849C-35FB19FB3F64.htm#CCAPP9035

https://community.oracle.com/tech/developers/discussion/comment/10668808/#Comment_10668808

https://docs.oracle.com/database/121/ERRMG/ORA-00910.htm#ERRMG-GUID-D9EBDFFA-88C6-4185-BD2C-E1B959A97274

https://samadhandba.wordpress.com/tag/ora-01450/