BULK SQL 사용예

대량의 데이터를 load 하거나 update(delete) 할 때 일반적으로 사용하는 LOOP 문보다 성능이 수십배 이상 좋은 방법이 BULK SQL 을 사용하는 방법이다. 

일반적인 LOOP 문 안에 있는 SQL 은 매 LOOP 마다 1회씩 수행된다. LOOP 가 100만개이면 100만번의 SQL 수행이 발생되는 것이다. 반면에 BULK SQL 을 사용하면 LOOP 없이 1번의 SQL 수행으로 처리할 수 있다.

BULK SQL 처리를 위해 오라클 PL/SQL 에서는 FORALL 과 BULK COLLECT INTO 라는 이라는 예약어를 사용한다. FORALL 은 PL/SQL 이 DML 문장을 SQL 엔진에게 보내는데 사용되고, BULK COLLECT INTO 는 SQL 수행결과 데이터셋을 PL/SQL 엔진에게 보낸다.

BULK SQL 은 MULTI ROW 집합을 한번에 처리하기 때문에 ARRAY 처리가 쉬운 TABLE DATATYPE 같은 collection type 이 주로 사용된다.

BULK SQL 로 수백만건을 한번에 처리할 수도 있지만, 이렇게 하면 PGA 소모가 크기 때문에 1000 ~ 100000 건 단위로 나누어서 작업하는 것이 좋다. 이를 위해 LIMIT 이라는 예약어를 사용한다. 

<참고> BULK SQL 에 대해서 자세히 공부하려면 아래의 링크를 보세요.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#BABFHGHI

---------------------

BULK SQL 사용예

---------------------

DROP TABLE SH.SALES2;

CREATE TABLE SH.SALES2 AS 

SELECT * FROM SH.SALES

WHERE 1=2;

CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST IS

  CURSOR sales_cur IS

                         SELECT  PROD_ID,

                                 CUST_ID,

                                 TIME_ID,

                                 CHANNEL_ID,

                                 PROMO_ID,

                                 QUANTITY_SOLD,

                                 AMOUNT_SOLD

                         FROM    SH.SALES;

  TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;

  SALES_TBL SALES_TBL_TYPE;

BEGIN

  OPEN sales_cur;

  LOOP 

    FETCH sales_cur BULK COLLECT INTO  sales_tbl LIMIT 1000; -- PGA 소모를 작게 유지하기 위해 1000건씩만 처리

    --각종 계산은 여기에서

    FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP

      sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;

    END LOOP;

    --<방법1> FETCH 된 1000건을 SQL 1회 수행으로 처리

    FORALL i IN sales_tbl.FIRST..sales_tbl.LAST

      INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,

                                 sales_tbl(i).CUST_ID,

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

                                 sales_tbl(i).TIME_ID,

                                 sales_tbl(i).CHANNEL_ID,

                                 sales_tbl(i).PROMO_ID,

                                 sales_tbl(i).QUANTITY_SOLD,

                                 sales_tbl(i).AMOUNT_SOLD

                                 );

-- <방법2> LOOP를 사용하는 방법; INSERT SQL 1000 번 수행 (속도는 방법1 보다 30배 이상 느림)

--    FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP

--      INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,

--                                 sales_tbl(i).CUST_ID,

--                                 sales_tbl(i).TIME_ID,

--                                 sales_tbl(i).CHANNEL_ID,

--                                 sales_tbl(i).PROMO_ID,

--                                 sales_tbl(i).QUANTITY_SOLD,

--                                 sales_tbl(i).AMOUNT_SOLD

--                                 );

--

--    END LOOP;

    -- UPDATE / DELETE 도 가능

    FORALL i IN sales_tbl.FIRST..sales_tbl.LAST

      UPDATE SALES SET AMOUNT_SOLD = sales_tbl(i).AMOUNT_SOLD

      WHERE PROD_ID = sales_tbl(i).PROD_ID

        AND CUST_ID = sales_tbl(i).CUST_ID

        AND TIME_ID = sales_tbl(i).TIME_ID

        AND CHANNEL_ID = sales_tbl(i).CHANNEL_ID

        AND PROMO_ID = sales_tbl(i).PROMO_ID;

    EXIT WHEN sales_cur%NOTFOUND;

  END LOOP;

  CLOSE sales_cur;

  COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK;

        RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));

END;

[출처] https://m.blog.naver.com/inhim/100154127152