Oracle sql очистить таблицу
Обновлено: 04.07.2024
Я не человек базы данных, точно, и большая часть моей работы с БД была с MySQL, поэтому простите меня, если что-то в этом вопросе невероятно наивно.
мне нужно удалить 5.5 миллионов строк из таблицы Oracle, которая имеет около 100 миллионов строк. У меня есть все идентификаторы строк, которые мне нужно удалить во временной таблице. Если бы это было всего несколько тысяч строк, я бы сделал следующее:
есть ли что-то, что мне нужно знать, и / или делать по-другому, потому что это 5.5 миллион строк? Я подумал о том, чтобы сделать цикл, что-то вроде этого:
прежде всего - это делает то, что я думаю, что это-пакетные коммиты 200,000 за раз? Предполагая, что это так, я все еще не уверен, лучше ли генерировать 5,5 миллиона SQL-операторов и фиксировать партиями по 200 000 или иметь один SQL-оператор и фиксировать все сразу.
идеи? Передовая практика?
редактировать: я запустил первый вариант, оператор single delete, и он только потребовалось 2 часа, чтобы завершить разработку. Исходя из этого, он находится в очереди на запуск в производство.
первый подход лучше, потому что вы даете оптимизатору запросов четкое представление о том, что вы пытаетесь сделать, вместо того, чтобы пытаться скрыть его. Компонент database engine может использовать другой подход к внутреннему удалению 5.5 m (или 5.5% таблицы), чем к удалению 200k (или 0.2%).
вот также статьи о массовом удалении в Oracle, которое вы можете прочитать.
самый быстрый способ создать новый с CREATE TABLE AS SELECT используя . Я имею в виду:
конечно, вы должны воссоздать ограничения без проверки, индексы с nologging, гранты. но очень очень быстро.
если вы имеете тревогу в продукции, то вы можете сделать следующее:
вы должны позаботиться о:
- хранимые процедуры могут быть признаны недействительными, но они будут перекомпилированы при втором вызове. Вы должны это проверить.
- NOLOGGING означает, что минимальный создаются повторы. Если у вас есть роль DBA, запустите ALTER SYSTEM CHECKPOINT чтобы гарантировать отсутствие потерянных данных при сбое экземпляра.
- на NOLOGGING табличное пространство должно быть также в NOLOGGING .
другой вариант лучше, чем создавать миллионы вставки:
UPDATE: почему я могу гарантировать, что последний блок PLSQL будет работать? Потому что я предполагаю, что:
- никто другой не использует эту временную таблицу по какой-либо причине (dba или задания, собирающие статистику, задачи dab, такие как перемещение, вставка записей и т. д.). Это можно обеспечить потому что вспомогательная таблица только для этот.
- затем, с последним утверждением, запрос будет выполнен ровно С тем же планом и собирается вернуть строки с тем же порядком.
при выполнении массовых удалений в Oracle , убедитесь, что у вас не заканчивается UNDO SEGMENTS .
при выполнении DML , Oracle сначала записывает все изменения в REDO log (старые данные вместе с новыми данными).
когда REDO журнал заполняется или происходит тайм-аут, Oracle выполняет log synchronization : он пишет new data в файлы данных (в вашем случае отмечает блоки файлов данных как свободные) и записывает старые данные в UNDO табличное пространство (так что он остается видимым для параллельных транзакций, пока вы commit ваши изменения).
когда вы фиксируете свои изменения, пробел в UNDO сегменты, занятые транзакцией yuor, освобождаются.
это означает, что если удалить 5M строки данных, вам нужно будет иметь место для all эти строки в свой UNDO сегменты, чтобы данные могли быть перемещены туда первыми ( all at once ) и удаляется только после фиксации.
это также означает, что параллельные запросы (если таковые имеются) необходимо будет прочитать из REDO журналы или UNDO сегментов при выполнении сканирования таблицы. Это не самый быстрый способ доступа к данным.
это также означает, что если оптимизатор будет выбирать HASH JOIN для вашего запроса на удаление (что он, скорее всего, сделает), и временная таблица не будет вписываться в HASH_AREA_SIZE (что, скорее всего, будет так), тогда запрос будет нужен several просматривает над большой таблицей, и некоторые из частей таблицы будут уже переехал в REDO или UNDO .
учитывая все сказанное выше, вам, вероятно, лучше удалить данные в 200,000 куски и зафиксировать изменения между ними.
таким образом, Вы, во-первых, избавиться от проблем, описанных выше, и, во-вторых, оптимизировать свой HASH_JOIN , поскольку у вас будет такое же количество чтений, но сами чтения будут более эффективными.
в вашем случае, однако, я бы попытался заставить оптимизатор использовать NESTED LOOPS , as Думаю, в твоем случае это будет быстрее.
чтобы сделать это, убедитесь, что ваша временная таблица имеет первичный ключ ID , и перепишите свой запрос следующим образом:
вам нужно будет иметь первичный ключ на temp_table для работы этого запроса.
сравните его со следующим:
, посмотрите, что быстрее и придерживайтесь этого.
лучше делать все сразу, как в первом примере. Но я определенно пройдусь по нему с вашим DBA, так как они могут захотеть вернуть блоки, которые вы больше не используете после чистки. Кроме того, могут быть проблемы планирования, которые обычно не видны с точки зрения пользователя.
если исходный SQL занимает очень много времени, некоторые параллельные SQL могут работать медленно, так как они должны использовать UNDO для восстановления версии данных без незафиксированных изменений.
компромисс может быть что-то вроде
вы можете настроить ROWNUM по мере необходимости. Меньший ROWNUM означает более частые коммиты и (возможно) снижение влияния на другие сеансы с точки зрения необходимости применения отмены. Однако, в зависимости от планов выполнения, могут быть и другие последствия, и это вероятно, потребуется больше времени в целом. Технически " FOR " часть цикла не нужна, так как выход завершит цикл. Но я параноик о неограниченных петлях, так как это боль, чтобы убить сеанс, если они застряли.
Я бы рекомендовал запустить это как одно удаление.
есть ли дочерние таблицы того, из которого вы удаляете? Если это так, убедитесь, что внешний ключ в этих таблицах индексируется. В противном случае вы можете выполнить полное сканирование дочерней таблицы для каждой удаленной строки, что может замедлить работу.
вам могут понадобиться некоторые способы проверить ход удаления по мере его выполнения. См.Как проверить базу данных oracle на длительный срок запросы?
Как предложили другие люди, если вы хотите проверить воду, вы можете поместить: rownum
Я сделал что-то подобное в прошлом с Oracle 7, где мне пришлось удалить миллионы строк из тысяч таблиц. По всем характеристикам и особенно больших удалений (миллион строк, плюс в одной таблице) этот скрипт работал хорошо.
вам нужно будет немного изменить его (т. е. изучить пользователей/пароли, а также получить правильные сегменты отката). Также вам действительно нужно обсудить это с вашим DBA и запустить его в тестовой среде. Сказав Все это, довольно легкий. Функция delete_sql() ищет пакет rowids в указанной таблице, а затем удаляет их пакет за пакетом. Например:
приведенный выше пример удаляет 500 записей одновременно из таблицы MSF170 на основе инструкции sql.
Если вам нужно удалить данные из нескольких таблиц, просто включают дополнительные exec delete_sql(. ) строки в файле delete-tables.в SQL
О и не забудьте поставить свои сегменты отката обратно в интернет, это не в сценарии.
Ну и последний совет. Это будет медленно и в зависимости от таблицы может потребоваться некоторое время простоя. Тестирование, синхронизация и настройка-ваш лучший друг здесь.
все ответы здесь отличные, только одна вещь, чтобы добавить: если вы хотите удалить все записей в таблице, а конечно вам не понадобится откат, тогда вы хотите использовать усечь таблицу.
(в вашем случае вы хотели удалить только подмножество, но для тех, кто скрывается с подобной проблемой, я думал, что добавлю это)
Читайте также: