title: Optimizing oracle jdbc batch inserts
body: <p>I need to make a large amount of inserts, i.e. double digit millions, into an Oracle-DB using JDBC. To do so I'm using something like the following class, inspired by <a href="https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc">Efficient way to do batch INSERTS with JDBC</a>:</p>
  
  <pre><code>public class Inserter {
      private final int batchSize;
      private final Connection con; // with .setAutoCommit(false)
      private final PreparedStatement ps;
      private int currentSize = 0;
  
      public Inserter(Connection con, PreparedStatement ps, int batchSize) {
          this.con = con;
          this.ps = ps;
          this.batchSize = batchSize;
      }
  
      public void addInsert(Object[] vals) throws SQLException {
          ps.clearParameters(); // should be redundant, but better safe than sorry
          for (int i = 0; i &lt; val.length; i++) {
              this.ps.setObject(i + 1, vals[i]);
          }
          ps.addBatch();
          currentSize++;
  
          if (currentSize &gt;= batchSize) {
              ps.executeBatch();
              currentSize = 0;
          }
      }
  
      public void flush() {/** to flush leftovers */}
  }
  </code></pre>
  
  <p>While this way of inserting works fine, it is very slow. <a href="https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance">JDBC batch insert performance</a> describes how basically this exact issue can be dealt with for MySQL, as <code>rewriteBatchedStatements</code> does however not seem to exist on Oracle it is not of much help here.</p>
  
  <p>To improve performance I have also tried to switch the Statement to one big <code>INSERT ALL ...</code>/<code>INSERT APPEND ...</code> statement, as per <a href="https://stackoverflow.com/questions/18075220/oracle-11g-most-efficient-way-of-inserting-multiple-rows">Oracle 11g - most efficient way of inserting multiple rows</a>, which made everything even slower.</p>
  
  <p>My question would thus be if there is any way to optimize these inserts beyond simply using <code>addBatch()</code> and <code>executeBatch()</code>? Or is there maybe some gross, inefficient mistake in the <code>Inserter</code> class above? Any help would be very much appreciated.</p>
  
  <hr>
  
  <p>More possibly useful information:</p>
  
  <ul>
  <li><p>The tables to be inserted to are partitioned with around one to ten million rows per partition.</p></li>
  <li><p>There is a unique constraint on the tables looking something like this   <code>unique(id1, id2, id3, id4)</code> where all columns in it are of type <code>NUMBER</code> and further bound by foreign key constraints to primary keys in other tables.</p></li>
  </ul>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Following the suggestions from the comments I switched the <code>setObject(index, val)</code> call to:</p>
  
  <ol>
  <li><p><code>setInt(index, val)</code>, <code>setFloat(index, val)</code>, <code>...</code>, <code>setNull(index, type)</code> calls where apprpropriate</p></li>
  <li><p><code>setObject(index, val, type)</code> and <code>setNull(index, typR)</code></p></li>
  </ol>
  
  <p>Neither version improved performance significantly.</p>
  
  <p>Furthermore I tried inserting the data into a staging table without any constraints on it, which also did not lead to better performance.</p>
  
  <p>To compare, exporting the data to CSV and loading it in using SQL*Loader lead to a significant performance increase, i.e. ~4.5k => ~50k+ rows per second for the slowest table<sup>1</sup>.</p>
  
  <p>This leads me to believe the bottleneck to lie with JDBC.</p>
  
  <p><sup>1</sup><sub>Alas, the use of SQL*Loader is not a (desired) option in my particular case.</sub></p>