Чем TRUNCATE отличается от DELETE и DROP?
- Как быстро очистить таблицу?
- Можно ли откатить TRUNCATE?
DELETE удаляет строки (можно с WHERE), построчно, с триггерами; TRUNCATE очищает таблицу целиком и быстро; DROP удаляет саму таблицу со структурой.
- DELETE — DML: пишет каждую строку в журнал, запускает row-триггеры, медленный на больших таблицах
- TRUNCATE — DDL: освобождает страницы целиком, сбрасывает последовательности (RESTART IDENTITY), берёт эксклюзивную блокировку
- Откат TRUNCATE зависит от СУБД: в PostgreSQL — да, в MySQL и Oracle — нет (неявный коммит)
А ещё После массового DELETE в PostgreSQL место освобождает VACUUM, а не сам DELETE
Подробно
DELETE — команда DML. Удаляет строки по условию, для каждой срабатывают триггеры и проверки внешних ключей, каждая строка попадает в журнал. Полностью транзакционна. В PostgreSQL строки лишь помечаются удалёнными (MVCC), место освобождает VACUUM — после удаления половины таблицы файл не уменьшится.
TRUNCATE — команда DDL. Не перебирает строки, а освобождает страницы таблицы целиком — мгновенно даже для миллиардов строк. Особенности: нет WHERE, не срабатывают построчные триггеры, берётся эксклюзивная блокировка таблицы, последовательности можно сбросить (RESTART IDENTITY), нельзя выполнить на таблице, на которую ссылаются внешние ключи, без CASCADE. В PostgreSQL TRUNCATE транзакционен и откатывается; в MySQL и Oracle вызывает неявный коммит.
DROP — удаляет таблицу вместе со структурой, индексами, ограничениями и правами.
Для больших объёмов удаляют не DELETE на всю таблицу, а: TRUNCATE, если нужно всё; удаление пачками по индексу; или партиционирование по времени — старая партиция отключается и удаляется мгновенно (DETACH + DROP).
Что хотят услышать
- Различия: строки, содержимое целиком, структура.
- Скорость, триггеры, журнал, блокировки.
- Откатываемость в разных СУБД.
- Удаление больших объёмов через партиции — плюс.
Частые ошибки
- Считать, что DELETE сразу освобождает место на диске.
- Делать DELETE на миллиард строк одной транзакцией.
Проверьте себя
Один верный вариант. Подумайте, потом откройте ответ.
Показать варианты и ответ
- ✗DELETE и TRUNCATE полностью идентичны
- ✗TRUNCATE удаляет строки по условию, DELETE очищает всю таблицу, а DROP — только индексы таблицы
- ✗DROP удаляет только данные, оставляя структуру таблицы
- ✓TRUNCATE очищает всю таблицу, DELETE удаляет строки по условию, DROP — саму таблицу
Что спросят дальше
- Можно ли откатить TRUNCATE?
- Что происходит с identity/sequence при TRUNCATE?
Разбор темы
- SQL: операторы, NULL и фильтрация
Группы команд, логический порядок SELECT, трёхзначная логика NULL, разница WHERE/HAVING и оконные функции — с ловушками, на которых валятся на собеседовании.
Связанные вопросы
Разбор собеседования с sotto
После собеседования sotto разбирает разговор: оценка, каждый вопрос — хорошо, частично или слабо, чего не хватило в ответе и над чем поработать к следующему разу. Первые 30 минут бесплатно.