Comparison between Oracle Data insertion and batch data insertion

Create test table

Create table base_users

(

Userid varchar2 (16 ),

Username varchar2 (32 ),

Passwd varchar2 (16)

) Tablespace cat_data;

Insert one by one

Create or replace procedure insert_data_one_by_one (n in number)

As

CurrentId number (16): = 0;

Rochelle userid varchar2 (16 );

Rochelle username varchar2 (32 );

Sqltext varchar2 (256 );

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

Sqltext: = 'insert into base_users (userid, username, passwd) values (: userid,: username, '| '2017 )';

Loop
CurrentId: = currentId + 1;
Rochelle userid: = to_char (currentId );
Rochelle 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;
/

Batch 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 (16) index by binary_integer;

Type usernameArray is table of varchar2 (32) index by binary_integer;

Puserid useridArray;

Pusername usernameArray;

Begin

Dbms_output.put_line ('in in... '| 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), '123 ');

Commit;

Dbms_output.put_line ('end... '| to_char (current_timestamp, 'hh24: MI: SSxFF '));

Endinsert_data_bulk;

/

Test the insertion of 10 million pieces of 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

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12