Oracle line-by-row submission, batch submission and limit speed-raising method ____oracle

In an Oracle database, the more frequent the submission is, the better. On the contrary, bulk submissions can get better performance. This article gives you a simple demonstration of the performance difference between submitting a row to a bulk commit in an Oracle database. Finally, a method that can greatly change the performance is given.

1. Create a table t_ref and initialize 880,992 data for later experiments.
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. Write a simple pl/sql block to simulate progressive submission, and observe the execution time.
Our goal is to insert all the data from the T_ref table into 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

A total of 3 minutes and 12 seconds per row commit.

3. To simulate the situation of bulk submissions.
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 >= THEN
8 COMMIT;
9 End IF;
Ten end LOOP;
COMMIT;
End;
13/

Pl/sql procedure successfully completed.

elapsed:00:01:27.69

This time is 1 minutes 27 seconds, approximately half the time to commit the method line by row. This shows that Oracle should try to complete the work in bulk submissions.

4. Finally, we use Oracle's more advanced approach to complete the above task and experience the ultimate speed.
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
into RECs LIMIT 100;
One forall i in 1. RECs. COUNT
INSERT into T VALUES RECs (i);
COMMIT;
The EXIT when Cur%notfound;
End LOOP;
Close cur;
The end;
18/

elapsed:00:00:09.75

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

5. Summary
In an Oracle database, frequent commits can cause a lot of physical I/O to redo log, which can greatly limit the performance of the database. Therefore, in order to improve database performance, as much as possible batch submissions.
Oracle has a lot of good ways to be worth trying.

Good luck.

--The end--