SQL for Beginners (Part 10) : The DELETE and TRUNCATE TABLE Statements
This is the tenth part of a series of articles showing the basics of SQL. In this article we take a look at the DELETE and TRUNCATE TABLE Statements.
Setup
You can perform all these queries online for free using SQL Fiddle.
The examples in this article require the following tables to be present.
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the «$ORACLE_HOME/rdbms/admin/utlsampl.sql» script.
COMMIT and ROLLBACK
All Data Manipulation Language (DML) changes are done as part of a transaction. They are not permanent until they are confirmed using the COMMIT statement. Once committed, the only way you can revert a change is to issue a new DML statement to alter the data. Several statements can be grouped together to form a single transaction.
Data Definition Language (DDL) commands perform an implicit commit, which also confirms all outstanding DML changes in the current session.
If you decide you don’t want to keep some uncommitted changes, you can throw them away using using the ROLLBACK statement. Many of the examples in this article will issue ROLLBACK statements after the test, to revert the data to its original state.
Some tools and programming languages default to auto-commit, so they automatically issue a COMMIT statement after every DML statement they process. Don’t let this fool you into thinking this is default behaviour. It is not.
Basic DELETE
The DELETE statement is used to remove rows from the table. Without a WHERE clause, all rows in the table are deleted by a single statement.
The following example deletes all the rows from the EMPLOYEES table, then issues a ROLLBACK to cancel the deletion.
The WHERE clause allows you to limit the rows to be deleted.
DELETE via View
It’s possible to delete from the base table associated with a view. There are some restrictions associated with this, but they are a little out of scope for a beginner level article. In the following example, we create a simple view on the EMPLOYEES table, then delete via it.
You won’t see it very often, but you can also delete via inline views. This can be used to control the number of rows deleted, rather than using a filter in the WHERE clause of the DELETE statement itself.
0 Rows Deleted
A deletion of zero rows is a valid deletion, and does not result in an error. This can prove rather confusing to beginners.
As a result, you can’t test for failure to delete rows using the NO_DATA_FOUND exception in PL/SQL, as it doesn’t get raised.
Instead, you must manually test for the number of rows deleted using SQL%ROWCOUNT .
TRUNCATE TABLE
If you want to remove all the rows from a table, the TRUNCATE TABLE statement is a lot more efficient than the DELETE statement. The TRUNCATE TABLE statement is a DDL command, so it includes an implicit COMMIT , so there is no way to issue a ROLLBACK if you decide you didn’t want to remove the rows.
In the following example, we check the number of rows in the table, issue the TRUNCATE TABLE statement, immediately ROLLBACK and check the number of rows in the table again. You will see from the output, the ROLLBACK does not cancel the TRUNCATE TABLE statement.
The TRUNCATE TABLE statement can remove the storage associated with the table, or leave it to be reused later.
TRUNCATE TABLE
Оператор TRUNCATE TABLE используется для удаления всех записей из таблицы в Oracle. Он выполняет ту же функцию что и DELETE, только без условий WHERE.
Предупреждение: Если вы очистите таблицу с помощью оператора TRUNCATE TABLE, то ее откат невозможен.
Синтаксис
Синтаксис для оператора TRUNCATE TABLE в Oracle/PLSQL:
Параметры или аргументы
Необязательный. Если указано, то это имя схемы к которой принадлежит таблица.
Таблица, которую вы хотите очистить.
PRESERVE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет сохранено, когда таблица очищается. Это поведение по умолчанию.
PURGE MATERIALIZED VIEW LOG
Необязательный. Если указано, то materialized view log будет очищен, когда таблица очищается.
Необязательный. Если указано, всё хранилище очищающихся строк будет высвобождено, за исключением пространства, которое было выделено в MINEXTENTS . Это поведение по умолчанию.
Необязательный. Если указано, всё хранилище очищающихся строк останется распределенным в таблице.
Пример
В Oracle очищение таблицы является быстрым способом, чтобы очистить записи из таблицы, если вам не нужно беспокоиться об откате. Одной из причин является то, что, когда таблица очищается, это не влияет на какие-либо из индексов, триггеров или зависимостей таблицы. Очищение таблицы также намного проще, чем удаление таблицы и ее воссоздания.
Рассмотрим пример того, как использовать оператор TABLE TRUNCATE в Oracle/PLSQL.
Oracle как очистить таблицу
You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process
Grant object privileges on the table
Create the indexes, integrity constraints, and triggers on the table
Specify the storage parameters of the table
Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
DELETE and DROP TABLE for information on other ways of removing data from a table
TRUNCATE CLUSTER for information on truncating a cluster
To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.
To specify the CASCADE clause, all affected child tables must be in your schema or you must have the DROP ANY TABLE system privilege.
You can truncate a private temporary table with the existing TRUNCATE TABLE command. Truncating a private temporary table will not commit and existing transaction. This applies to both transaction-specific and session-specific private temporary tables. Note that a truncated private temporary table will not go into the RECYCLEBIN .
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema , then Oracle Database assumes the table is in your own schema.
You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.
Oracle Database changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.
Oracle Database also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table : range and hash partitions of local indexes and subpartitions of local indexes.
If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment. This operation is equivalent to creating a new segment for the index. Therefore, at the end of the truncate operation, the indexes are once again USABLE .
For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.
If a regular or index-organized table contains LOB columns, then all LOB data and LOB index segments are truncated.
If table is partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
When you truncate a table, Oracle Database automatically removes all data in the table’s indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log. If this direct-path INSERT information is removed, then an incremental refresh of the materialized view may lose data.
All cursors are invalidated.
Restrictions on Truncating Tables
This statement is subject to the following restrictions:
You cannot roll back a TRUNCATE TABLE statement.
You cannot flash back to the state of the table before the truncate operation.
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
If a domain index is defined on table , then neither the index nor any index partitions can be marked IN_PROGRESS .
You cannot truncate the parent table of a reference-partitioned table. You must first drop the reference-partitioned child table.
You cannot truncate a duplicated table.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export or import without affecting the ability of primary key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary key materialized views, the materialized view log must record primary key information.
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Specify PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.
Specify PURGE if any materialized view log should be purged when the master table is truncated.
Oracle Database Administrator’s Guide for more information about materialized view logs and the TRUNCATE statement
The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause, DROP ALL STORAGE clause, and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.
Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.
DROP ALL STORAGE
Specify DROP ALL STORAGE to deallocate all space from the deleted rows from the table, including the space allocated by the MINEXTENTS parameter. All segments for the table, as well as all segments for its dependent objects, will be deallocated.
Restrictions on DROP ALL STORAGE
This clause is subject to the same restrictions as described in «Restrictions on Deferred Segment Creation» .
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.
This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future.
This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.
If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
If you specify CASCADE , then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.
Truncating a Table: Example
The following statement removes all rows from a hypothetical copy of the sample table hr.employees and returns the freed space to the tablespace containing employees :
The preceding statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.
Preserving Materialized View Logs After Truncate: Example
The following statements are examples of TRUNCATE statements that preserve materialized view logs:
Oracle TRUNCATE TABLE
Summary: in this tutorial, you will learn how to use the Oracle TRUNCATE TABLE statement to delete all data from a table faster and more efficiently.
Introduction to Oracle TRUNCATE TABLE statement
When you want to delete all data from a table, you use the DELETE statement without the WHERE clause as follows:
For a table with a small number of rows, the DELETE statement does a good job. However, when you have a table with a large number of rows, using the DELETE statement to remove all data is not efficient.
Oracle introduced the TRUNCATE TABLE statement that allows you to delete all rows from a big table.
The following illustrates the syntax of the Oracle TRUNCATE TABLE statement:
By default, to remove all rows from a table, you specify the name of the table that you want to truncate in the TRUNCATE TABLE clause:
In this case, because we don’t specify the schema name explicitly, Oracle assumes that we truncate the table from our own schema.
If a table has relationships with other tables via the foreign key constraints, you need to use the CASCADE clause:
In this case, the TRUNCATE TABLE CASCADE statement deletes all rows from the table_name , and recursively truncates down the associated tables in the chain.
Note that the TRUNCATE TABLE CASCADE statement requires the foreign key constraints defined with the ON DELETE CASCADE clause to work.
The MATERIALIZED VIEW LOG clause allows you to specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. By default, the material view log is preserved.
The STORAGE clause allows you to choose either drop or reuse storage freed by the truncated rows and associated indexes if any. By default, the storage is dropped.
Oracle TRUNCATE TABLE examples
Let’s look at some examples of using the TRUNCATE TABLE statement.
A) Oracle TRUNCATE TABLE simple example
The following statement creates a table named customers_copy and copies data from the customers table in the sample database:
To delete all rows from the customers_copy table you use the following TRUNCATE TABLE statement:
B) Oracle TRUNCATE TABLE CASCADE example
First, let’s create quotations and quotation_items tables for the demonstration:
Next, insert some rows into these two tables:
Then, truncate the quotation table:
The statement failed and Oracle returned the following error:
To fix this, you add the CASCADE clause to the TRUNCATE TABLE statement above:
This statement deleted data from not only quotations table but also quotation_items table.
Finally, verify whether the data from both quotations and quotation_items are deleted or not:
Notice that if we did not specify the ON DELETE CASCADE for the fk_quotation constraint, the TRUNCATE TABLE CASCADE statement above would fail.
In this tutorial, you have learned how to use Oracle TRUNCATE TABLE statement to delete all data from a table faster and more efficiently.