Oracle 19c ONLINE 인덱스 생성: 잠금·병렬 DML·TEMP를 확인하는 운영 절차

Oracle 19c의 CREATE INDEX ... ONLINE은 인덱스를 만드는 동안 기본 테이블의 DML(INSERT·UPDATE·DELETE)을 허용합니다. 다만 DDL은 함께 실행할 수 없고, 병렬 DML도 지원하지 않습니다. 대용량 테이블이라면 “온라인이니 언제든 실행해도 된다”가 아니라, 낮은 DML 시간대·TEMP 여유·기존 인덱스 중복·작업 중 변경 금지를 먼저 확인하는 작업입니다.

이 글은 일반 힙 테이블에 B-tree 보조 인덱스를 추가하는 Oracle Database 19c 기준의 절차입니다. 파티션, IOT, 비트맵, 제약조건을 위한 인덱스는 설계와 제한이 달라질 수 있으므로, 같은 명령을 운영 환경에 바로 적용하지 마세요.

적용 조건과 먼저 멈춰야 할 경우

왜 ONLINE을 선택해도 배포 창이 필요한가

일반 인덱스 생성은 테이블을 읽고 키를 정렬해 인덱스 세그먼트를 만듭니다. ONLINE을 붙이면 그 과정 중에도 기본 테이블의 DML이 계속될 수 있습니다. 대신 새로 들어오거나 바뀌는 행까지 인덱스에 반영해야 하므로, 동시에 쓰기 작업이 많을수록 완료 시점은 늦어집니다.

따라서 이 옵션은 “서비스를 멈추지 않는 마법”보다 “읽기·쓰기를 유지하면서 인덱스 작업을 관리할 수 있는 방식”에 가깝습니다. 같은 테이블에 컬럼 변경, 테이블 이동, 파티션 작업처럼 DDL을 함께 넣으면 안 됩니다. 배포 캘린더에서 대상 테이블을 잠시 단독 변경 대상으로 잡는 편이 안전합니다.

최소 재현 예제

아래는 테스트 스키마용 예제입니다. 운영 테이블 이름이나 테이블스페이스 이름을 그대로 복사하지 마세요.

CREATE TABLE app_orders (
    order_id      NUMBER        NOT NULL,
    customer_id   NUMBER        NOT NULL,
    created_at    DATE          NOT NULL,
    order_status  VARCHAR2(20)  NOT NULL,
    amount        NUMBER(12, 2) NOT NULL,
    CONSTRAINT app_orders_pk PRIMARY KEY (order_id)
);

INSERT INTO app_orders
    (order_id, customer_id, created_at, order_status, amount)
VALUES
    (1001, 101, DATE '2026-08-01', 'PAID', 25000);

COMMIT;

목적: 인덱스가 필요한 조회 조건을 분명히 합니다. 권한: 자신의 스키마에서 테이블을 만들 수 있는 테스트 계정. 예상 결과: 한 행이 저장된 테스트 테이블이 만들어집니다. 이 예제만으로 운영 규모의 작업 시간이나 잠금 양상을 검증했다고 볼 수는 없습니다.

실행 전 확인: 인덱스보다 먼저 보는 네 가지

1. 조회 조건이 맞는지: customer_id로 좁힌 뒤 created_at 범위를 읽는 SQL이 실제로 있는지 확인합니다. 인덱스는 모든 느린 SQL의 해결책이 아닙니다.
2. 같은 선두 컬럼의 인덱스가 있는지: 비슷한 인덱스를 하나 더 만들면 쓰기 비용만 늘 수 있습니다.
3. TEMP·인덱스 테이블스페이스 여유가 있는지: Oracle은 매우 큰 인덱스 생성에 별도 임시 테이블스페이스를 고려하라고 안내합니다. 공용 TEMP를 무작정 키우기보다 작업 계정과 운영 정책을 먼저 확인합니다.
4. 변경 창에 DDL·병렬 배치가 없는지: 온라인 빌드 중 병렬 DML은 오류가 납니다. 대상 테이블의 배치, 마이그레이션, 파티션 유지보수 일정을 분리합니다.

기존 인덱스 확인 SQL

SELECT i.index_name,
       i.index_type,
       i.visibility,
       i.status,
       LISTAGG(c.column_name, ', ')
         WITHIN GROUP (ORDER BY c.column_position) AS index_columns
FROM user_indexes i
JOIN user_ind_columns c
  ON c.index_name = i.index_name
WHERE i.table_name = 'APP_ORDERS'
GROUP BY i.index_name, i.index_type, i.visibility, i.status
ORDER BY i.index_name;

목적: 대상 테이블의 기존 인덱스와 컬럼 순서를 확인합니다. 권한: 자신의 스키마에서는 추가 권한이 필요하지 않습니다. 다른 스키마라면 권한에 맞는 ALL_INDEXES, ALL_IND_COLUMNS로 범위를 좁혀 조회합니다. 예상 결과: 인덱스 이름, 상태, 가시성, 컬럼 순서가 한 행씩 나옵니다.

작업 SQL: ONLINE 인덱스 생성

CREATE INDEX app_orders_cust_created_ix
    ON app_orders (customer_id, created_at)
    TABLESPACE app_idx
    ONLINE;

목적: 고객별 기간 조회에 사용할 복합 B-tree 인덱스를 온라인으로 생성합니다. 권한: 대상 테이블이 자신의 스키마에 있거나 해당 테이블의 INDEX 권한이 필요합니다. 다른 스키마에 인덱스를 만들 때는 CREATE ANY INDEX와 소유자의 테이블스페이스 quota 또는 UNLIMITED TABLESPACE가 필요합니다. 예상 결과: 문이 성공하면 인덱스가 생성됩니다. 작업 시간은 테이블 크기와 동시 DML 양에 따라 달라집니다.

TABLESPACE app_idx는 예시입니다. 인덱스 테이블스페이스를 명시하지 않는 운영 표준이라면 제거하되, 어느 테이블스페이스에 생성될지 사전에 확인하세요. NOLOGGING, PARALLEL은 복구·공간·부하 정책을 바꾸므로 이 기본 절차에 섞지 않았습니다.

실행 중과 실행 후 검증

긴 작업은 작업 세션을 식별한 뒤 V$SESSION_LONGOPS에서 경과 시간과 남은 시간을 볼 수 있습니다. 이 뷰 조회에는 별도 운영 권한이 필요합니다. 모든 인덱스 생성이 이 뷰에 같은 모양으로 표시된다고 가정하지 말고, 작업 전 테스트 환경에서 표시 여부를 확인하세요.

SELECT sid,
       serial#,
       opname,
       sofar,
       totalwork,
       units,
       elapsed_seconds,
       time_remaining
FROM v$session_longops
WHERE totalwork > 0
  AND sofar < totalwork
ORDER BY elapsed_seconds DESC;

완료 뒤에는 데이터 딕셔너리에서 상태와 가시성을 확인합니다.

SELECT index_name,
       status,
       visibility,
       tablespace_name,
       last_analyzed
FROM user_indexes
WHERE table_name = 'APP_ORDERS'
  AND index_name = 'APP_ORDERS_CUST_CREATED_IX';

예상 결과: 정상 생성된 인덱스 한 행이 보입니다. 그다음 대표 조회를 실제 바인드 값으로 실행하고, 애플리케이션의 실행계획 수집 방식이나 DBMS_XPLAN.DISPLAY_CURSOR로 새 인덱스가 선택되는지 별도로 확인합니다. 인덱스가 보인다는 사실과 성능 개선은 다른 검증 항목입니다.

실패·잠금·성능 영향에서 놓치기 쉬운 점

• 온라인 빌드 중 DML은 가능하지만 대상 테이블의 DDL은 불가합니다. 배포 작업이 겹치면 작업 순서를 조정합니다.
• 병렬 DML은 온라인 인덱스 생성·재구축 중 지원되지 않습니다. 병렬 배치가 있으면 창을 분리하거나 담당자와 조율합니다.
• 인덱스 하나가 추가되면 해당 키를 바꾸는 INSERT·UPDATE·DELETE에는 인덱스 유지 비용이 생깁니다. 읽기 이득과 쓰기 비용을 함께 봅니다.
• Oracle 문서는 온라인 빌드 시간이 테이블 크기와 동시 DML 양에 비례한다고 설명합니다. 쓰기 폭주 시간대에는 완료 예측이 더 어려워집니다.
• 작업 실패 뒤 인덱스가 UNUSABLE로 남아 있다면, 그 인덱스는 재구축하거나 제거한 뒤 다시 만들어야 합니다. 상태만 보고 재시도하지 말고 실패 원인과 공간·동시 작업 여부를 먼저 기록합니다.

롤백과 복구 경계

DROP INDEX app_orders_cust_created_ix;

목적: 이 글의 예제처럼 명시적으로 만든 보조 인덱스를 제거합니다. 권한: 자신의 스키마에 있거나 DROP ANY INDEX 권한. 주의: 활성화된 PRIMARY KEY·UNIQUE 제약을 지키는 인덱스에는 적용하지 않습니다. Oracle은 그 인덱스만 따로 삭제할 수 없으며 제약조건을 먼저 검토해야 한다고 안내합니다.

마무리

ONLINE 인덱스 생성의 판단 기준은 문법 한 줄이 아니라 운영 조건입니다. 기존 인덱스와 대표 SQL을 먼저 확인하고, DDL·병렬 DML이 없는 창을 잡고, TEMP·테이블스페이스 여유를 확인한 뒤 실행합니다. 완료 후에는 인덱스 상태와 실제 계획을 분리해서 검증해야 합니다.

공식 출처

• Oracle Database 19c Administrator’s Guide — Managing Indexes
• Oracle Database 19c SQL Language Reference — CREATE INDEX
• Oracle Database 19c Administrator’s Guide — Managing Processes