Removing Table Data

Vertica provides several ways to remove data from a table:

Delete operation Description
DROP TABLE Permanently removes a table and its definition, optionally removes associated views and projections.
DELETE

Marks rows with delete vectors and stores them so data can be rolled back to a previous epoch. The data must be purged to reclaim disk space. See Purging Deleted Data.
TRUNCATE TABLE

Removes all storage and history associated with a table. The table structure is preserved for future use. The results of this command cannot be rolled back.

DROP_PARTITION

Removes one partition from a partitioned table. Each partition contains a related subset of data in the table. Partitioned data can be dropped efficiently, and provides query performance benefits. See Using Table Partitions.

Delete Operations Compared

The following table summarizes differences between various delete operations.

Operations and options Performance Auto commits Saves history 
DELETE FROM table

Normal

No

Yes

 
DELETE FROM temp‑table

High

No

No

 
DELETE FROM table where-clause

Normal

No

Yes

 
DELETE FROM temp‑table where-clause

Normal

No

Yes

 
DELETE FROM temp‑table where-clause ON COMMIT PRESERVE
 ROWS

Normal

No

Yes

 
DELETE FROM temp‑table where-clause ON COMMIT DELETE 
 ROWS

High

Yes

No

 
DROP table

High

Yes

No

 
TRUNCATE table

High

Yes

No

 
TRUNCATE temp‑table

High

Yes

No

 
SELECT DROP_PARTITION (...)

High

Yes

No

Choosing the Best Delete Operation

The following table can help you decide which delete operation is best:

If you want to... Use...
Delete both table data and definitions and start from scratch. DROP TABLE
Quickly drop data while preserving table definitions, and reload data. TRUNCATE TABLE
Regularly perform bulk delete operations. DROP_PARTITION

Perform occasional small deletes or updates with the option to roll back or review history.

See also: Best Practices for DELETE and UPDATE.

 DELETE