在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 onsec@ora10g> DECLARE2 BEGIN3 FOR cur IN (SELECT * FROM t_ref) LOOP4 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> DECLARE2 v_count NUMBER;3 BEGIN4 FOR cur IN (SELECT * FROM t_ref) LOOP5 INSERT INTO t VALUES cur;6 v_count := v_count + 1;7 IF v_count >= 100 THEN8 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> DECLARE2 CURSOR cur IS3 SELECT * FROM t_ref;4 TYPE rec IS TABLE OF t_ref%ROWTYPE;5 recs rec;6 BEGIN7 OPEN cur;8 WHILE (TRUE) LOOP9 FETCH cur BULK COLLECT10 INTO recs LIMIT 100;11 FORALL i IN 1 .. recs.COUNT12 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 --
oracle批量insert循环,【INSERT】逐行提交、批量提交及极限提速方法
最新推荐文章于 2024-08-15 17:22:33 发布