Удаление одной записи из базы данных является простой задачей, которая может быть выполнена щелчком по кнопке мыши, но как удалить, скажем, 250 тысяч записей? В этой статье показан эффективный метод удаления больших объемов данных с упором на управление временем и ресурсами.
Почему большие удаления обременяют базу данных
Большие операции удаления непроизводительно расходуют ресурсы, необходимые для восстановления экземпляра в случае отказа диска. Когда базовая DML (Data Manipulation Language) транзакция (delete, insert, update) изменяет данные, содержимое "старых" данных сохраняется в сегментах отката. Эти сегменты используются прежде всего для отката изменений, сделанных транзакциями, выполнения согласованного чтения и восстановления данных. Пока не выдана команда 'COMMIT', в базе находятся как "старые", так и "новые" данные, и поэтому эти изменения архивируются; они же могут понадобиться для восстановления первоначального состояния. Размер табличного пространства для сегментов отката и распределение сегментов отката оптимальных размеров в этом табличном пространстве является критичным для успеха операции.
В идеале размер сегмента отката должен составлять примерно 10% от размера самой большой активной таблицы. Это не всегда возможно, особенно когда размер сегмента отката меньше, чем объем данных, с которыми производятся манипуляции. Транзакции заполнят сегменты отката и не смогут продолжиться из-за недостаточного размера табличного пространства. Предположим, что удаляется один миллион записей, занимающих 900М, а размер вашего сегмента отката всего лишь 500М; Oracle выдаст сообщение об ошибке 'UNABLE TO EXTEND ROLLBACK SEGMENT' ('НЕВОЗМОЖНО РАСШИРИТЬ СЕГМЕНТ ОТКАТА') и аварийно завершит транзакцию.
Один методов состоит в том, чтобы разделить delete-предложение на меньшие части и выдавать 'COMMIT' для каждой из них. Однако, этот метод перегружает оперативный журнал регистрации изменений (online redo log) и процессы архивации файлов этого журнала. Оперативный журнал регистрации изменений - это набор двух или больше файлов, в которые процесс LGWR (Log Writer) экземпляра записывает все зафиксированные или не зафиксированные изменения в базе данных.По мере выполнения delete-предложения файл журнала регистрации наполняется записями. После заполнения очередного файла, журнал переключается, то есть LGWR приостанавливается, прекращает запись в заполнившийся файл, а затем начинается запись в следующий доступный файл журнала.
Когда журнал переключается и вызывается процесс архивирования для копирования накопленных файлов журнала по месту их архивного хранения, производительность работы базы снижается, поскольку новые записи не могут быть записаны в следующий доступный файл журнала до тех пор, пока не завершится архивирование. Другой недостаток этого метода - неосвобождение памяти табличного пространства в ходее выполнения команды DELETE. Хотя сегмент данных таблицы содержит уже значительно меньшее количество записей, база данных пока считает удаленые записи за обычные входы. В сочетании с задержками от оперативной журнализации и архивирования, эта задержка с освобождением памяти еще более снижает производительность транзакций, угрожая отсутствием свободного места для новых записей. Этот метод удаления данных может занимать много времени (часы) и излишне обременяет ресурсы базы данных.
Более эффективный метод
Предлагается простой логический в 10 шагов способ для удаления большого объема данных, который более эффективен как по скорости, так и по применяемым ресурсам. В практике применения этот способ может занять всего лишь час времени, и то в зависимости от физической среды, используемой для назначения экспорта.
Следует иметь в виду:
- необходимо удостовериться, что в наличии имеются все скрипты для перегенерации пакетов;
- надо выбрать табличное пространство, в котором будут созданы временные таблицы. Ваше пользовательское табличное пространство TEMPSPACE, которое обычно слишком велико для OLTP (On-Line Transaction Processing - оперативная обработка транзакий) сортировок, является хорошим кандидатом для этого [ прим. редактора : Обычно табличное пространство для временных сегментов имеею ствтус TEMPORARY, что исключает возможность размещения в нем других, нежели временных сегментов];
- следует удостовериться, что ваши объем системных томов (или устройств) достаточно велик, чтобы отвечать требованиям шага 5. Я настоятельно рекомендую, чтобы выбор группы предназначенных для "дампинга" ("dumping") томов (или устройств) начинался с магнитных лент, а не с более медленых механизмов.
Последовательность действий
Шаг_1: Отключение табличных триггеров, тел пакетов и ограничений целостности
Поскольку необходимо будет сделать дамп (dump - свалка) данных, необходимо предотвратить задействование табличных триггеров, тел пакетов и ограничений целостности от выполнения операций обновления и проверки при прохождении шагов 6 и 7. Если некая таблица является родителем (parent) в справочном отношении (referential relation), необходимо отключить внешние ключи. Кроме того, если справочные ограничения не содержат фразы 'ON DELETE CASCADE' , необходимо также спланировать удаление записей из дочерней (child) таблицы (как только вы закончили все шаги по удалению записей из родительской таблицы), чтобы обеспечить согласованность. (Если потребуется проанализировать исходный код, следует использовать представление словаря данных DBA_SOURCE.)
Синтаксис:
ALTER TABLE "table_name" DISABLE ALL TRIGGERS;
DROP PACKAGE BODY "package_body_name";
ALTER TABLE DISABLE CONSTRAINT "ref_constraint_name";
ШАГ-2. Создание новой таблицы для сохраненных данных
Следует использовать предложение 'CREATE TABLE AS SELECT' (CTAS) для создания некой таблицы, именуемой, например, 'keep_table' для данных, которые желательно сохранить. Можно даже попытаться оптимизировать ввод/вывод этой таблицы. (См. статью " Turning the Tables on Disk I/O" в January/February 2000 выпуске журнала Oracle Magazine .) Можно также использовать предложение CTAS, чтобы переупорядочить табличные записи, используя в предложении create указание (hint) или фразу 'ORDER BY'. Заметим, что таким образом создается только табличная структура с данными, но не индексы и привилегии (grants).
Синтаксис
CREATE TABLE "keep_table" AS SELECT * FROM "original_table"
WHERE "criteria = keep";
Шаг_3: Переименование первоначальной таблицы
Следует переименовать первоначальную таблицу, ассоциируя ее имя, например, от 'original_table' в 'original_table_tmp'. Это переименование позволяет повторно использовать оригинальное имя таблицы, перенаправляет все индексы и привилегии по новому имени таблицы.. Это действие не связано с операциями ввода/вывода; единственно обновляется только словарь данных в самой глубине базы данных.
Синтаксис
RENAME "original_table" TO "'original_table_tmp";
Шаг_4: Переименование 'keep_table' в 'original_table'
Присвоение таблице сохраненных данных имени первоначальной таблицы позволяет выполнить действия с этой таблицей, как с начальной таблицей. Опять же это действие не связано с операциями ввода/вывода, обновляется только словарь данных..
Синтаксис
RENAME "keep_table" TO "original_table";
Шаг_5: Экспорт новой 'original_table'
Надо экспортировать таблицу, которая содержит сохраненные данные. Опять же это - только данные без индексов. Необходимо, чтобы объем томов (или устройств) был достаточен для осуществления такого действия. Если это не выполнимо, я настоятельно рекомендую выбрать магнитные ленты для этого "дампирования", даже если это решение приведет к увеличению времени всей операции. Утилита экспорта, являющаяся частью Oracle 7 RDBMS, имеет много опций по умолчанию. Для того, чтобы все их посмотреть надо ввести команду 'exp help=y ' в командной строке Unix [ прим. редактора : в СУБД Oracle 7/8 под управлением различных Windows исполнительный файл утилиты экспорта именуется как exp73.exe, exp80.exe,… Соответственно вызов из командной строки: 'exp{73/80/.…}.exe help=y '] .
Синтаксис
EXP "username"/"password" BUFFER="value" TABLES="table_name" ROWS=y FILE="dump_file_name"
Шаг_6: Опустошение временной таблицы
Когда экспорт выполнен, надо опустошить (truncate) временную таблицу. Операция опустошения не использует сегмент отката, вызывая неявное фиксирование (commit). При ней сохраняются индексные структуры и привилегии, но освобождается занятое пространство. На этом шаге надо быть особенно осторожным, поскольку это действие необращаемо.
Синтаксис
TRUNCATE TABLE "original_table_tmp";
Шаг_7: Удаление экспортированной таблицы
После выполнения экспорта надо удалить (drop) экспортированную таблицу. Данные из нее разгружены в файл.
Синтаксис
На приглашение SQL> ввести:
DROP TABLE"original_table"
Шаг_8: Переименование временной таблицы
Надо восстановить начальное имя временной оригинальной таблицы. Эта таблица не содержит каких-либо записей, но сохраняет структуру. Хорошая идея - воспользоваться индекс-файлом (index-file), который содержит все соответствующие индексы, привилегии и ограничения целостности в виде SQL-скрипта. Можно воспользоваться этим скриптом, чтобы воссоздать индекс, но это не является необходимым.
Синтаксис
RENAME "original_table_tmp" TO "original_table";
Шаг_9: Импорт дамп-файла ('dump file') в начальную таблицу
Надо выполнить импорт экпортного дамп-файла в начальную таблицу. Эта таблица имеет точно ту же структуру, с которой вы начинали. Для того, чтобы получить опции и параметры импорта, следует в командной строке Unix ввести команду "imp help=y" [ прим. редактора : в СУБД Oracle 7/8 под управлением различных Windows исполнительный файл утилиты импорта именуется как imp73.exe, imp80.exe,… Соответственно вызов из командной строки: 'imp{73/80/.…}.exe help=y '] . Лучше всего написать скрипт. Я бы посоветовал использовать опцию 'COMMIT=Y', чтобы освобождать буфер импорта каждый раз после его заполнения, отпускать сегмент отката, не дожидаясь завершения всего импорта. Однако, если этот процесс оборвется, небходимо будет опустошить (truncate) таблицу и повторить импорт с начала.
IMP "username"/"password" BUFFER="value" FILE="dump_file" TABLES="table_name" IGNORE=y COMMIT=y
Шаг_10: Подключение триггеров, внешних ключей, тел пакетов
Подключение триггеров, внешних ключей, тел пакетов, отключенных на шаге_1. Теперь, когда операция удаления полностью завершена, это действие в достаточной степени безопасно.
Синтаксис
ALTER TABLE "original_table" ENABLE ALL TRIGGERS;
Запуск скрипта воссоздания тел пакета
ALTER TABLE ENABLE CONSTRAINT "ref_constraint_name";
Вспомним об оптимизаторе
После любой серьезной экзекуции, проделанной с базой данных, правильным было бы проанализировать (analyze) таблицы, чтобы создать статистику, которой будет пользоваться стоимостной оптимизатор (cost-based optimizer) для построения наиболее эффективных запросов.
Дополнительные преимущества этого решения
Дополнительно к реализации более быстрого и менее ресурсоемкого способа массовых удалений эта процедура обеспечивает легкий механизм архивирования исторических данных. А также хорошо применять этот прием для расщепления (splitting) таблиц.