Updated on 2024-06-11 GMT+08:00

How Do I Delete Table Data Efficiently?

Yes. TRUNCATE is more efficient than DELETE for deleting massive data.

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;