Data removal operations compared

/need to include purge operations? or is that folded into DELETE operations?/.

The following table summarizes differences between various data removal 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_PARTITIONS (...)`	High	Yes	No

Choosing the best operation

The following table can help you decide which operation is best for removing table data:

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 on logical sets of data.	`DROP_PARTITIONS`
Occasionally perform small deletes with the option to roll back or review history.	`DELETE`