从 Java 批量插入 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2716818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
bulk insert from Java into Oracle
提问by Will Glass
I need to insert many small rows rapidly into Oracle. (5 fields).
我需要在 Oracle 中快速插入许多小行。（5 个字段）。
With MySQL, I break the inserts into groups of 100, then use one insert statement for every group of 100 inserts.
使用 MySQL，我将插入分成 100 组，然后对每组 100 次插入使用一个插入语句。
But with Oracle, user feedback is that the mass inserts (anywhere from 1000-30000) are too slow.
但是对于 Oracle，用户反馈是批量插入（1000-30000 之间的任何地方）太慢了。
Is there a similar trick I can use to speed up the programmatic inserts from Java into Oracle?
有没有类似的技巧可以用来加速从 Java 到 Oracle 的编程插入？
回答by crowne
Nowadays MySQL is Oracle so perhaps a simpler solution might be to stay on MySQL...
现在 MySQL 是 Oracle，所以也许一个更简单的解决方案可能是留在 MySQL 上......
If not then you should make sure that the transaction is started before you start your group of inserts, once the group is finished then commit the transaction and start a new transaction for the next group of inserts.
如果没有，那么您应该确保在开始插入组之前启动事务，一旦该组完成，则提交事务并为下一组插入启动新事务。
Also check for unnecessary index definitions that may be slowing the insert time.
还要检查可能会减慢插入时间的不必要的索引定义。
Updated...
Bulk inserting refers to the last step of ETL(Extract Transform Load), so have you considered using a java based ETL tool such as pentaho kettleor talend-studio.
更新...
批量插入是指ETL（提取转换加载）的最后一步，所以您是否考虑过使用基于 Java 的 ETL 工具，例如pentaho Kenya或talend-studio。
Pentaho describes their Oracle Bulk loading facilities here.
Pentaho在这里描述了他们的 Oracle 批量加载设施。
A quick google also shows some preliminary evidence that Talend also has some support for Oracle bulk loading.
一个快速的谷歌也显示了一些初步证据，表明 Talend 也有一些对 Oracle 批量加载的支持。
回答by APC
You don't say how you are passing these records to the database. The best way is to use an array, as this allows the use of Oracle's plenty of nifty FORALL bulk operations.
您没有说明如何将这些记录传递到数据库。最好的方法是使用数组，因为这允许使用 Oracle 的大量漂亮的 FORALL 批量操作。
This example package has two procedures. One which populates a collection of T23 records (a table which comprises five numeric columns) and one which bulk inserts records into that table using an array.
这个示例包有两个过程。一个填充一组 T23 记录（一个包含五个数字列的表），一个使用数组将记录批量插入到该表中。
SQL> create or replace package p23 as
2 type t23_nt is table of t23%rowtype;
3 function pop_array ( p_no in number )
4 return t23_nt;
5 procedure ins_table ( p_array in t23_nt );
6 end p23;
7 /
Package created.
SQL> create or replace package body p23 as
2
3 function pop_array ( p_no in number )
4 return t23_nt
5 is
6 return_value t23_nt;
7 begin
8 select level,level,level,level,level
9 bulk collect into return_value
10 from dual
11 connect by level <= p_no;
12 return return_value;
13 end pop_array;
14
15 procedure ins_table
16 ( p_array in t23_nt )
17 is
18 s_time pls_integer;
19 begin
20
21 s_time := dbms_utility.get_time;
22
23 forall r in p_array.first()..p_array.last()
24 insert into t23
25 values p_array(r);
26
27 dbms_output.put_line('loaded '
28 ||to_char(p_array.count())||' recs in '
29 ||to_char(dbms_utility.get_time - s_time)
30 ||' csecs');
31 end ins_table;
32 end p23;
33 /
Package body created.
SQL>
Here is the output from some sample runs:
以下是一些示例运行的输出：
SQL> declare
2 l_array p23.t23_nt;
3 begin
4 l_array := p23.pop_array(500);
5 p23.ins_table(l_array);
6 l_array := p23.pop_array(1000);
7 p23.ins_table(l_array);
8 l_array := p23.pop_array(2500);
9 p23.ins_table(l_array);
10 l_array := p23.pop_array(5000);
11 p23.ins_table(l_array);
12 l_array := p23.pop_array(10000);
13 p23.ins_table(l_array);
14 l_array := p23.pop_array(100000);
15 p23.ins_table(l_array);
16 end;
17 /
loaded 500 recs in 0 csecs
loaded 1000 recs in 0 csecs
loaded 2500 recs in 0 csecs
loaded 5000 recs in 1 csecs
loaded 10000 recs in 1 csecs
loaded 100000 recs in 15 csecs
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from t23
2 /
COUNT(*)
----------
119000
SQL>
I think inserting 100,000 records in 0.15 seconds should please all but the most demanding of users. So, the question is, how do you approach your inserts?
我认为在 0.15 秒内插入 100,000 条记录应该会让除了最苛刻的用户满意。所以，问题是，你如何处理你的插入物？
回答by Espen
You can use Spring's DAO module to batch insert many rows.
您可以使用 Spring 的 DAO 模块批量插入多行。
An example that inserts a collection of Order objects into the database in one update:
在一次更新中将 Order 对象集合插入数据库的示例：
public class OrderRepositoryImpl extends SimpleJdbcDaoSupport implements
OrderRepository {
private final String saveSql = "INSERT INTO orders(userid, username, coffee, coffeename, amount) "
+ "VALUES(?, ?, ?, ?, ?)";
public void saveOrders(final Collection<Order> orders) {
List<Object[]> ordersArgumentList = new ArrayList<Object[]>(orders
.size());
Object[] orderArguments;
for (Order order : orders) {
orderArguments = new Object[] { order.getUserId(),
order.getUserName(), order.getCoffe(),
order.getCoffeeName(), order.getAmount() };
ordersArgumentList.add(orderArguments);
}
getSimpleJdbcTemplate().batchUpdate(saveSql, ordersArgumentList);
}
}
