среда, 10 декабря 2008 г.

Копирование данных без длинных транзакций

Как быть если требуется перенести большой объем данных из одной таблицы в другую? Можно воспользоваться например выгрузкой данных в промежуточный файл. Если данных не очень много то можно попробовать использовать прямую вставку с помощью insert into ... select ... from ... однако такой способ не будет работать если данных очень много, поскольку высока вероятность длинной транзакции. Напрашивается способ использования таблицы без журналирования (raw table) однако он не подходит если сервер используется в паре HDR.
Следующий способ был бы универсальным однако он не реализован:

insert into t1 select ... from t2 where ... commitrows N;


Таким образом приходится придумывать свои способы. Один из таких способов это использование хранимой процедуры и объявления курсора через foreeach. Этот способ позволяет делать commit через нужное число вставленных строк. Вот примерная реализация этого способа (нечто подобное обсуждали как то на конференции comp.databases.informix):

CREATE PROCEDURE batch_move(commitrows int);
...
DEFINE count_rows INT;
...
LET count_rows = 0;
BEGIN WORK;
...
lock table table2 in exclusive mode;
FOREACH cur_ins WITH HOLD FOR
SELECT список полей INTO список переменных FROM table1
LET count_rows = count_rows + 1;
INSERT INTO table2 values (список переменных);
IF mod(count_rows, commitrows) = 0 THEN
COMMIT WORK;
BEGIN WORK;
lock table table2 in exclusive mode;
END IF;
END FOREACH;
COMMIT WORK;
...
END PROCEDURE;


commitrows в параметре процедуры это число записей, через которые последует очередной commit. Таким способом можно перенести любое число записей не опасаясь длинной транзакции, надо лишь задавать реалистичное значение commitrows, которое выбирается индивидуально исходя из настроек сервера и размера записи в таблице-источнике. Опционально можно эксклюзивно блокировать таблицу источник и таблицу приемник, выставлять нужный уровень изоляции и т.д.

1 комментарий:

Gleb комментирует...

Согласен, метод простой, как двери, и подходит практически в любой ситуации, но только если время исполнения не важно.

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

Не говоря уже о таблицах с синтетическим ключем (например, на основе SERIAL). Для таких таблиц просто нужно иметь скрипт анализа пространства ключей с подсчетом нужных диапазонов копирования и хранением их во временной таблице. И т.д.

Ибо выполнение 1000 запросов вставки по 1000000 записей уж точно лучше выполнения 1000000000 запросов вставки по 1 записи.
И запросов к индексу ключа, думаю, потребуется в районе логорифма по основанию 2 от количества порций данных (с коефициентом, скорее всего, не менее 2).