11

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:

Honey
  • 2,208
  • 11
  • 21
Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
  • Have you found a way to direct-insert through jdbc? – Denis Nov 06 '14 at 12:18
  • Nope. Doesn't look like it is possible. We ended up just using conventional path loading and parallelising it. I'm still hopeful that someone will come along and point out something that I missed. – Burhan Ali Nov 06 '14 at 13:59
  • I see smb was able to test it, however it wan't useful in his case: http://asanga-pradeep.blogspot.com/2014/10/appendvalues-hint-and-jdbc.html – Denis Nov 06 '14 at 14:34
  • @BurhanAli - Maybe try to use the jdbc oci driver. It's supposed to be the JNI layer over OCI C based functionality. – Kumar Vaibhav Apr 25 '18 at 14:31
  • If you can only use it with the subquery syntax than could you use `INSERT INTO table_name ( value ) SELECT 'literal' FROM DUAL UNION ALL SELECT 'other' FROM DUAL`? – MT0 Dec 20 '20 at 00:56

2 Answers2

0

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.

Beginner
  • 5,277
  • 6
  • 34
  • 71
-2

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();
Lokesh
  • 7,810
  • 6
  • 48
  • 78
  • 3
    I've tried this and it doesn't trigger direct-path insert, because you're still only inserting one record at a time, not an array (as mentioned by the OP and as illustrated in the PL/SQL example). – J. Dimeo Apr 10 '14 at 14:12
  • There are a number of restrictions for APPEND_VALUES to take effect such that it triggers Direct Path loading. – Kumar Vaibhav Apr 02 '18 at 20:12
  • 3
    Also - "Direct-load INSERT (serial or parallel) can only support the INSERT ... SELECT syntax of an INSERT statement, not the INSERT... values syntax", from https://docs.oracle.com/cd/A58617_01/server.804/a58227/ch_dlins.htm – Kumar Vaibhav Apr 02 '18 at 20:50