Oracle-by-article vs. Bulk Insert Data Mode comparison

Create a test table

CREATE TABLE Base_users

(

UserID VARCHAR2 (16),

Username VARCHAR2 (32),

passwd VARCHAR2 (16)

) Tablespace Cat_data;

In an insert-by-line way

Create or replace procedure Insert_data_one_by_one (n in number)

As

CurrentID Number (16): = 0;

L_userid VARCHAR2 (16);

L_username VARCHAR2 (32);

SQLText VARCHAR2 (256);

Begin
Dbms_output.put_line (' Begin ... ' | | to_char (current_timestamp, ' HH24:MI:SSxFF '));

SQLText: = ' INSERT INTO Base_users (userid, username, passwd) VALUES (: UserID,: Username, ' | | ' 111111) ';

    loop
         Currentid:=currentid + 1;
        l_userid:= To_char (currentId);
        l_username:= to_char (18600000000 + CurrentID);

Execute immediate sqltext using L_userid, l_username;
Exit when CurrentID >= N;
End Loop;
Commit
Dbms_output.put_line (' End commit ... ' | | to_char (current_timestamp, ' HH24:MI:SSxFF '));
End Insert_data_one_by_one;
/

The way to use BULK INSERT

Create or replace procedure Insert_data_bulk (n in number)

As

I int;

Tmp_userid number;

Tmp_username number;

Type Useridarray is Table of VARCHAR2 (+) index by Binary_integer;

Type Usernamearray is Table of VARCHAR2 (+) index by Binary_integer;

Puserid Useridarray;

Pusername Usernamearray;

Begin

Dbms_output.put_line (' Begin ... ' | | to_char (current_timestamp, ' HH24:MI:SSxFF '));

Tmp_userid: = 1;

Tmp_username: = 18600000000;

For I in 1.. N Loop

Puserid (i): = Tmp_userid;

Pusername (i): = Tmp_username;

Tmp_userid: = Tmp_userid + 1;

Tmp_username: = tmp_username + 1;

End Loop;

ForAll i in 1.. N

INSERT INTO Base_users (userid, username, passwd)

VALUES (Puserid (i), pusername (i), ' 111111 ');

Commit

Dbms_output.put_line (' End ... ' | | to_char (current_timestamp, ' HH24:MI:SSxFF '));

Endinsert_data_bulk;

/

Test the insertion of 10 million data

Sql>set Serveroutput on

Sql>begin

Insert_data_one_by_one (10000000);

End

/

Begin ... 22:14:01.572928000
End commit ... 22:20:43.911104000

Sql>truncate table base_users;

Sql>begin

Insert_data_bulk (10000000);

End

/

Begin ... 22:25:31.497810000
End ... 22:27:23.801515000

Oracle-by-article vs. Bulk Insert Data Mode comparison