COMMIT THE DELETION

4. Commit the deletion:

To remove rows from a table, there are two options: the DELETE command and

the TRUNCATE command. DELETE is less drastic, in that a deletion can be rolled

back whereas a truncation cannot be. DELETE is also more controllable, in that it is

possible to choose which rows to delete, whereas a truncation always affects the whole

table. DELETE is, however, a lot slower and can place a lot of strain on the database.

TRUNCATE is virtually instantaneous and effortless.

TRUNCATE

The TRUNCATE command is not a DML command; it is a DDL command. The

difference is enormous. When DML commands affect data, they insert, update, and

delete rows as part of transactions. Transactions are defined later in this chapter, in

the section “Control Transactions.” For now, let it be said that a transaction can be

controlled, in the sense that the user has the choice of whether to make the work

done in a transaction permanent, or whether to reverse it. This is very useful but

forces the database to do additional work behind the scenes that the user is not aware

of. DDL commands are not user transactions (though within the database, they are in

fact implemented as transactions—but developers cannot control them), and there is

no choice about whether to make them permanent or to reverse them. Once executed,

they are done. However, in comparison to DML, they are very fast.

EXAM TIP Transactions, consisting of INSERT, UPDATE, and DELETE (or even

MERGE) commands, can be made permanent (with a COMMIT) or reversed

(with a ROLLBACK). A TRUNCATE command, like any other DDL command,

is immediately permanent: it can never be reversed.

From the user’s point of view, a truncation of a table is equivalent to executing a

DELETE of every row: a DELETE command without a WHERE clause. But whereas

a deletion may take some time (possibly hours, if there are many rows in the table), a

truncation will go through instantly. It makes no difference whether the table contains

one row or billions; a TRUNCATE will be virtually instantaneous. The table will still

exist, but it will be empty.