Oracle SQL*Plus Pocket Reference, 3rd Edition

Deleting Data

Use the DELETE statement to delete rows from a table.

Simple Deletes

A simple DELETE statement takes the following form:

DELETE FROM table_name
WHERE selection_criteria

All rows meeting the selection criteria will be deleted. For example, to delete the sixth-period Spanish I course, specify:

DELETE FROM course
WHERE course_name = 'Spanish I'
  AND period = 6;

Be careful with DELETE statements. If you omit the WHERE clause, you will delete all rows from your table.

Deleting All Rows (TRUNCATE)

You can delete all rows from a table by issuing a DELETE without a WHERE clause:

DELETE FROM course;

Deleting all rows like this exacts a price. The deletion of each row must be logged to the database redo log, and a copy of each row to be deleted must be written to a rollback segment (or to an undo tablespace) in case the transaction is rolled back. A more efficient mechanism for deleting all rows from a table is the TRUNCATE statement:

TRUNCATE TABLE course;

The TRUNCATE statement requires only one short entry in the database redo log and generates no rollback or undo data. As a result, it is faster to truncate a table than it is to delete all rows using a DELETE statement. Beware, however, that you cannot roll back a TRUNCATE statement. Once you issue it, all the data in your table is gone for good.

By default, TRUNCATE deallocates all extents assigned to the table. If you wish, you can save the extents for later use:

TRUNCATE TABLE course REUSE STORAGE;

This resets the table’s highwater mark ...