개요

본 문서는 index를 생성하는 동안 해당 테이블에 DML을 수행할 수 있는 online 옵션에 대한 내용을 안내합니다.

기본 옵션으로는  index를 생성하는 동안에는 대상 Table에 DML 사용이 불가합니다.

그러나 Online 옵션 사용 시  index를 (재)생성하는 동안 해당 테이블에 DML을 수행할 수 있도록 합니다.

주의

Online 으로 생성 중 원본 테이블의 transaction이 완료될 때까지 index생성은 완료될 수 없습니다.

시나리오

테스트 환경 구축

-- <session 1>
SQL> create table a (a number);
SQL> insert into a select level from dual connect by level <= 100000;
SQL> commit;
SQL> insert into a values (100001);   -- 세션1 에서 transaction을 유발후 대기 상태

시나리오1. online 옵션없이 index 생성 시

-- <session 1> a 테이블에 transaction을 시작한다.
SQL> insert into a values (100001);

-- <session 2> online 옵션 없이  index 생성 시도시 실패한다.
SQL> create index idx_a on a (a);
TBR-12033: Lock acquisition failed in NOWAIT mode.

시나리오2. online 옵션으로 index 생성 시

-- <session 2> online 옵션으로 index 생성 시
create index idx_a on a (a) online;   -- session 1에서 transaction을 완료할때까지 대기한다.

-- <session 1> a 테이블에 transaction을 완료한다.   
commit;

-- <session 2> 생성 완료 알림
Index 'IDX_A' created.

시나리오3. online 옵션으로 index 생성 중 불완전 종료

-- <session 2> online 옵션으로 index 생성 시 statement canceled
SQL> create index idx_a on a (a) online;
^C
Trying to cancel the current statement by user's request.

TBR-12040: Statement canceled.

-- <session 2> 인덱스 생성을 재시도한다.
SQL> create index idx_a on a (a);
TBR-7102: Duplicate schema object 'TIBERO.IDX_A' exists.

SQL> create index idx_a on a (a) online;
TBR-7102: Duplicate schema object 'TIBERO.IDX_A' exists.

SQL> drop index tibero.idx_a;  
TBR-7249: Index 'IDX_A' is being built or rebuilt. -- drop 시도 또한 실패한다. DBMS_REPAIR.ONLINE_INDEX_CLEAN 패키지로 정리가 필요하다.

방법

1. DDL 명령어를 ONLINE 수행

CREATE INDEX 인덱스명 ONLINE;
ALTER INDEX 인덱스명 REBUILD ONLINE;

2. Temporary Segment 생성 및 사용 (내부 동작)

인덱스를 우선 Temp에 생성(SORT)합니다.

3. Journal Table 생성 (내부 동작)

인덱스 생성 중 테이블 변경 데이터를 적재합니다.

4. Temporary Segment 생성 완료 후, Temp 인덱스로 변경 및 Journal Table 인덱스에 적용 (내부 동작)

주의

Online 수행 도중 실패하거나 중단되었을 경우 반드시 DBMS_REPAIR 패키지의 ONLINE_INDEX_CLEAN 함수를 통해 인덱스 online-rebuild에 대한 클린업을 수행해야 합니다.

참고

Online 옵션으로 인덱스 생성 중 모종의 이유로 끊기게 되면, 인덱스는 unusable상태로 생성되며 임시로 생성되었던 journal 테이블 또한 남습니다. 
따라서 반드시 DBMS_REPAIR.ONLINE_INDEX_CLEAN 패키지를 이용하여 정리해주어야 합니다.

아래 3개 쿼리 조회 결과가 존재한다면 cleanup이 제대로 안되었다고 판단할 수 있습니다.

SQL> select * from sys._dd_idxre;
    OBJ_ID    SGMT_ID PCTFREE_BYTES   INITRANS      FLAGS
---------- ---------- ------------- ---------- ----------
      4344       4347           819          2          2

SQL> select * from sys._dd_idxon;
    OBJ_ID    TYPE_NO      FLAGS
---------- ---------- ----------
      4344          1          2

SQL> select * from dba_objects where OBJECT_NAME like '%SYS_JOURNAL%';
OWNER           OBJECT_NAME                         SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          OBJECT_TYPE_NO CREATED              LAST_DDL_TIME        TIMESTAMP                 STATUS     TEMPORARY
--------------- ----------------------------------- -------------------- ---------- -------------- -------------------- -------------- -------------------- -------------------- ------------------------- ---------- ---------
SYS             SYS_JOURNAL_4344                                               4345           4345 TABLE                             1 2025/11/05           2025/11/05           2025-11-05:13:34:07       VALID      N

DBMS_REPAIR.ONLINE_INDEX_CLEAN

실패하거나 중단된 인덱스 online-build 또는 online-rebuild에 대한 클린업을 수행합니다.

DBMS_REPAIR.ONLINE_INDEX_CLEAN
(
    object_id       IN  BINARY_INTEGER  DEFAULT ALL_INDEX_ID, -- 클린업을 수행할 인덱스의 스키마 객체 ID이다. ALL_INDEX_ID 상수로 지정할 경우 모든 인덱스에 대해 클린업을 수행한다.
    wait_for_lock   IN  BOOLEAN         DEFAULT LOCK_WAIT -- 인덱스와 관련된 테이블, 파티션 또는 서브파티션에 대한 DML LOCK을 요청할 때의 LOCK 모드이다. LOCK_WAIT 상수로 설정할 경우 LOCK을 얻을 때까지 계속 기다린다. LOCK_NOWAIT 상수로 설정할 경우 LOCK을 얻는 데 실패하면 클린업이 바로 중단된다.
)  
RETURN BOOLEAN;

예제

DECLARE
    cleaned BOOLEAN;
BEGIN
    cleaned := false;
    WHILE cleaned = false
    LOOP
        cleaned := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,
                                               DBMS_REPAIR.LOCK_WAIT);
        DBMS_LOCK.SLEEP(10);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    RAISE;
END;
/  -- 모든 인덱스에 대해 클린업을 LOCK_WAIT 모드로 수행