Множественная вставка строк в Oracle: эффективный способ

Быстрый ответ

Если вам необходимо обеспечить высокую производительность при вставке множества записей в базу данных Oracle, можно использовать оператор INSERT ALL для версий до 23c или более простой синтаксис, доступный начиная с Oracle 23c.

Для версии до 23c:

INSERT ALL
  INTO my_table (col1, col2) VALUES ('val1', 'val2')
  INTO my_table (col1, col2) VALUES ('val3', 'val4')
SELECT * FROM dual;

Для версии Oracle 23c и последующих:

INSERT INTO my_table (col1, col2) VALUES 
('val1', 'val2'),
('val3', 'val4');

Важно помнить о лимите в 1000 строк за одну транзакцию, чтобы сократить время для анализа и увеличить пропускную способность.

Продвинутые стратегии вставки

Локальные PL/SQL процедуры

Используя PL/SQL процедуры, вы можете улучшить гибкость и контроль над процессом вставки:

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO my_table (col1, col2) VALUES (i, 'value'||i);
  END LOOP;
END;

Для ускорения вставки можно использовать FORALL:

DECLARE
  TYPE t_my_table IS TABLE OF my_table%ROWTYPE;
  v_my_data t_my_table := t_my_table(); 
BEGIN
  -- Заполняем v_my_data данными
  FORALL i IN v_my_data.FIRST .. v_my_data.LAST 
    INSERT INTO my_table VALUES v_my_data(i);
END;

Подробнее об этом расскажет наш спикер на видео

Использование SQL*Loader

SQL*Loader идеально подходит для загрузки внешних данных в таблицу:

sqlldr userid=myuser/mypassword control=mycontrol.ctl log=mylog.log

Важно корректно настроить файл управления (mycontrol.ctl):

LOAD DATA
INFILE 'mydata.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(col1, col2)

Обязательно проверьте корректность вставленных данных после загрузки.

Визуализация

Представьте работу с вашей базой данных через эмодзи:

🛤️ Работа с базой данных = Таблица Oracle

Вставка по одной записи – это как медленное и однообразное действие. А вот множественная вставка – это быстрое и эффективное решение.

Итак, множественная вставка позволяет ускорить процесс и оптимизировать работу с базой данных.

Оптимизация вставок: следующие шаги

Эффективное перемещение данных

Для эффективного перемещения данных между таблицами используйте INSERT INTO ... SELECT:

INSERT INTO dest_table (col1, col2)
SELECT col1, col2 FROM source_table;

Разбиение данных на части

При работе с большими наборами данных, стоит разделить операцию вставки на части, чтобы лучше управлять ресурсами.

Оптимизация для параллельной обработки данных

Если требуется выполнить конкурентные операции, используйте подсказки или ключевое слово APPEND для ускорения вставки данных.

Полезные материалы

1. Официальное руководство Oracle по синтаксису многотабличных вставок.
2. Рекомендации Тома Кайта из Oracle о вставке нескольких строк одним SQL-запросом.
3. Практичный подход к пакетной вставке в JDBC.
4. Использование FORALL для вставки, обновления и удаления записей в Oracle.
5. Советы по оптимизации операций вставки в Oracle.