oracle-databaseperformancehibernatespring-data-jpabulk-operations

Insert huge data into Oracle DB


My setup - Oracle DB 12.1C, Spring application with Hibernate.

table:

create table war
(
  id      int generated by default as identity                  not null constraint wars_pkey primary key,
  t1_id   int references t1 (id) on delete cascade              not null,
  t2_id   int references t2 (id) on delete cascade              not null,
  day     timestamp                                             not null,
  diff    int                                                   not null
);

I would like to insert 10 000 records into table. With repository.saveAll(<data>) it takes 70s, with using JpaTemplate.batchUpdate(<insert statements>) it takes 68s. When I create new temporary table without constraints it takes 65s.

What is the best/fastest way, how to insert this amount of records into Oracle DB? Unfortunately CSV is not an option.


Solution

  • My solution was to redesign our model - we used int_array for storing diff -> this was almost 10 times faster than first solution.