Databases
StackOverflow2013
Postsdbo
POIs it possible to use direct path insertion with JDBC/Java?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible to use direct path insertion with JDBC/Java?
    primarykey
    data
    text
    <p>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 <code>APPEND</code> and <code>NOLOGGING</code> hints to take advantage of direct path insertion and reduce the amount of redo generated. <code>NOLOGGING</code> makes sense for this as it is a temporary staging table and the data can be recovered from the log files if needed.</p> <p>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?</p> <p>The things that I have tried and investigated are:</p> <ul> <li>JDBC batching (both standard batching and <a href="http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#autoId2" rel="nofollow noreferrer">Oracle's extensions</a>). 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 direct path.</li> <li>The <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId6" rel="nofollow noreferrer"><code>APPEND_VALUES</code></a> hint. This sounds promising but makes little sense as JDBC batching does not appear to actually perform an "array" insert of many records.</li> </ul> <p>From what I understand, direct path inserts only supports <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables004.htm#autoId10" rel="nofollow noreferrer">the subquery syntax and not the VALUES clause</a>. This cannot be used as the data to be inserted does not exist in the database yet.</p> <p>I have been unable to find any reference to Java being able to use the host array style loading which Pro*C uses.</p> <p>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 future projects. </p> <p>So to reiterate the question, is there a way that I can make use of direct path insertion from Java?</p> <p>Related question:</p> <ul> <li><a href="https://stackoverflow.com/questions/5185835/oracle-direct-load-inserts-through-jdbc">Oracle direct-load INSERTs through JDBC?</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBurhan Ali
    1. USBurhan Ali
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs it possible to use direct path insertion with JDBC/Java?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs it possible to use direct path insertion with JDBC/Java?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs it possible to use direct path insertion with JDBC/Java?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload