Oracle 11g is the most efficient way to insert multiple rows

Question

I have an application that works slowly on a WAN - we believe that the reason is a lot of inserts into the table. I am currently exploring more efficient ways to insert multiple rows at once.

I found this method:

INSERT ALL INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (100,20) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (21,2) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (321,10) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (22,13) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (14,121) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (11,112) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (112,23) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (132,2323) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (121,34) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (24333,333) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (1232,3434) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (4554,3434) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,211) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,1233) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (12,22) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (356,233) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (9347,23) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (8904,245) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (342,4545) INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (453,233) SELECT 1 FROM DUAL; 

What I would like to know is the method above, actually more efficient than just doing 20 "INSERT INTO MY_TABLE (1,1);"? Are there other ways to do this?

Answer 1

You can try direct path insertion to speed things up, but for 100 records, the usual path insert should be fast enough, and it seems that the problem is about locking the table when pasting to the log from a large number of sources.

To tell Oracle to use direct path insertion, you must specify APPEND or APPEND_VALUES depending on the syntax of the insert statement. For instance.

 insert /*+ APPEND */ into multi_insert(val_1, val_2) select * from ( select 100, 20 from dual union all select 21, 2 from dual union all select 321, 10 from dual union all select 22, 13 from dual union all select 14, 121 from dual union all select 11, 112 from dual union all select 112, 23 from dual union all select 132, 2323 from dual union all select 121, 34 from dual union all select 24333, 333 from dual union all select 1232, 3434 from dual union all select 4554, 3434 from dual union all select 3434, 211 from dual union all select 3434, 1233 from dual union all select 12, 22 from dual union all select 356, 233 from dual union all select 9347, 23 from dual union all select 8904, 245 from dual union all select 342, 4545 from dual union all select 453, 233 from dual ) 

If the insert statement came from PL / SQL code, you can use bulk insert with the forall statement to improve performance ( SQLFiddle ):

 declare type TRowList is table of multi_insert%rowtype index by binary_integer; vRowList TRowList; vRow multi_insert%rowtype; begin vRow.val_1 := 100; vRow.val_2 := 20; vRowList(0) := vRow; vRow.val_1 := 21; vRow.val_2 := 2; vRowList(1) := vRow; vRow.val_1 := 321; vRow.val_2 := 10; vRowList(2) := vRow; -- ... forall vIdx in vRowList.first .. vRowList.last insert /*+ APPEND_VALUES */ -- direct path insert into multi_insert values vRowList(vIdx); end; 

Answer 2

"the client said that it works fine when the application and Oracle were on the same local network, but when they moved their Oracle servers abroad they say that the program runs very slowly"

Okay, now we're getting somewhere. If you have a setting in which your hundreds of agents are separate calls, they are likely to be sent in separate packets. This would be painful on a global network compared to a local area network. In this case, it would be interesting to see if converting statements from RBAR to something based on a set will reduce the number of transmitted packets.

However, I would advise you to get some serious facts before you begin to modify these changes. Doesn't your client have a network administrator with whom you could talk? Or at least you could get them to install Wireshark and send you some reports?

Answer 3

Some DBMSs, such as mysql, and now SQL Server supports multiple lines of data syntax insertion:

 Insert into myTable ( c1, c2 ) values ( 1,1 ), ( 1,2 ), ... ; 

(For more details see Insert multiple rows of Sql server data or insert multirow on mysql )

But not an oracle. Sorry for the bad news. A closer path is documented in Technology on the Web .