逐行提交、批量提交及极限提速方法

【INSERT】逐行提交、批量提交及极限提速方法
个人分类：性能优化


在Oracle数据库中，不是提交越频繁越好。恰恰相反，批量提交可以得到更好的性能。这篇文章给大家简单展示一下在Oracle数据库中逐行提交于批量提交两者之间的性能差别。最后再给出一种可以极大改变性能的方法。

1.创建表t_ref，并初始化880992条数据，用于后面的实验。
sec@ora10g> create table t_ref as select * from all_objects;

sec@ora10g> insert into t_ref select * from t;

220248 rows created.

sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /

440496 rows created.

2.编写一个简单的PL/SQL块来模拟逐行提交的情况，注意观察执行时间。
我们的目标是将t_ref表中的数据全部插入到t中。
sec@ora10g> set timing on

sec@ora10g> DECLARE
2 BEGIN
3 FOR cur IN (SELECT * FROM t_ref) LOOP
4 INSERT INTO t VALUES cur;
5 COMMIT;
6 END LOOP;
7 END;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:12.77

逐行提交的情况下，一共用时3分12秒。

3.再来模拟批量提交的情况。
sec@ora10g> truncate table t;

Table truncated.

sec@ora10g> DECLARE
2 v_count NUMBER;
3 BEGIN
4 FOR cur IN (SELECT * FROM t_ref) LOOP
5 INSERT INTO t VALUES cur;
6 v_count := v_count + 1;
7 IF v_count >= 100 THEN
8 COMMIT;
9 END IF;
10 END LOOP;
11 COMMIT;
12 END;
13 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:27.69

此时共用时1分27秒，大约是逐行提交方法一半的时间。由此可见，对于Oracle应该尽量以批量提交的方式来完成工作。

4.最后我们使用Oracle更高级的方法完成上面的任务，体验一下极限速度。
sec@ora10g> DECLARE
2 CURSOR cur IS
3 SELECT * FROM t_ref;
4 TYPE rec IS TABLE OF t_ref%ROWTYPE;
5 recs rec;
6 BEGIN
7 OPEN cur;
8 WHILE (TRUE) LOOP
9 FETCH cur BULK COLLECT
10 INTO recs LIMIT 100;
11 FORALL i IN 1 .. recs.COUNT
12 INSERT INTO t VALUES recs (i);
13 COMMIT;
14 EXIT WHEN cur%NOTFOUND;
15 END LOOP;
16 CLOSE cur;
17 END;
18 /

Elapsed: 00:00:09.75

此时我们仅仅使用了不到10秒的时间就完成了曾经需要几分钟才能完成的任务。

5.小结
在Oracle数据库中，频繁的COMMIT会引起大量Redo Log的物理I/O，会极大的限制数据库的性能。因此，为提高数据库性能，尽可能的批量提交。
Oracle有很多优秀的方法值得尝试。

Good luck.

-- The End --