Отличие delete от truncate. Параметры или аргументы

TRUNCATE - опустошить таблицу или набор таблиц

Синтаксис

TRUNCATE [ TABLE ] [ ONLY ] имя [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

Описание

Команда TRUNCATE быстро удаляет все строки из набора таблиц. Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется. Наиболее полезна она для больших таблиц.

Параметры

имя

Имя таблицы (возможно, дополненное схемой), подлежащей опустошению. Если перед именем таблицы указано ONLY , очищается только заданная таблица. Без ONLY очищается и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание * , чтобы явно обозначить, что блокировка затрагивает и все дочерние таблицы. RESTART IDENTITY

Автоматически перезапускать последовательности, связанные со столбцами опустошаемой таблицы. CONTINUE IDENTITY

Не изменять значения последовательностей. Это поведение по умолчанию. CASCADE

Автоматически опустошать все таблицы, ссылающиеся по внешнему ключу на заданные таблицы, или на таблицы, затронутые в результате действия CASCADE . RESTRICT

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

Замечания

Чтобы опустошить таблицу, необходимо иметь право TRUNCATE для этой таблицы.

Команда TRUNCATE запрашивает блокировку ACCESS EXCLUSIVE для каждой таблицы, которую она обрабатывает. Когда указано RESTART IDENTITY , все последовательности, которые должны быть перезапущены, так же блокируются исключительно. В случаях, когда требуется обеспечить параллельный доступ к таблице, следует использовать DELETE .

TRUNCATE нельзя использовать с таблицей, на которую по внешнему ключу ссылаются другие таблицы, если только и эти таблицы не опустошаются этой же командой. Проверка допустимости очистки в таких случаях потребовала бы сканирования таблицы, а главная идея данной команды в том, чтобы не делать этого. Для автоматической обработки всех зависимых таблиц можно использовать указание CASCADE - но будьте очень осторожны с ним, иначе вы можете потерять данные, которые не собирались удалять!

При выполнении TRUNCATE не срабатывают никакие триггеры ON DELETE , которые могут быть настроены для таблиц. Однако при этом срабатывают триггеры ON TRUNCATE . Если триггеры ON TRUNCATE определены для любых из этих таблиц, то все триггеры BEFORE TRUNCATE срабатывают до того, как происходит опустошение, а все триггеры AFTER TRUNCATE срабатывают после того, как завершается опустошение последней таблицы и все последовательности сбрасываются. Триггеры срабатывают по порядку обработки таблиц (сначала для таблиц, перечисленных в команде, затем для тех, что затрагиваются каскадно).

Команда TRUNCATE небезопасна с точки зрения MVCC. После опустошения таблицы она будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до опустошения. За подробностями обратитесь к Разделу 13.5 .

TRUNCATE является надёжной транзакционной операцией в отношении данных в таблицах: опустошение будет безопасно отменено, если окружающая транзакция не будет зафиксирована.

С указанием RESTART IDENTITY подразумеваемые операции ALTER SEQUENCE RESTART также выполняются транзакционно; то есть, они будут отменены, если окружающая транзакция не будет зафиксирована. Это отличается от обычного поведения ALTER SEQUENCE RESTART . Учтите, что если до того, как транзакция отменится, будут выполнены какие-либо дополнительные операции с последовательностями, эффект этих операций также будет отменён, но не их влияние на значение currval() ; то есть после транзакции currval() продолжит возвращать последнее значение последовательности, полученное внутри прерванной транзакции, хотя сама последовательность уже может быть несогласованной с ним. Подобным образом обычно ведёт себя currval() после сбоя транзакции.

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

Примеры

Опустошение таблиц bigtable и fattable:

TRUNCATE bigtable, fattable;

Та же операция и сброс всех связанных генераторов последовательностей:

TRUNCATE bigtable, fattable RESTART IDENTITY;

Опустошение таблицы othertable и каскадная обработка всех таблиц, ссылающихся на othertable по ограничениям внешнего ключа:

TRUNCATE othertable CASCADE;

Совместимость

Стандарт SQL:2008 включает команду TRUNCATE с синтаксисом TRUNCATE TABLE имя_таблицы . Предложения CONTINUE IDENTITY / RESTART IDENTITY также описаны в стандарте, но с небольшими отличиями, хотя их назначение похоже. Поведение этой команды при параллельных операциях, согласно стандарту, отчасти определяются реализацией, так что приведённые выше замечания при необходимости следует учитывать и сопоставлять с другими реализациями.

In this syntax, you specify the table_name that you want to delete data after the TRUNCATE TABLE clause.

Some database systems such as and PostgreSQL allow you to skip the TABLE keyword so the TRUNCATE TABLE statement is as simple as follows:

TRUNCATE table_name;

When you issue the TRUNCATE TABLE statement, the database system deletes all rows from the table by deallocating the data pages allocated by the table. By doing this, the RDBMS can reduce the resources for logging and the number of locks that need to acquire.

To truncate multiple tables at a time, you can use a list of comma-separated table names after the TRUNCATE TABLE clause as follows:

TRUNCATE TABLE table_name1,table_name2,...;

Not all database systems support this form of the TRUNCATE TABLE statement. If you are using the one that does not, you must issue multiple TRUNCATE TABLE statements to truncate multiple tables.

SQL TRUNCATE TABLE vs. DELETE

Logically the TRUNCATE TABLE statement and the DELETE statement without the WHERE clause gives the same effect that removes all data from a table. However, they do have some differences:

  • When you use the DELETE statement, the database system logs the operations. And with some efforts, you can roll back the data that was deleted. However, when you use the TRUNCATE TABLE statement, you have no chance to roll back except you use it in a transaction that has not been committed.
  • To delete data from a table referenced by a foreign key constraint, you cannot use the TRUNCATE TABLE statement. In this case, you must use the DELETE statement instead.
  • The TRUNCATE TABLE statement does not fire the delete trigger if the table has the triggers associated with it.
  • Some database systems reset the value of an auto-increment column (or identity, sequence, etc.) to its starting value after you execute the TRUNCATE TABLE statement. It is not the case for the DELETE statement.
  • The DELETE statement with a WHERE clause deletes partial data from a table while the TRUNCATE TABLE statement always removes all data from the table.

SQL TRUNCATE TABLE examples

Let’s take a look at an example of truncating a table.

First, create a new table named big_table as follows:

CREATE TABLE big_table(

idINT AUTO_INCREMENT PRIMARY KEY ,

valINT

Second, execute the following statement as many times as you want to sample data into the big_table table:

INSERT INTO big_table(val)

VALUES

(RAND (100000));

Note that if you use a database system that supports stored procedure, you can put this statement inside a loop. For example, the following loads data into the big_table table with the number of rows specified by the num parameter.

DELIMITER $$

CREATE PROCEDURE load_big_table_data(IN numint )

BEGIN

DECLARE counterint default 0;

WHILE counter< numDO

INSERT INTO big_table(val)

Это учебное пособие Oracle объясняет, как использовать оператор Oracle TRUNCATE TABLE с синтаксисом, примерами и практическими упражнениями.

Описание

Оператор TRUNCATE TABLE используется для удаления всех записей из таблицы в Oracle. Он выполняет ту же функцию что и DELETE, только без условий WHERE.

Предупреждение: Если вы очистите таблицу с помощью оператора TRUNCATE TABLE , то ее откат невозможен.

Синтаксис

Синтаксис для оператора TRUNCATE TABLE в Oracle/PLSQL:

TRUNCATE TABLE table_name
[ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
[ DROP STORAGE | REUSE STORAGE ] ;

Параметры или аргументы

schema_name

Необязательный. Если указано, то это имя схемы к которой принадлежит таблица.

table_name

Таблица, которую вы хотите очистить.

PRESERVE MATERIALIZED VIEW LOG

Необязательный. Если указано, то materialized view log будет сохранено, когда таблица очищается. Это поведение по умолчанию.

PURGE MATERIALIZED VIEW LOG

Необязательный. Если указано, то materialized view log будет очищен, когда таблица очищается.

DROP STORAGE

Необязательный. Если указано, всё хранилище очищающихся строк будет высвобождено, за исключением пространства, которое было выделено в MINEXTENTS . Это поведение по умолчанию.

REUSE STORAGE

Необязательный. Если указано, всё хранилище очищающихся строк останется распределенным в таблице.

Пример

В Oracle очищение таблицы является быстрым способом, чтобы очистить записи из таблицы, если вам не нужно беспокоиться об откате. Одной из причин является то, что, когда таблица очищается, это не влияет на какие-либо из индексов, триггеров или зависимостей таблицы. Очищение таблицы также намного проще, чем удаление таблицы и ее воссоздания.

Рассмотрим пример того, как использовать оператор TABLE TRUNCATE в Oracle/PLSQL.

Например:

Oracle PL/SQL

TRUNCATE TABLE customers;

Оба эти примера приведут к тому, что все данные из таблицы customers удаляется. Основное различие между ними состоит в том, что вы можете выполнить откат DELETE если выберите, но вы не сможете выполнить откат оператора TRUNCATE TABLE .

This SQL tutorial explains how to use the SQL TRUNCATE TABLE statement with syntax and examples.

Description

The SQL TRUNCATE TABLE statement is used to remove all records from a table. It performs the same function as a DELETE statement without a WHERE clause.

Warning : If you truncate a table, the TRUNCATE TABLE statement can not be rolled back in some databases.

Syntax

The syntax for the TRUNCATE TABLE statement in SQL is:

TRUNCATE TABLE table_name;

Parameters or Arguments

table_name The table that you wish to truncate.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Example

You might choose to truncate a table instead of dropping the table and recreating it. Truncating a table can be faster and does not affect any of the table"s indexes, triggers, and dependencies. It is also a fast way to clear out the records from a table if you don"t need to worry about rolling back.

Let"s look at an example of how to use the TRUNCATE TABLE statement in SQL.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Enter the following TRUNCATE TABLE statement:

Try It TRUNCATE TABLE suppliers;

Then select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state

suppliers and remove all records from that table. It would be equivalent to the following DELETE statement in SQL:

Try It DELETE FROM suppliers;

Both of these statements would result in all data from the suppliers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you may not be able to roll back the TRUNCATE TABLE statement in all SQL databases.

If you need to prefix the table name with the database name, you can rewrite the TRUNCATE TABLE statement as follows:

TRUNCATE TABLE totn.suppliers;

This example would truncate the table called suppliers in the database called totn .