1.
create table t_ref as select * from all_objects;

insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;

2.模擬逐行提交的情況，注意觀察執行時間
目標是將t_ref表中的資料全部插入到t中
set timing on

DECLARE
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
COMMIT;
END LOOP;
END;
/

3分

3.模擬批次提交
truncate table t;

DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
v_count：=0；
END IF;
END LOOP;
COMMIT;
END;
/

1分多，大約是逐行提交方法一半的時間

4.更高階的方法，體驗一下極限速度。
DECLARE
CURSOR cur IS
SELECT * FROM t_ref;
TYPE rec IS TABLE OF t_ref%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
INSERT INTO t VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/

DECLARE
v_count NUMBER;
BEGIN
FOR cur IN ( select * from gsinfo.t_order_backup where query_info='MonitorAdd' and substr(order_no ,21,22) in (1,5) ) LOOP
update gsinfo.t_order_backup set state='1' where query_info=cur.query_info and order_no=cur.order_no;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/

頻繁的COMMIT會引起大量Redo Log的物理I/O，會極大的限制資料庫的效能
因此，為提高資料庫效能，儘可能的批次提交。
Oracle有很多優秀的方法值得嘗試

oracle 儲存過程批次提交

相關文章