Re: Array Inserts from Java JDBC
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Feb 2013 10:00:19 -0800 (PST)
The docs cover this pretty well:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#i1056232
here is a blog as well:
http://betteratoracle.com/posts/25-array-batch-inserts-with-jdbc
-------------
Update Batching
You can reduce the number of round-trips to the database, thereby improving
application performance, by grouping multiple UPDATE, DELETE, or
INSERTstatements into a single batch and having the whole batch sent to the
database and processed in one trip. This is referred to as update batching.
Note:The JDBC 2.0 specification refers to update batching as batch updates.
This is especially useful with prepared statements, when you are repeating the
same statement with different bind variables.
Oracle JDBC supports two distinct models for update batching:
* The standard model, implementing the JDBC 2.0 specification, which is
referred to as standard update batching
* The Oracle-specific model, independent of the JDBC 2.0 specification,
which is referred to as Oracle update batching
Note:It is important to be aware that you cannot mix these models. In any
single application, you can use one model or the other, but not both. Oracle
JDBC driver will throw exceptions when you mix these.
This section covers the following topics:
* Overview of Update Batching Models
* Oracle Update Batching
* Standard Update Batching
* Premature Batch Flush
Oracle Update Batching
The Oracle update batching feature associates a batch value with each prepared
statement object. With Oracle update batching, instead of the JDBC driver
running a prepared statement each time the executeUpdate method is called, the
driver adds the statement to a batch of accumulated processing requests. The
driver will pass all the operations to the database for processing once the
batch value is reached. For example, if the batch value is 10, then each batch
of 10 operations will be sent to the database and processed in one trip.
A method in the OracleConnection class enables you to set a default batch value
for the Oracle connection as a whole, and this batch value applies to any
Oracle prepared statement in the connection. For any particular Oracle prepared
statement, a method in the OraclePreparedStatement class enables you to set a
statement batch value that overrides the connection batch value. You can also
override both batch values by choosing to manually process the pending batch.
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci);
ods.setUser("scott");
ods.setPassword("tiger"); Connection conn = ods.getConnection();
conn.setAutoCommit(false); PreparedStatement ps =
conn.prepareStatement("insert into dept values (?, ?, ?)"); //Change batch
size for this statement to 3
((OraclePreparedStatement)ps).setExecuteBatch (3); ps.setInt(1, 23);
ps.setString(2, "Sales");
ps.setString(3, "USA");
ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 24);
ps.setString(2, "Blue Sky");
ps.setString(3, "Montana");
ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 25);
ps.setString(2, "Applications");
ps.setString(3, "India");
ps.executeUpdate(); //The queue size equals the batch value of 3 //JDBC sends
the requests to the database ps.setInt(1, 26);
ps.setString(2, "HR");
ps.setString(3, "Mongolia");
ps.executeUpdate(); //JDBC queues this for later execution
((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request
conn.commit(); ps.close();
...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
