Вставка SQL с большим набором данных

В хороших базах данных это атомарный оператор, поэтому нет возможности ограничить количество вставляемых записей - и это хорошо!

Вы не можете обработать размер фиксации, если явно не закодируете его. Например, вы можете использовать цикл where и закодировать способ ограничения количества данных, которые вы выбираете.

Если вам нужно ограничить набор данных, включите это ограничение в запрос.

Например, на языке Microsoft SQL Server вы можете использовать «TOPN», чтобы убедиться, что запрос возвращает только ограниченное количество строк.

INSERT INTO thisTable
  SELECT TOP 100 * FROM anotherTable;

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

Я не хочу использовать цикл where, потому что это может увеличить производительность. Не так ли?

~ Шри

Я написал код на разных языках, в основном на Java, для выполнения массовых вставок, подобных тому, что вы описали. Каждый раз, когда я это делал, в основном из-за синтаксического анализа какого-либо входного файла или чего-то в этом роде, я просто готовил подмножество данных для вставки из общего количества (обычно партии по 4000 или около того) и передавал эти данные на наш слой DAO. . Так было сделано программно. Мы никогда не замечали реального снижения производительности от такого подхода, и мы имели дело с несколькими миллионами записей. Если у вас есть большие наборы данных для вставки, операция «займет некоторое время», независимо от того, как вы это делаете.

Вы правы, возможно, вам захочется запускать большие вставки партиями. Прилагаемая ссылка показывает способ сделать это в SQL Server. Если вы используете другой бэкэнд, вы бы сделали что-то похожее, но точный синтаксис может отличаться. Это тот случай, когда допустима петля.

http://www.tek-tips.com/faqs.cfm?fid=3141

«Причина, по которой я хочу это сделать, состоит в том, чтобы избежать нехватки места в сегменте отката. Кроме того, я хочу, чтобы результаты заполнялись в целевой таблице через регулярные промежутки времени».

Первый - это просто вопрос правильного определения размера табличного пространства отмены. Поскольку отмена представляет собой удаление существующей строки, для нее не требуется много места. И наоборот, для удаления обычно требуется больше места, потому что у него должна быть копия всей удаленной строки, чтобы повторно вставить ее для отмены.

Для второго взгляните на v $ session_longops и / или rows_processed в v $ sql.

В контексте того, что исходный плакат хочет избежать проблем с пространством отката, ответ довольно прост. Размер сегментов отката должен соответствовать размеру транзакций, а не наоборот. Вы совершаете фиксацию, когда ваша транзакция завершена.

Дэвид Олдридж прав, размер сегмента отката зависит от максимальной транзакции, когда вы хотите, чтобы INSERT либо завершился успешно, либо завершился неудачно.

Некоторые альтернативы:

Если вас не волнует возможность отката (для чего предназначен этот сегмент), вы можете ALTER TABLE и добавить предложение ОТСУТСТВИЕ. Но это не разумный шаг, если вы не загружаете таблицу отчетов, в которой вы отбрасываете все старые строки и загружаете новые, или в некоторых других особых случаях.

Если вас устраивает вставка одних строк и сбоев других по какой-либо причине, добавьте поддержку обработки сбоев, используя синтаксис ВСТАВИТЬ ВЗАПИСАТЬ ОШИБКИ В.

Вы можете просто сделать индексы NOLOGGING. Таким образом, данные таблицы можно будет восстановить, но индексы необходимо будет перестроить, если таблица будет восстановлена. Обслуживание индекса может привести к множеству отмен.

INSERT INTO TableInserted
SELECT *
FROM (
   SELECT  *,
          ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
   FROM TableSelected
) X
WHERE RowNumber BETWEEN 101 AND 200

Вы можете довольно легко обернуть вышеуказанное в цикл while, заменив 101 и 200 переменными. Это лучше, чем делать по одной записи за раз.

Я не знаю, какие версии Oracle поддерживают оконные функции.

Это расширенный комментарий, демонстрирующий, что установка индексов на NOLOGGING поможет нет уменьшить UNDO или REDO для INSERT.

В руководстве подразумевается, что индексы NOLOGGING могут помочь улучшить DML за счет сокращения UNDO и REDO. И поскольку NOLOGGING помогает с табличным DML, кажется логичным, что он также поможет с изменениями INDEX. Но этот тестовый пример демонстрирует, что изменение индексов на NOLOGGING не влияет на инструкции INSERT.

drop table table_no_index;
drop table table_w_log_index;
drop table table_w_nolog_index;

--#0: Before
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#1: NOLOGGING table with no index.  This is the best case scenario.
create table table_no_index(a number) nologging;
insert /*+ append */ into table_no_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#2: NOLOGGING table with LOGGING index.  This should generate REDO and UNDO.
create table table_w_log_index(a number) nologging;
create index table_w_log_index_idx on table_w_log_index(a);
insert /*+ append */ into table_w_log_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#3: NOLOGGING table with NOLOGGING index.  Does this generate as much REDO and UNDO as previous step?
create table table_w_nolog_index(a number) nologging;
create index table_w_nolog_index_idx on table_w_nolog_index(a) nologging;
insert /*+ append */ into table_w_nolog_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

Вот результаты статистических запросов. Цифры суммируются за сеанс. Тестовые случаи №2 и №3 имеют одинаковое увеличение UNDO и REDO.

--#0: BEFORE: Very little redo or undo since session just started.
redo size      35,436
undo change vector size    10,120

--#1: NOLOGGING table, no index: Very little redo or undo.
redo size      88,460
undo change vector size    21,772

--#2: NOLOGGING table, LOGGING index: Large amount of redo and undo.
redo size   6,895,100
undo change vector size 3,180,920

--#3: NOLOGGING table, NOLOGGING index: Large amount of redo and undo.
redo size   13,736,036
undo change vector size 6,354,032