오라클 UNDO 최소화 및 데이터 저장 전략(ㅇ)

UNDO를 최소화하면서 테이블을 하루에 한 번 업데이트, 삭제, 그리고 신규 데이터 삽입으로 현행화를 유지하는 최적화된 방법은 다음과 같습니다. 이 작업을 효율적으로 수행하려면 MERGE 문과 테이블 파티셔닝 또는 임시 테이블을 활용할 수 있습니다.

1. MERGE를 이용한 UPSERT와 DELETE

• 오라클의 MERGE 문은 기존 데이터의 업데이트와 새로운 데이터의 삽입을 한 번의 SQL 문으로 처리할 수 있습니다. 삭제 작업은 별도로 수행합니다.
• 데이터 흐름

1. 임시 테이블 (STAGING_TABLE): 외부에서 데이터를 로드하여 비교의 기준이 되는 임시 테이블.
2. MERGE:
• STAGING_TABLE과 대상 테이블(TARGET_TABLE)을 비교하여 업데이트 및 삽입 처리.
3. DELETE:
• STAGING_TABLE에 없는 데이터는 삭제.

• 구현 방법

-- 임시 테이블에 최신 데이터를 로드
CREATE TABLE STAGING_TABLE AS SELECT * FROM SOURCE_DATA WHERE 1=0;

-- 하루 동안 데이터 로드
INSERT /*+ APPEND */ INTO STAGING_TABLE
SELECT * FROM SOURCE_DATA;

-- MERGE를 사용하여 업데이트 및 삽입 처리
MERGE INTO TARGET_TABLE T
USING STAGING_TABLE S
ON (T.KEY_COLUMN = S.KEY_COLUMN)
WHEN MATCHED THEN
    UPDATE SET
        T.COLUMN1 = S.COLUMN1,
        T.COLUMN2 = S.COLUMN2
        -- 필요한 컬럼 업데이트
WHEN NOT MATCHED THEN
    INSERT (T.KEY_COLUMN, T.COLUMN1, T.COLUMN2)
    VALUES (S.KEY_COLUMN, S.COLUMN1, S.COLUMN2);

-- STAGING_TABLE에 없는 데이터 삭제
DELETE FROM TARGET_TABLE
WHERE NOT EXISTS (
    SELECT 1
    FROM STAGING_TABLE S
    WHERE TARGET_TABLE.KEY_COLUMN = S.KEY_COLUMN
);

-- 임시 테이블 정리
TRUNCATE TABLE STAGING_TABLE;

 

2. UNDO 최소화 전략

• DIRECT PATH INSERT: INSERT /*+ APPEND */ 힌트를 사용하면 REDO/UNDO를 최소화할 수 있습니다.
• NOLOGGING 옵션: 임시 테이블과 타겟 테이블 모두에 NOLOGGING 옵션을 설정하면 UNDO와 REDO를 줄일 수 있습니다.
• PARALLEL 처리: 대용량 데이터를 처리할 때 PARALLEL 힌트를 사용하여 병렬로 작업 속도를 향상합니다.

3. 테이블 파티셔닝

• 만약 데이터가 크고 일정한 기간에 따라 갱신된다면 파티셔닝을 통해 데이터 관리 효율성을 높일 수 있습니다.
• 파티션 교체 전략

1. STAGING_TABLE에 데이터를 로드.
2. EXCHANGE PARTITION을 사용하여 파티션을 교체.
3. 오래된 파티션은 삭제.

ALTER TABLE TARGET_TABLE
EXCHANGE PARTITION target_partition
WITH TABLE STAGING_TABLE;

4. PL/SQL로 통합 작업 : 위의 논리를 PL/SQL 블록으로 작성하여 스케줄러에서 매일 자동으로 실행할 수 있습니다.

BEGIN
    -- 1. STAGING_TABLE 초기화 및 데이터 로드
    EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGING_TABLE';
    INSERT /*+ APPEND */ INTO STAGING_TABLE
    SELECT * FROM SOURCE_DATA;

    -- 2. MERGE로 업데이트 및 삽입
    MERGE INTO TARGET_TABLE T
    USING STAGING_TABLE S
    ON (T.KEY_COLUMN = S.KEY_COLUMN)
    WHEN MATCHED THEN
        UPDATE SET
            T.COLUMN1 = S.COLUMN1,
            T.COLUMN2 = S.COLUMN2
    WHEN NOT MATCHED THEN
        INSERT (T.KEY_COLUMN, T.COLUMN1, T.COLUMN2)
        VALUES (S.KEY_COLUMN, S.COLUMN1, S.COLUMN2);

    -- 3. STAGING_TABLE에 없는 데이터 삭제
    DELETE FROM TARGET_TABLE
    WHERE NOT EXISTS (
        SELECT 1
        FROM STAGING_TABLE S
        WHERE TARGET_TABLE.KEY_COLUMN = S.KEY_COLUMN
    );

    -- 4. STAGING_TABLE 정리
    EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGING_TABLE';
END;
/