[DB] Oracle 대용량 일괄 UPDATE 프로시저

1. 개요

작업 요건은 단순했습니다. 시스템 전체에 걸쳐 TEN_ID 컬럼의 값을 일괄 변경해야 하는 상황이었습니다. TEN_ID 는 테넌트를 식별하는 공통 키 컬럼으로, 수십 개의 테이블에 걸쳐 존재하는 컬럼이었습니다.

처음에는 단순하게 접근했습니다. USER_TAB_COLUMNS에서 TEN_ID 컬럼을 가진 테이블 목록을 뽑아 루프를 돌면서 테이블마다 UPDATE 를 치는 방식이었습니다.

그런데 곧 문제가 생겼습니다. FK 제약조건 때문이었습니다.

TEN_ID를 참조하는 자식 테이블이 존재하는 상황에서 부모 테이블을 먼저 UPDATE하면 Oracle은 즉시 오류를 발생시킵니다. 그렇다고 자식 테이블을 먼저 UPDATE하자니, 이번엔 또 그 자식 테이블을 참조하는 다른 테이블이 있습니다. 320개 테이블의 FK 참조 관계를 모두 파악해서 UPDATE 순서를 수작업으로 정하는 것은 사실상 불가능한 일이었습니다.

결국 방향을 바꿨습니다. UPDATE 전에 제약조건과 인덱스를 비활성화하고, 병렬 처리를 적용한 뒤, 작업이 끝난 후 다시 복원하는 방식입니다. 결과적으로 320개 테이블, 2,800만 건의 데이터를 15분 안에 처리할 수 있었습니다.

2. 단순 UPDATE가 실패하는 이유

2-1. FK 제약조건의 벽

관계형 데이터베이스에서 외래 키(Foreign Key, FK)는 두 테이블 사이의 참조 무결성을 보장하는 제약조건입니다. 예를 들어 ORDERS 테이블의 TEN_ID 컬럼이 TEN 테이블의 TEN_ID를 참조하고 있다면, TEN 테이블의 TEN_ID 값을 변경하는 순간 ORDERS 테이블의 참조가 깨지기 때문에 Oracle은 즉시 오류를 발생시킵니다.

ORA-02292: integrity constraint violated - child record found

이 문제를 단순하게 해결하려면 참조하는 자식 테이블을 먼저 UPDATE하고, 그다음에 부모 테이블을 UPDATE해야 합니다. 그런데 이 의존 관계가 수십 개의 테이블에 걸쳐 있고, 테이블 간 참조 관계가 복잡하게 얽혀 있다면 순서를 파악하는 것 자체가 쉽지 않습니다. 테이블이 수백 개라면 사실상 수작업으로는 불가능합니다.

가장 근본적인 해결책은 UPDATE를 수행하는 동안 FK 제약조건 자체를 잠시 비활성화하는 것입니다.

2-2. 인덱스가 성능을 잡아먹는 구조

인덱스는 SELECT 성능을 높여주지만, DML(INSERT/UPDATE/DELETE) 성능에는 반대로 작용합니다. 특히 UPDATE의 경우, Oracle은 값이 변경될 때마다 해당 컬럼에 걸린 인덱스 엔트리를 삭제하고 새로운 엔트리를 삽입하는 작업을 내부적으로 수행합니다.

대상 테이블이 수십 개이고, 각 테이블마다 수십만 건의 레코드가 있으며, 해당 컬럼에 인덱스가 걸려 있다면 UPDATE 한 건당 인덱스 유지 비용이 실제 데이터 변경 비용보다 훨씬 커지는 상황이 발생합니다. 전체 작업 시간의 대부분이 인덱스 유지에 소비되는 것입니다.

이 문제를 해결하는 전략은 UPDATE 전에 인덱스를 UNUSABLE 상태로 만들고, UPDATE가 완료된 후에 인덱스를 일괄 재생성하는 것입니다. 인덱스를 처음부터 다시 쌓는 비용이 건건이 유지하는 비용보다 훨씬 저렴하기 때문입니다.

3. 설계 전략 개요

위의 문제들을 해결하기 위한 프로시저의 전체 흐름은 다음과 같습니다.

STEP 1. 제약조건 비활성화 (FK → UNIQUE → PK 순서)
STEP 2. 비고유 인덱스 UNUSABLE 처리
STEP 3. USER_TAB_COLUMNS 기반 동적 병렬 UPDATE
STEP 4. UNUSABLE 인덱스 PARALLEL REBUILD
STEP 5. PK 제약조건 재활성화
STEP 6. UNIQUE 제약조건 재활성화
STEP 7. FK 제약조건 재활성화

각 단계는 독립적으로 보이지만, 순서가 바뀌면 오류가 발생하거나 성능이 크게 저하됩니다. 이 흐름 자체가 이 작업의 핵심 설계입니다.

4. STEP 1 — 제약조건 비활성화

4-1. 제약조건 종류별 처리 순서가 중요한 이유

Oracle의 제약조건 중 DML 성능에 영향을 미치는 제약 조건은 세 가지가 있습니다.

• PK (Primary Key): 테이블의 기본 키입니다. 다른 테이블의 FK가 참조하는 대상이 됩니다.
• UNIQUE: 특정 컬럼의 유일성을 보장합니다. FK 참조 대상이 될 수도 있습니다.
• FK (Foreign Key): 다른 테이블의 PK 또는 UNIQUE를 참조하는 제약조건입니다.

비활성화할 때는 FK를 먼저 끄고, 그다음에 PK와 UNIQUE를 꺼야 합니다. PK를 먼저 비활성화하려고 하면, 해당 PK를 참조하는 FK가 아직 살아있기 때문에 Oracle이 오류를 발생시킵니다.

-- 비활성화 순서: FK → UNIQUE → PK
-- 활성화 순서는 반대:  PK → UNIQUE → FK

코드 상에서는 CONSTRAINT_TYPE DESC 정렬을 활용합니다. Oracle이 제약조건 타입에 사용하는 코드값은 P(Primary Key), R(Referential, 즉 FK), U(Unique)입니다. 이를 알파벳 역순으로 정렬하면 U → R → P 순서가 되며, 덕분에 FK(R)가 PK(P)보다 먼저 비활성화됩니다.

FOR C IN (
    SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE IN ('R', 'P', 'U')
    ORDER BY CONSTRAINT_TYPE DESC  -- U → R → P 순서
) LOOP
    ...
END LOOP;

4-2. CASCADE 옵션

PK를 비활성화할 때는 CASCADE 옵션이 필요할 수 있습니다. CASCADE는 해당 PK를 참조하는 FK들도 함께 비활성화하라는 의미입니다. FK를 먼저 모두 끈 후에 PK를 끄는 구조라면 이미 FK가 비활성화되어 있어 CASCADE가 실질적으로 동작하지 않을 수 있지만, 예외 상황에 대비해 PK에는 CASCADE를 붙여두는 것이 안전합니다.

IF C.CONSTRAINT_TYPE = 'P' THEN
    EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                    ' DISABLE CONSTRAINT ' || C.CONSTRAINT_NAME || ' CASCADE';
ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                    ' DISABLE CONSTRAINT ' || C.CONSTRAINT_NAME;
END IF;

모든 작업은 EXCEPTION WHEN OTHERS THEN NULL로 감싸서, 특정 테이블의 제약조건 비활성화가 실패하더라도 전체 루프가 중단되지 않도록 합니다. 이미 비활성화된 제약조건이나 권한 문제 등으로 일부 실패가 발생할 수 있기 때문입니다.

5. STEP 2 — 인덱스 UNUSABLE 처리

5-1. NONUNIQUE 인덱스만 대상으로 하는 이유

인덱스를 비활성화할 때 주의할 점이 있습니다. UNIQUE 인덱스는 건드리지 않습니다.

UNIQUE 인덱스는 UNIQUE 제약조건 또는 PK 제약조건과 연결되어 있습니다. 이 인덱스를 UNUSABLE로 만들면 제약조건 자체가 무력화되거나, 이후 활성화 과정에서 예기치 않은 오류가 발생할 수 있습니다. 또한 PK 인덱스가 UNUSABLE 상태에서는 해당 테이블에 대한 DML 자체가 거부되는 경우도 있습니다.

따라서 성능 최적화를 위해 비활성화하는 대상은 NONUNIQUE 인덱스로 한정합니다.

FOR IDX IN (
    SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE INDEX_TYPE NOT IN ('LOB')
      AND UNIQUENESS = 'NONUNIQUE'
) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME || ' UNUSABLE';
END LOOP;

INDEX_TYPE NOT IN ('LOB')을 추가하는 이유는 LOB 컬럼에 연결된 인덱스는 일반적인 방법으로 UNUSABLE 처리가 되지 않기 때문입니다. 이 조건 없이 LOB 인덱스에 UNUSABLE을 시도하면 오류가 발생합니다.

인덱스가 UNUSABLE 상태가 되면 Oracle은 해당 인덱스를 DML 과정에서 완전히 무시합니다. UPDATE 시 인덱스 유지 비용이 사라지므로 대용량 변경 작업에서 극적인 성능 향상을 기대할 수 있습니다.

6. STEP 3 — 병렬 UPDATE 실행

6-1. USER_TAB_COLUMNS로 대상 테이블 동적 수집

이 작업의 핵심은 변경 대상 컬럼이 존재하는 테이블을 자동으로 찾아내는 것입니다. 하드코딩으로 테이블 목록을 관리하면, 테이블이 추가되거나 삭제될 때마다 프로시저를 수정해야 합니다. 대신 Oracle의 데이터 딕셔너리 뷰인 USER_TAB_COLUMNS를 활용하면 현재 스키마에서 해당 컬럼을 가진 모든 테이블을 실행 시점에 동적으로 수집할 수 있습니다.

FOR COL IN (
    SELECT TABLE_NAME
    FROM USER_TAB_COLUMNS
    WHERE COLUMN_NAME = 'TEN_ID'  -- 변경 대상 컬럼명
    ORDER BY TABLE_NAME
) LOOP
    ...
END LOOP;

USER_TAB_COLUMNS는 현재 사용자 소유의 테이블 컬럼 정보를 모두 담고 있습니다. ALL_TAB_COLUMNS나 DBA_TAB_COLUMNS를 사용하면 다른 스키마의 테이블까지 포함할 수 있습니다.

6-2. PARALLEL 힌트 동작 원리

각 테이블에 대한 UPDATE는 PARALLEL 힌트를 사용해 병렬로 실행합니다.

EXECUTE IMMEDIATE
    'UPDATE /*+ PARALLEL(t, 4) */ ' || COL.TABLE_NAME || ' t ' ||
    'SET TEN_ID = :1 ' ||
    'WHERE TEN_ID = :2'
    USING AFTR_VALUE, PREV_VALUE;

PARALLEL(t, 4)는 t라는 별칭을 가진 테이블에 대해 4개의 병렬 프로세스를 사용하라는 지시입니다. Oracle은 이 힌트를 받으면 테이블의 데이터를 4개의 구간으로 나누어 각 병렬 프로세스가 동시에 UPDATE를 수행하도록 실행 계획을 수립합니다.

병렬 처리가 유효하게 동작하려면 몇 가지 전제 조건이 필요합니다.

• 데이터베이스 레벨에서 병렬 처리가 활성화되어 있어야 합니다(PARALLEL_MAX_SERVERS 파라미터).
• 해당 세션에 병렬 DML이 허용되어 있어야 합니다. 필요하다면 ALTER SESSION ENABLE PARALLEL DML을 사전에 실행해야 합니다.
• 너무 많은 병렬 프로세스는 오히려 리소스 경합을 유발할 수 있으므로, CPU 코어 수와 서버 부하를 고려해 병렬도를 결정해야 합니다.

7. STEP 4 — 인덱스 REBUILD (PARALLEL + NOLOGGING)

UPDATE가 완료된 후에는 UNUSABLE 상태의 인덱스를 재생성해야 합니다. STEP 2에서 UNUSABLE로 만들었기 때문에, STATUS = 'UNUSABLE' 조건으로 대상을 찾으면 됩니다.

FOR IDX IN (
    SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE STATUS = 'UNUSABLE'
) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME ||
                    ' REBUILD PARALLEL 4 NOLOGGING';
    EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME || ' NOPARALLEL';
END LOOP;

PARALLEL 4는 인덱스 재생성 자체도 4개의 병렬 프로세스로 수행하라는 의미입니다. 대용량 테이블의 인덱스를 순차적으로 재생성하면 시간이 매우 오래 걸리기 때문에, 병렬로 처리해 재생성 시간을 크게 단축합니다.

NOLOGGING은 인덱스 재생성 과정에서 Redo 로그 생성을 최소화하는 옵션입니다. 일반적인 DML은 장애 발생 시 복구를 위해 모든 변경 내역을 Redo 로그에 기록합니다. 그런데 인덱스 REBUILD는 데이터 자체가 아닌 파생 구조물을 만드는 작업이므로, 혹시 장애가 발생하더라도 인덱스를 다시 재생성하면 그만입니다. 따라서 Redo 로그 기록을 생략해 I/O 부하를 줄이는 NOLOGGING 옵션이 매우 효과적입니다.

REBUILD가 끝난 후 NOPARALLEL을 명시적으로 설정하는 이유는, REBUILD 시 사용한 병렬 설정이 인덱스 속성으로 남아 이후 일반 DML에도 불필요하게 병렬 처리를 시도하는 부작용을 방지하기 위함입니다.

8. STEP 5~7 — 제약조건 ENABLE NOVALIDATE 전략

8-1. VALIDATE vs NOVALIDATE 차이

제약조건을 다시 활성화할 때 두 가지 옵션 중 하나를 선택할 수 있습니다.

• ENABLE VALIDATE (기본값): 제약조건을 활성화하면서 기존 데이터 전체를 검사합니다. 모든 데이터가 제약조건을 만족하는지 확인하므로 데이터가 많을수록 시간이 오래 걸립니다.
• ENABLE NOVALIDATE: 제약조건을 활성화하되, 기존 데이터는 검사하지 않습니다. 이후 신규로 입력되는 데이터에 대해서만 제약조건을 적용합니다.

이번 작업에서는 ENABLE NOVALIDATE를 사용합니다. 이유는 명확합니다. UPDATE 작업을 통해 모든 데이터를 일관성 있게 변경했기 때문에 기존 데이터를 다시 검증할 필요가 없습니다. 불필요한 전체 데이터 스캔을 건너뜀으로써 제약조건 활성화 시간을 크게 단축할 수 있습니다.

EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                ' ENABLE NOVALIDATE CONSTRAINT ' || C.CONSTRAINT_NAME;

8-2. PK → UNIQUE → FK 순서의 이유

제약조건 활성화 순서는 비활성화 순서의 정반대입니다. FK는 PK나 UNIQUE를 참조하기 때문에, 참조 대상인 PK와 UNIQUE가 먼저 활성화되어 있어야 FK를 활성화할 수 있습니다. 만약 FK를 먼저 활성화하려고 하면 참조 대상이 아직 비활성화 상태이므로 오류가 발생합니다.

STEP 5. PK     활성화 — 참조 대상 먼저 준비
STEP 6. UNIQUE 활성화 — 참조 대상 준비 완료
STEP 7. FK     활성화 — 참조 대상이 모두 준비된 후 마지막으로 활성화

9. 트랜잭션 설계 — 중간 COMMIT 전략

대용량 일괄 UPDATE에서 트랜잭션 관리는 매우 중요합니다. 모든 테이블의 UPDATE를 하나의 트랜잭션으로 묶으면 두 가지 문제가 발생합니다.

첫째, Undo 세그먼트 부족 문제입니다. Oracle은 트랜잭션 롤백을 위해 변경 전 데이터를 Undo 세그먼트에 저장합니다. 수백만 건의 UPDATE를 하나의 트랜잭션으로 처리하면 Undo 세그먼트가 고갈되어 ORA-01555: snapshot too old 오류가 발생할 수 있습니다.

둘째, 락(Lock) 경합 문제입니다. UPDATE된 레코드는 COMMIT이 이루어지기 전까지 행 수준 잠금이 유지됩니다. 트랜잭션이 길어질수록 잠금 보유 시간이 늘어나고, 다른 세션과의 경합 가능성이 높아집니다.

이를 해결하기 위해 각 테이블 단위로 COMMIT을 수행합니다.

EXECUTE IMMEDIATE 'UPDATE ... SET COL = :1 WHERE COL = :2' ...;
V_COUNT := SQL%ROWCOUNT;
COMMIT;  -- 테이블 단위 중간 COMMIT

테이블 단위로 COMMIT을 하면 각 테이블의 UPDATE가 독립적인 트랜잭션으로 처리됩니다. 중간에 특정 테이블에서 오류가 발생하더라도 이미 COMMIT된 테이블은 롤백되지 않으므로, 오류 발생 테이블만 재처리할 수 있습니다.

단, 이 전략은 중간 실패 시 일부 테이블만 변경된 불일치 상태가 남을 수 있다는 점을 인지해야 합니다. 이 프로시저는 운영 중단 상태에서 수행하는 일회성 작업을 전제로 설계된 것이므로, 서비스 중 실행보다는 점검 시간에 수행하는 것이 안전합니다.

10. 전체 프로시저 코드

지금까지 설명한 모든 내용을 담은 전체 프로시저 코드입니다.

CREATE OR REPLACE PROCEDURE UPDATE_COLUMN_VALUE_FAST (
    P_COLUMN_NAME IN VARCHAR2,  -- 변경 대상 컬럼명
    PREV_VALUE    IN VARCHAR2,  -- 변경 전 값
    AFTR_VALUE    IN VARCHAR2   -- 변경 후 값
) IS
    V_TOTAL_UPDATED NUMBER := 0;
    V_TABLE_COUNT   NUMBER := 0;
    V_COUNT         NUMBER;
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    -- STEP 1: 제약조건 비활성화 (U → R → P 순서)
    FOR C IN (
        SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
        FROM USER_CONSTRAINTS
        WHERE CONSTRAINT_TYPE IN ('R', 'P', 'U')
        ORDER BY CONSTRAINT_TYPE DESC
    ) LOOP
        BEGIN
            IF C.CONSTRAINT_TYPE = 'P' THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                    ' DISABLE CONSTRAINT ' || C.CONSTRAINT_NAME || ' CASCADE';
            ELSE
                EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                    ' DISABLE CONSTRAINT ' || C.CONSTRAINT_NAME;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- STEP 2: NONUNIQUE 인덱스 UNUSABLE 처리
    FOR IDX IN (
        SELECT INDEX_NAME
        FROM USER_INDEXES
        WHERE INDEX_TYPE NOT IN ('LOB')
          AND UNIQUENESS = 'NONUNIQUE'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME || ' UNUSABLE';
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- STEP 3: 동적 병렬 UPDATE
    FOR COL IN (
        SELECT TABLE_NAME
        FROM USER_TAB_COLUMNS
        WHERE COLUMN_NAME = P_COLUMN_NAME
        ORDER BY TABLE_NAME
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE
                'UPDATE /*+ PARALLEL(t, 4) */ ' || COL.TABLE_NAME || ' t ' ||
                'SET ' || P_COLUMN_NAME || ' = :1 ' ||
                'WHERE ' || P_COLUMN_NAME || ' = :2'
                USING AFTR_VALUE, PREV_VALUE;

            V_COUNT := SQL%ROWCOUNT;

            IF V_COUNT > 0 THEN
                V_TABLE_COUNT   := V_TABLE_COUNT + 1;
                V_TOTAL_UPDATED := V_TOTAL_UPDATED + V_COUNT;
            END IF;

            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
        END;
    END LOOP;

    -- STEP 4: 인덱스 REBUILD (PARALLEL + NOLOGGING)
    FOR IDX IN (
        SELECT INDEX_NAME
        FROM USER_INDEXES
        WHERE STATUS = 'UNUSABLE'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME ||
                ' REBUILD PARALLEL 4 NOLOGGING';
            EXECUTE IMMEDIATE 'ALTER INDEX ' || IDX.INDEX_NAME || ' NOPARALLEL';
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- STEP 5: PK 활성화
    FOR C IN (
        SELECT CONSTRAINT_NAME, TABLE_NAME
        FROM USER_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'P' AND STATUS = 'DISABLED'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                ' ENABLE NOVALIDATE CONSTRAINT ' || C.CONSTRAINT_NAME;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- STEP 6: UNIQUE 활성화
    FOR C IN (
        SELECT CONSTRAINT_NAME, TABLE_NAME
        FROM USER_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'U' AND STATUS = 'DISABLED'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                ' ENABLE NOVALIDATE CONSTRAINT ' || C.CONSTRAINT_NAME;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- STEP 7: FK 활성화
    FOR C IN (
        SELECT CONSTRAINT_NAME, TABLE_NAME
        FROM USER_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'R' AND STATUS = 'DISABLED'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME ||
                ' ENABLE NOVALIDATE CONSTRAINT ' || C.CONSTRAINT_NAME;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('완료: ' || V_TABLE_COUNT || '개 테이블, ' ||
        TO_CHAR(V_TOTAL_UPDATED, '999,999,999') || '건 업데이트');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;
END;