"INSERT" progressive submission, batch submission, and limit acceleration methods

In an Oracle database, it is better to submit more frequently. On the contrary, batch submissions can achieve better performance. This article gives you a simple demonstration of the performance difference between a row-by-line commit in an Oracle database and a batch commit. Finally, we give a method that can greatly change the performance.

1. Create the table t_ref and initialize 880,992 data for subsequent experiments.
[Email protected]> CREATE TABLE T_ref as SELECT * from All_objects;

[Email protected]> INSERT INTO T_ref select * from T;

220248 rows created.

[Email protected]>/
[Email protected]>/
[Email protected]>/
[Email protected]>/
[Email protected]>/

440496 rows created.

2. Write a simple PL/SQL block to simulate the case of progressive commits, and observe the execution time.
Our goal is to insert all of the data from the T_ref table into T.
[Email protected]> set timing on

[Email protected]> DECLARE
2 BEGIN
3 for cur inch (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

In the case of progressive submission, it takes 3 minutes and 12 seconds altogether.

3. Again to simulate the case of batch submissions.
[Email protected]> truncate TABLE t;

Table truncated.

[Email protected]> DECLARE
2 V_count number;
3 BEGIN
4 for cur inch (SELECT * from T_ref) LOOP
5 INSERT into T VALUES cur;
6 V_count: = V_count + 1;
7 IF V_count >=
8 COMMIT;
9 END IF;
Ten END LOOP;
One COMMIT;
The END;
13/

PL/SQL procedure successfully completed.

elapsed:00:01:27.69

At this point, it takes 1 minutes and 27 seconds, which is about half the time of the progressive commit method. This shows that Oracle should try to do the work in a batch submission way.

4. Finally, we use Oracle's more advanced approach to complete the above tasks and experience the ultimate speed.
[Email protected]> 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
Ten into RECs LIMIT 100;
FORALL i in 1.. RECs. COUNT
INSERT into T VALUES RECs (i);
COMMIT;
Cur%notfound EXIT when;
The END LOOP;
CLOSE cur;
The END;
18/

elapsed:00:00:09.75

At this point, we just used less than 10 seconds to complete a task that took a few minutes to complete.

5. Summary
In an Oracle database, frequent commits can cause large amounts of redo log physical I/O, which can greatly limit the performance of the database. Therefore, in order to improve the database performance, as much as possible batch submission.
Oracle has a number of good ways to try it out.

Good luck.

--The End--

"INSERT" progressive submission, batch submission, and limit acceleration methods