성능 향상을 위한 다중 로우 처리

정적 SQL이든 동적 SQL이든 한 개 이상의 결과를 반환하는 SELECT문의 결과 집합을 받아 오기 위해 필요한 것은 세 가지인데, 바로 커서와 결과를 받아 오는 그릇 역할을 하는 커서나 레코드 변수, 그리고 LOOP나 FOR 같은 반복문이다. 즉 SQL문에 대한 커서를 연 뒤, 루프를 돌며 결과를 패치해 변수에 담고 루프가 끝나면 커서를 닫는다. 그런데 만약 쿼리 결과로 반환되는 로우 수가 많다면 루프를 돌면서 결과를 패치하는 식의 처리는 시간도 많이 걸리고 성능 면에서 좋지 않다. 일일이 한 로우씩 읽어 가며 결과를 받아올 것이 아니라 SQL의 장점인 집합적으로 처리하는 방법이 있다면 더 좋지 않을까? 바로 BULK COLLECT INTO 절을 사용해서 이를 구현할 수 있다. 먼저 정적 SQL을 이용한 예제를 살펴 보자.

입력

    -- BULK COLLECT INTO를 사용한 정적 SQL
    DECLARE
      -- 레코드 선언
      TYPE rec_physicist IS RECORD  (
        ids ch13_physicist.ids%TYPE,
        names ch13_physicist.names%TYPE,
        birth_dt ch13_physicist.birth_dt%TYPE );

      -- 레코드를 항목으로 하는 중첩 테이블 선언
      TYPE NT_physicist IS TABLE OF rec_physicist;

      -- 중첩 테이블 변수 선언
      vr_physicist NT_physicist;
    BEGIN
      BULK COLLECT INTO절(패치가 한 번에 이루어 진다)
      SELECT *
        BULK COLLECT INTO vr_physicist
        FROM ch13_physicist;
      -- 루프를 돌며 출력(이 루프는 값을 패치하는 것이 아니라 출력하기 위한 루프임)
      FOR i IN 1..vr_physicist.count
      LOOP
        DBMS_OUTPUT.PUT_LINE(vr_physicist(i).names);
      END LOOP;
    END;

결과

    Galileo Galilei
    Isaac Newton
    Max Plank
    Albert Einstein

쿼리를 수행한 결과 로우 수가 1개일 때는 SELECT INTO를, 2개 이상일 때는 커서를 사용해 결과를 변수에 담았는데, 위 예제에서는 SELECT … BULK COLLECT INTO 구문을 사용해 컬렉션 변수에 결과 집합을 담았다. 기존에 커서를 사용해 루프를 돌며 로우 하나씩 데이터를 받은 것에 비하면 코드도 훨씬 줄어 들었을 뿐만 아니라 성능 면에서도 우수하다. BULK COLLECT INTO 절은 동적 SQL에서도 사용할 수 있다. 정적 SQL에서는 SELECT 구문에 직접 들어갔지만, 동적 SQL에서는 EXECUTE IMMEDIATE 문에 포함된다.

입력

    DECLARE
      -- 레코드 선언
      TYPE rec_physicist IS RECORD  (
        ids ch13_physicist.ids%TYPE,
        names ch13_physicist.names%TYPE,
        birth_dt ch13_physicist.birth_dt%TYPE );
      -- 레코드를 항목으로 하는 중첩 테이블 선언
      TYPE NT_physicist IS TABLE OF rec_physicist;

      -- 중첩 테이블 변수 선언
      vr_physicist NT_physicist;

      vs_sql VARCHAR2(1000);
      vn_ids ch13_physicist.ids%TYPE := 1;
    BEGIN
      -- SELECT 구문
      vs_sql := 'SELECT * FROM ch13_physicist WHERE ids > :a' ;

      -- EXECUTE IMMEDIATE .. BULK COLLECT INTO 구문
      EXECUTE IMMEDIATE vs_sql BULK COLLECT INTO vr_physicist USING vn_ids;

      -- 루프를 돌며 출력
      FOR i IN 1..vr_physicist.count
      LOOP
        DBMS_OUTPUT.PUT_LINE(vr_physicist(i).names);
      END LOOP;

    END;

결과

    Isaac Newton
    Max Plank
    Albert Einstein

동적 SQL에서는 BULK COLLECT INTO 절이 EXECUTE IMMEDIATE 문의 쿼리 문자열과 USING 사이에 위치한다. 동적 SQL 역시 BULK COLLECT INTO 절을 사용하는 것이 커서 보다는 가독성과 성능 면에서 우수하므로, 결과 집합 건수가 많을 때는 이 방법을 사용하자.