Is it possible to use direct path insertion with JDBC/Java?

Question

We have an application written in C and Pro*C which inserts data from log files into an Oracle 11.2 database using host arrays and bulk insertion. This uses the APPEND and NOLOGGING hints to take advantage of direct path insertion and reduce the amount of redo generated. NOLOGGING makes sense for this as it is a temporary staging table and the data can be recovered from the log files if needed.

We are trying to replicate this functionality in Java but have been unable to make use of direct path insertion of large numbers of records. Is this possible with Java/JDBC?

The things that I have tried and investigated are:

• JDBC batching (both standard batching and Oracle's extensions). This approach saves on round trip times but this is negligible as the application is on the same machine as the database. It also does not use a direct path.
• The APPEND_VALUES hint. This sounds promising but makes little sense as JDBC batching does not appear to actually perform an "array" insert of many records.

From what I understand, direct path inserts only supports the subquery syntax and not the VALUES clause. This cannot be used as the data to be inserted does not exist in the database yet.

I have been unable to find any reference to Java being able to use the host array style loading which Pro*C uses.

As an aside, we are investigating external table loading or SQL*loader and appreciate that these tools are capable of direct path loading, but this question is really about getting a definitive answer on whether direct path insertion is even possible from Java. Understanding the limitations of the Java API is useful not only for this project but for future projects.

So to reiterate the question, is there a way that I can make use of direct path insertion from Java?

Related question:

• Oracle direct-load INSERTs through JDBC?

Answer 1

Oracle documentation clearly states this:

    If you are performing an INSERT with the VALUES clause, specify the APPEND_VALUES hint in 
each INSERT statement immediately after the INSERT keyword. Direct-path INSERT with the VALUES
 clause is best used when there are hundreds of thousands or millions of rows to load. The
  typical usage scenario is for array inserts using OCI. Another usage scenario might be inserts in a FORALL statement in PL/SQL

.

So answer to your question is APPEND_VALUES hint. I can see in your post that you have tried it but can't make out what problem you faced.

Also this assertion in your post is not correct "From what I understand, direct path inserts only supports the subquery syntax and not the VALUES clause." Oracle documentation gives this example :

The following PL/SQL code fragment is an example of using the APPEND_VALUES hint:

FORALL i IN 1..numrecords
  INSERT /*+ APPEND_VALUES */ INTO orderdata 
  VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));
COMMIT;

Link for oracle documentation: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm#i1009100

Sample Code:

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT /*+ APPEND_VALUES */ INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";              
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "test1");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "test2");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();

Answer 2

I found this answer:

direct path inserts are only possible in a insert into x as select * from y scenario. This can be done using jdbc, no problem. This can not be done with insert and values. This also can not be done when the database in in force logging mode. Most of the times when a standby database in connected, the primary database will be in force logging mode.

As Gary Myers mentioned, since 11gR2 there is the APPEND_VALUES hint. As with the 'old' append hint, it should only be used for bulk inserts.