용어 뜻:
출처:
Bulk SQL은 대량의 데이터를 select하거나 update/delete 할 때 사용한다.
Bulk SQL을 사용하면 루프 없이 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 라는 예약어를 사용한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
---------------------
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,
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;
| cs |
LIVE LOVE LIFE, 2016-10-25, http://m.blog.naver.com/inhim/100154127152
한국데이터진흥원, 2016-10-25, http://www.kodb.or.kr/info/info_06_view.php?dbnum=176504
Albumbang, 2016-10-27, http://www.albumbang.com/board/board_view.jsp?board_name=free&no=149
댓글
댓글 쓰기