Массовая вставка из Java в Oracle
Мне нужно быстро вставить много маленьких строк в Oracle. (5 полей).
С MySQL я делю вставки на группы по 100, а затем использую одну инструкцию вставки для каждой группы из 100 вставок.
Но с Oracle пользовательский отзыв заключается в том, что массовые вставки (где-то от 1000 до 30000) слишком медленные.
Есть ли подобный прием, который я могу использовать для ускорения программных вставок из Java в Oracle?
3 ответа
Вы можете использовать DAO-модуль Spring для пакетной вставки множества строк.
Пример, который вставляет коллекцию объектов Order в базу данных в одном обновлении:
public class OrderRepositoryImpl extends SimpleJdbcDaoSupport implements
OrderRepository {
private final String saveSql = "INSERT INTO orders(userid, username, coffee, coffeename, amount) "
+ "VALUES(?, ?, ?, ?, ?)";
public void saveOrders(final Collection<Order> orders) {
List<Object[]> ordersArgumentList = new ArrayList<Object[]>(orders
.size());
Object[] orderArguments;
for (Order order : orders) {
orderArguments = new Object[] { order.getUserId(),
order.getUserName(), order.getCoffe(),
order.getCoffeeName(), order.getAmount() };
ordersArgumentList.add(orderArguments);
}
getSimpleJdbcTemplate().batchUpdate(saveSql, ordersArgumentList);
}
}
Вы не остаетесь, как вы передаете эти записи в базу данных. Наилучшим способом является использование массива, поскольку это позволяет использовать множество изящных массовых операций FORALL в Oracle.
В этом примере пакета есть две процедуры. Один, который заполняет коллекцию записей T23 (таблица, которая содержит пять числовых столбцов), а другой, который массово вставляет записи в эту таблицу, используя массив.
SQL> create or replace package p23 as
2 type t23_nt is table of t23%rowtype;
3 function pop_array ( p_no in number )
4 return t23_nt;
5 procedure ins_table ( p_array in t23_nt );
6 end p23;
7 /
Package created.
SQL> create or replace package body p23 as
2
3 function pop_array ( p_no in number )
4 return t23_nt
5 is
6 return_value t23_nt;
7 begin
8 select level,level,level,level,level
9 bulk collect into return_value
10 from dual
11 connect by level <= p_no;
12 return return_value;
13 end pop_array;
14
15 procedure ins_table
16 ( p_array in t23_nt )
17 is
18 s_time pls_integer;
19 begin
20
21 s_time := dbms_utility.get_time;
22
23 forall r in p_array.first()..p_array.last()
24 insert into t23
25 values p_array(r);
26
27 dbms_output.put_line('loaded '
28 ||to_char(p_array.count())||' recs in '
29 ||to_char(dbms_utility.get_time - s_time)
30 ||' csecs');
31 end ins_table;
32 end p23;
33 /
Package body created.
SQL>
Вот результат некоторых пробных прогонов:
SQL> declare
2 l_array p23.t23_nt;
3 begin
4 l_array := p23.pop_array(500);
5 p23.ins_table(l_array);
6 l_array := p23.pop_array(1000);
7 p23.ins_table(l_array);
8 l_array := p23.pop_array(2500);
9 p23.ins_table(l_array);
10 l_array := p23.pop_array(5000);
11 p23.ins_table(l_array);
12 l_array := p23.pop_array(10000);
13 p23.ins_table(l_array);
14 l_array := p23.pop_array(100000);
15 p23.ins_table(l_array);
16 end;
17 /
loaded 500 recs in 0 csecs
loaded 1000 recs in 0 csecs
loaded 2500 recs in 0 csecs
loaded 5000 recs in 1 csecs
loaded 10000 recs in 1 csecs
loaded 100000 recs in 15 csecs
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from t23
2 /
COUNT(*)
----------
119000
SQL>
Я думаю, что вставка 100 000 записей за 0,15 секунды должна понравиться всем, кроме самых требовательных пользователей. Итак, вопрос в том, как вы подходите к своим вставкам?
Попытайся ,
public Boolean inserTable(String fileName) {
logger.info("Begin - " + this.getClass().getSimpleName() + "." + "inserTable");
logger.info("File : " + fileName);
try (Connection conn1 = jdbcTemplate.getDataSource().getConnection();) {
OracleConnection conn = ( OracleConnection ) conn1.getMetaData().getConnection();
ScriptRunner sr = new ScriptRunner(conn);
StringBuilder sBuffer = new StringBuilder();
StringBuffer sb=new StringBuffer();
String query = "Insert into TABLE_DATA (ID, DATA1, DATECREATED, CREATEDBY) Values ";
String line = "";
//Creating a reader object
BufferedReader br = new BufferedReader(new FileReader(fileName),1024 * 1024 );
while ((line = br.readLine()) != null) {
//logger.info("Leyo linea : " + line);
sb.append(query.concat("(").concat("TABLE_DATA_SQ.NEXTVAL,").concat(line.substring(0,6)).concat(",").concat("sysdate,").concat("'BDVENLINEA'").concat(");"));
sb.append("\n");
}
sb.append("commit;");
Reader reader = new StringReader(sb.toString());
//Running the script
sr.runScript(reader);
reader.close();
br.close();
return true;
} catch (FileNotFoundException e) {
logger.error(e.getMessage(), e);
throw new TechnicalException(e, e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
throw new TechnicalException(e, e.getMessage());
} catch (IOException e) {
e.printStackTrace();
throw new TechnicalException(e, e.getMessage());
} finally {
logger.info("End - " + this.getClass().getSimpleName() + "." + "inserTable");
}
}
В настоящее время MySQL - это Oracle, поэтому, возможно, более простым решением будет остаться на MySQL...
Если нет, то вам следует убедиться, что транзакция запущена до того, как вы начнете свою группу вставок, как только группа будет завершена, зафиксируйте транзакцию и начните новую транзакцию для следующей группы вставок.
Также проверьте наличие ненужных определений индекса, которые могут замедлять время вставки.
Обновление...
Массовая вставка относится к последнему шагу ETL (Extract Transform Load), поэтому вы рассматривали возможность использования инструмента ETL на основе Java, такого как pentaho kettle или talend-studio.
Pentaho описывает свои средства массовой загрузки Oracle здесь.
Быстрый Google также показывает некоторые предварительные доказательства того, что Talend также имеет некоторую поддержку массовой загрузки Oracle.