Database Reference
In-Depth Information
Figure 18.23
DROP TABLE
Command Syntax.
Sometimes DBAs will need to clear a table completely. Using the
TRUNCATE command can be a better option than dropping and re-creat-
ing the same table. Dropping and re-creating a table implies using the
DROP TABLE and CREATE TABLE commands. Using the TRUNCATE
command will prevent you from having to re-create all indexes and con-
straints on the table.
18.5.1
Truncating Instead of Dropping Tables
Sometimes you need to remove all the rows in a table without disturbing
the table's structure. For example, you may have a table that is loaded with
rows from an external file of billing data at the beginning of each month's
billing cycle. The rows from last month's billing cycle must be removed
before inserting this month's data.
There are three ways to remove all the rows in a table: DELETE,
TRUNCATE, or DROP TABLE plus CREATE TABLE. The TRUN-
CATE command has the same effect as deleting all the rows in a table
except that it preserves table structure, unlike the DROP TABLE com-
mand. For large tables, the DELETE command is too slow. Figure 18.24
shows the syntax of the TRUNCATE command.
So let's compare between the TRUNCATE and DELETE commands
when completely clearing a table's rows. There are several differences
between using the TRUNCATE command or the DELETE command to
remove all rows in a table. These differences make the TRUNCATE com-
mand much faster but also irreversible.
TRUNCATE does not produce rollback entries. This means that you
cannot undo from a TRUNCATE command using the ROLLBACK
command. A DELETE command can be undone using rollback.
