오라클 BULK COLLECT와 FORALL의 사용법

 

오라클 데이터베이스는 대량 데이터를 처리하는 데 있어 성능 최적화를 위한 다양한 기능을 제공합니다. 그중에서도 BULK COLLECT와 FORALL은 PL/SQL에서 반복문을 효과적으로 처리하여 성능을 극대화하는 데 핵심적인 역할을 합니다. 이 글에서는 두 기능의 개념, 사용 방법, 그리고 실무에서 유용하게 활용할 수 있는 예제를 통해 이해를 돕겠습니다.

---

1. BULK COLLECT란?

개념

BULK COLLECT는 SQL 쿼리의 결과를 PL/SQL 컬렉션에 한 번에 대량으로 가져오는 기능입니다. 일반적으로 SELECT INTO 문은 한 번에 하나의 행만 처리하지만, BULK COLLECT를 사용하면 다수의 행을 한꺼번에 컬렉션에 로드하여 성능을 향상시킬 수 있습니다.

주요 장점

• 데이터 처리 시간 단축
• 컨텍스트 스위칭(Context Switching) 최소화
  • SQL과 PL/SQL 간의 빈번한 데이터 교환을 줄임
• 코드 가독성 향상

---

2. FORALL이란?

개념

FORALL은 PL/SQL 반복문에서 여러 DML(INSERT, UPDATE, DELETE, MERGE) 작업을 컬렉션을 기반으로 효율적으로 수행하도록 지원하는 기능입니다. 일반적인 FOR 루프와는 달리, FORALL은 반복을 단일 컨텍스트로 처리하여 DML 성능을 높입니다.

주요 장점

• 반복 처리 속도 향상
• 네트워크 왕복(Round Trip) 감소
• 대량 데이터 업데이트 작업에서 효율성 증가

---

3. BULK COLLECT와 FORALL의 기본 사용법

BULK COLLECT 사용법

DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    emp_data emp_tab;
BEGIN
    SELECT *
    BULK COLLECT INTO emp_data
    FROM employees
    WHERE department_id = 10;

    FOR i IN emp_data.FIRST..emp_data.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_data(i).first_name);
    END LOOP;
END;
/

핵심 사항:

• 컬렉션 타입을 먼저 정의합니다.
• BULK COLLECT 키워드를 사용해 데이터를 한 번에 가져옵니다.

---

FORALL 사용법

DECLARE
    TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
    emp_ids emp_id_tab := emp_id_tab(100, 101, 102);
BEGIN
    FORALL i IN emp_ids.FIRST..emp_ids.LAST
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = emp_ids(i);

    DBMS_OUTPUT.PUT_LINE('Salaries updated for selected employees.');
END;
/

핵심 사항:

• 컬렉션을 기반으로 반복 처리됩니다.
• FORALL은 단일 DML 문에 대해서만 작동합니다.

---

4. BULK COLLECT와 FORALL의 조합 사용

대량 데이터를 읽고 처리한 후, 결과를 다시 업데이트해야 하는 경우 두 기능을 조합하면 더욱 강력한 성능을 발휘합니다.

예제: 대량 데이터 처리 및 업데이트

DECLARE
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;

    emp_data emp_tab;
    emp_ids emp_id_tab;
BEGIN
    -- 1. BULK COLLECT로 데이터 로드
    SELECT *
    BULK COLLECT INTO emp_data
    FROM employees
    WHERE department_id = 20;

    -- 2. 필요한 데이터 처리 후, ID를 추출
    FOR i IN emp_data.FIRST..emp_data.LAST LOOP
        IF emp_data(i).salary < 3000 THEN
            emp_ids.EXTEND;
            emp_ids(emp_ids.COUNT) := emp_data(i).employee_id;
        END IF;
    END LOOP;

    -- 3. FORALL을 사용해 대량 업데이트
    FORALL i IN emp_ids.FIRST..emp_ids.LAST
        UPDATE employees
        SET salary = salary * 1.2
        WHERE employee_id = emp_ids(i);

    DBMS_OUTPUT.PUT_LINE('Salaries updated for employees with low salary.');
END;
/

---

5. 실무에서의 활용 예시

예제 1: 대량 데이터 조회 및 로그 처리

DECLARE
    TYPE log_tab IS TABLE OF logs%ROWTYPE;
    logs_data log_tab;
BEGIN
    SELECT *
    BULK COLLECT INTO logs_data
    FROM logs
    WHERE log_date > SYSDATE - 7;

    FOR i IN logs_data.FIRST..logs_data.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Log ID: ' || logs_data(i).log_id);
    END LOOP;
END;
/

예제 2: 대량 삭제 작업

DECLARE
    TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
    emp_ids emp_id_tab;
BEGIN
    SELECT employee_id
    BULK COLLECT INTO emp_ids
    FROM employees
    WHERE department_id = 30;

    FORALL i IN emp_ids.FIRST..emp_ids.LAST
        DELETE FROM employees
        WHERE employee_id = emp_ids(i);

    DBMS_OUTPUT.PUT_LINE('Employees deleted for department 30.');
END;
/

예제 3: 데이터 마이그레이션

DECLARE
    TYPE sales_tab IS TABLE OF sales_data%ROWTYPE;
    sales_data sales_tab;
BEGIN
    -- Source 데이터 조회
    SELECT *
    BULK COLLECT INTO sales_data
    FROM sales_data_source;

    -- Target 데이터 업데이트
    FORALL i IN sales_data.FIRST..sales_data.LAST
        INSERT INTO sales_data_target VALUES sales_data(i);

    DBMS_OUTPUT.PUT_LINE('Data migration completed.');
END;
/

---

6. 성능 개선 시 고려할 점

1. LIMIT 사용하기
   BULK COLLECT에서 너무 많은 데이터를 한꺼번에 가져오면 메모리 부족 문제가 발생할 수 있습니다. 이를 방지하기 위해 LIMIT를 사용하는 것이 좋습니다.
2. FETCH c1 BULK COLLECT INTO emp_data LIMIT 100;
3. 예외 처리
   FORALL 문에서 SAVE EXCEPTIONS 절을 사용하면 오류를 무시하고 나머지 작업을 계속 진행할 수 있습니다.
4. FORALL i IN emp_ids.FIRST..emp_ids.LAST SAVE EXCEPTIONS DELETE FROM employees WHERE employee_id = emp_ids(i);

---

7. 결론

BULK COLLECT와 FORALL은 PL/SQL에서 대량 데이터 작업의 성능을 크게 개선할 수 있는 강력한 도구입니다. 적절한 설계와 사용법을 익히면 실무에서 효율적인 데이터 처리를 구현할 수 있습니다. 특히 두 기능을 함께 활용하면 반복적인 데이터 조회와 업데이트를 최적화하여 응답 시간을 단축하고 시스템 리소스를 절약할 수 있습니다.

위의 예제들을 참고하여 실무에서 적극 활용해 보세요!