[ Oracle ] update join view 이용하여 빠른 업데이트

Oracle UPDATE JOIN 빠른 업데이트

Oracle에서 다른 테이블의 값을 참조하여 UPDATE할 때, 서브쿼리 대신 UPDATE JOIN을 사용하면 성능을 크게 향상시킬 수 있습니다.

1. 일반적인 서브쿼리 UPDATE (느림)

-- 서브쿼리 방식 (비효율적)
UPDATE tb_order o
SET o.customer_name = (
    SELECT c.name 
    FROM tb_customer c 
    WHERE c.customer_id = o.customer_id
)
WHERE EXISTS (
    SELECT 1 FROM tb_customer c 
    WHERE c.customer_id = o.customer_id
);
-- 문제: 행마다 서브쿼리 실행

2. UPDATE JOIN (Updatable View)

-- 인라인 뷰를 이용한 UPDATE
UPDATE (
    SELECT 
        o.customer_name AS old_name,
        c.name AS new_name
    FROM tb_order o
    JOIN tb_customer c ON o.customer_id = c.customer_id
)
SET old_name = new_name;

-- 조건 추가
UPDATE (
    SELECT 
        o.price AS old_price,
        p.new_price AS new_price
    FROM tb_order o
    JOIN tb_product p ON o.product_id = p.product_id
    WHERE o.order_date >= TRUNC(SYSDATE)
)
SET old_price = new_price;

3. Key Preserved 조건

-- 오류 발생 시
ORA-01779: cannot modify a column which maps to a non key-preserved table

-- 해결: 조인 대상 테이블에 PK/UK 필요
-- tb_customer.customer_id가 PK여야 함

-- 힌트로 우회 (주의 필요)
UPDATE /*+ BYPASS_UJVC */ (
    SELECT o.price, p.new_price
    FROM tb_order o
    JOIN tb_product p ON o.product_id = p.product_id
)
SET price = new_price;

4. MERGE 문 활용

-- MERGE를 이용한 UPDATE
MERGE INTO tb_order o
USING tb_customer c
ON (o.customer_id = c.customer_id)
WHEN MATCHED THEN
    UPDATE SET o.customer_name = c.name;

-- 조건부 MERGE
MERGE INTO tb_order o
USING (
    SELECT customer_id, name, grade
    FROM tb_customer
    WHERE grade = "VIP"
) c
ON (o.customer_id = c.customer_id)
WHEN MATCHED THEN
    UPDATE SET 
        o.customer_name = c.name,
        o.discount_rate = 0.1;

5. 성능 비교

6. 대량 UPDATE 최적화

-- 병렬 처리
ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL(o, 4) */ (
    SELECT o.amount, c.credit_limit
    FROM tb_order o
    JOIN tb_customer c ON o.customer_id = c.customer_id
)
SET amount = credit_limit * 0.1;

COMMIT;

-- 배치 처리
DECLARE
    CURSOR c_batch IS
        SELECT ROWID AS rid, customer_id FROM tb_order;
    TYPE t_batch IS TABLE OF c_batch%ROWTYPE;
    v_batch t_batch;
BEGIN
    OPEN c_batch;
    LOOP
        FETCH c_batch BULK COLLECT INTO v_batch LIMIT 10000;
        EXIT WHEN v_batch.COUNT = 0;
        
        FORALL i IN 1..v_batch.COUNT
            UPDATE tb_order SET ... WHERE ROWID = v_batch(i).rid;
        
        COMMIT;
    END LOOP;
    CLOSE c_batch;
END;

주의사항

• 조인 테이블에 PK/UK 있어야 UPDATE 가능


• BYPASS_UJVC 힌트는 데이터 무결성 주의


• 대량 UPDATE 전 백업 필수


• 인덱스 많으면 UPDATE 느려짐