How Do I Delete Table Data Efficiently?

Function

TRUNCATE quickly removes all rows from a table. It has the same effect as an unqualified DELETE but since it does not actually scan the table it is faster. This is most effective on large tables.

Functions

• TRUNCATE TABLE works like a DELETE statement with no WHERE clause, that is, emptying a table.
• TRUNCATE TABLE uses less system and transaction log resources.
  • DELETE deletes a row each time, and records each deletion in the transaction log.
  • TRUNCATE TABLE deletes all rows in a table by releasing the data page, and only records each releasing of the data page in the transaction log.
• TRUNCATE, DELETE, and DROP are different in that:
  • TRUNCATE TABLE deletes content, releases space, but does not delete definitions.
  • DELETE TABLE deletes content, but does not delete definitions or release space.
  • DROP TABLE deletes content and definitions, and releases space.

Examples

• Create a table.

  1	CREATE TABLE tpcds.reason_t1 AS TABLE tpcds.reason;

  Truncate the table.

  1	TRUNCATE TABLE tpcds.reason_t1;

  Delete the table.

  1	DROP TABLE tpcds.reason_t1;

• Create a partitioned table.

  1 2 3 4 5 6 7 8 9 10 11 12 13

  CREATE TABLE tpcds.reason_p ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) )PARTITION BY RANGE (r_reason_sk) ( partition p_05_before values less than (05), partition p_15 values less than (15), partition p_25 values less than (25), partition p_35 values less than (35), partition p_45_after values less than (MAXVALUE) );

  Insert data.

  1	INSERT INTO tpcds.reason_p SELECT * FROM tpcds.reason;

  Truncate the p_05_before partition.

  1	ALTER TABLE tpcds.reason_p TRUNCATE PARTITION p_05_before;

  Truncate the partition p_15 where 13 is located.

  1	ALTER TABLE tpcds.reason_p TRUNCATE PARTITION for (13);

  Truncate the partitioned table.

  1	TRUNCATE TABLE tpcds.reason_p;

  Delete the table.

  1	DROP TABLE tpcds.reason_p;