Oracle在操作大数据量时，如何分批插入、分批更新、分批删除、分批提交？
Tags： OracleSQL优化分批删除分批提交分批插入分批更新效率
👉 本文共约295个字，系统预计阅读时间或需2分钟。
本页目录 隐藏
在Oracle中，如果要操作比较大的数据量时，最好采用分批处理的方式，这样可以避免产生很大的事务。可以分为3个类别：分批插入、分批更新、分批删除及分批提交，主要采用游标加BULK COLLECT的方式来实现，用LIMIT来限制每次需要操作的行数。其中，分批更新也可以采用游标FOR循环加ORDER BY排序的方式，效率也是非常不错的。
下面麦老师给出3个模版程序，若工作中碰到类似的需求，则可以照猫画虎。
1、分批UPDATE
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 | DROP TABLE T2; CREATE TABLE T2 AS SELECT OBJECT_NAME FROM DBA_OBJECTS; SELECT * FROM T2; SELECT COUNT(*) FROM T2; DECLARE TYPE RIDARRAY IS TABLE OF ROWID; TYPE VCARRAY IS TABLE OF T2.OBJECT_NAME%TYPE; L_RIDS RIDARRAY; L_NAMES VCARRAY; CURSOR C IS SELECT ROWID, OBJECT_NAME FROM T2; BEGIN OPEN C; LOOP FETCH C BULK COLLECT INTO L_RIDS, L_NAMES LIMIT 10; FORALL I IN 1 .. L_RIDS.COUNT UPDATE T2 SET OBJECT_NAME = LOWER(L_NAMES(I)) WHERE ROWID = L_RIDS(I); COMMIT; EXIT WHEN C%NOTFOUND; END LOOP; CLOSE C; END; / |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
2、分批DELETE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DROP TABLE T3; CREATE TABLE T3 AS SELECT * FROM DBA_OBJECTS; DECLARE CURSOR MYCURSOR IS SELECT ROWID FROM T3 ORDER BY ROWID; -- 按ROWID排序的CURSOR，删除条件是XXX=XXXX，根据实际情况修改 TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER; V_ROWID ROWID_TABLE_TYPE; BEGIN OPEN MYCURSOR; LOOP FETCH MYCURSOR BULK COLLECT INTO V_ROWID LIMIT 5000; -- 每次处理5000行，也就是每5000行一提交 EXIT WHEN V_ROWID.COUNT = 0; FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST DELETE FROM T3 WHERE ROWID = V_ROWID(I); COMMIT; END LOOP; CLOSE MYCURSOR; END; / |
3、分批INSERT
将T_20160401的数据全部插入T_20160401_01表。
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 | DECLARE CURSOR MYCURSOR IS SELECT ROWID FROM T_20160401 ORDER BY ROWID; TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER ; V_ROWID ROWID_TABLE_TYPE; V_COUNT NUMBER := 0; V_START DATE; V_END DATE; BEGIN SELECT SYSDATE INTO V_START FROM DUAL; OPEN MYCURSOR; LOOP FETCH MYCURSOR BULK COLLECT INTO V_ROWID LIMIT 50000; EXIT WHEN V_ROWID.COUNT = 0; FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST INSERT INTO T_20160401_01 SELECT * FROM T_20160401 T WHERE T.ROWID=V_ROWID(I); V_COUNT:=V_COUNT+TO_CHAR(SQL%ROWCOUNT); COMMIT; END LOOP; CLOSE MYCURSOR; SELECT SYSDATE INTO V_END FROM DUAL; DBMS_OUTPUT.PUT_LINE('START: ' || V_START); DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_COUNT); DBMS_OUTPUT.PUT_LINE('END: ' || V_END); COMMIT; END; / -- 或者使用如下的方式 DECLARE CURSOR MYCURSOR IS SELECT * FROM T_20160401 ORDER BY ROWID; TYPE ROWID_TABLE_TYPE IS TABLE OF T_20160401%ROWTYPE; V_ROWID ROWID_TABLE_TYPE; BEGIN OPEN MYCURSOR; LOOP FETCH MYCURSOR BULK COLLECT INTO V_ROWID LIMIT 50000; EXIT WHEN V_ROWID.COUNT = 0; FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST INSERT INTO T_20160401_01 VALUES V_ROWID(I); COMMIT; END LOOP; CLOSE MYCURSOR; END; / |
