TRUNCATE

Description

TRUNCATE quickly removes all rows from a database table.

It has the same effect as an unqualified DELETE statement on a table, but TRUNCATE is faster because it does not scan the tables. This is most useful on large tables.

Precautions

• TRUNCATE TABLE has the same function as a DELETE statement without WHERE clause, where both delete all rows in a table.
• TRUNCATE TABLE uses less system and transaction log resources than DELETE.
  • DELETE deletes a row each time and records the deletion of each row in the transaction log.
  • TRUNCATE TABLE deletes data by releasing data pages storing the table data. It only records the releasing of the data page in the transaction log.
• The differences between TRUNCATE, DELETE, and DROP are as follows:
  • TRUNCATE TABLE deletes content, releases space, but does not delete definitions.
  • DELETE TABLE deletes content, but does not delete definitions nor release space.
  • DROP TABLE deletes content and definitions, and releases space.

Syntax

• Truncate table data.

TRUNCATE [ TABLE ] [ONLY] {table_name [ * ]} [, ... ]
    [CASCADE | RESTRICT][ PURGE ]};

• Truncate partition data.

ALTER TABLE { [ ONLY  ] table_name  
            | table_name *  
            | ONLY ( table_name )  } 
    TRUNCATE PARTITION { { ALL | partition_name [, ...] }  
                       | FOR (  partition_value  [, ...] )  } [ UPDATE GLOBAL INDEX ];

Parameters

• ONLY

  If ONLY is specified, only the specified table is truncated. If ONLY is not specified, the table and all its subtables (if any) are truncated. Currently, the syntax of ONLY with the * option is reserved, but the function is not supported.

• table_name

  Specifies the name (optionally schema-qualified) of the target table.

  Value range: an existing table name.

• CASCADE | RESTRICT
  • CASCADE: automatically deletes the objects (such as other views) that depend on the view.
  • RESTRICT: refuses to delete the view if any objects depend on it. This is the default action.
  

  This attribute is not supported when the version compatibility control parameter m_format_dev_version of an M-compatible database is set to 's1' or later.

• PURGE

  Removes table data which is moved to the recycle bin by default.

• { ALL | partition_name [, ...] }

  ALL: clears all partition data.

  partition_name: specifies a partition in the target partitioned table. Level-1 and level-2 partition names are supported.

  Value range: an existing partition name.

• partition_value

  Specifies the partition key value.

  You can use PARTITION FOR to uniquely identify a partition.

  Value range: value range of partition keys of the partition to be truncated.

  

  1. If a table is a foreign table of another table, running TRUNCATE to clear data will fail and an error will be reported.

  2. When the PARTITION FOR clause is used, the entire partition that partition_value corresponds to is truncated.

Examples

-- Create a schema.
m_db=# CREATE SCHEMA tpcds;

-- Create a table named tpcds.reason.
m_db=# CREATE TABLE tpcds.reason
(
  r_reason_sk      integer,
  r_reason_id      character(16),
  r_reason_desc    character(100)
);

-- Insert multiple records into the table.
m_db=# INSERT INTO tpcds.reason values(1,'AAAAAAAABAAAAAAA','reason 1'),(5,'AAAAAAAABAAAAAAA','reason 2'),(15,'AAAAAAAABAAAAAAA','reason 3'),(25,'AAAAAAAABAAAAAAA','reason 4'),(35,'AAAAAAAABAAAAAAA','reason 5'),(45,'AAAAAAAACAAAAAAA','reason 6'),(55,'AAAAAAAACAAAAAAA','reason 7');

-- Create a table.
m_db=# CREATE TABLE tpcds.reason_t1  LIKE tpcds.reason;

-- Truncate the tpcds.reason_t1 table.
m_db=# TRUNCATE TABLE tpcds.reason_t1;

-- Drop the table.
m_db=# DROP TABLE tpcds.reason_t1;

-- Create a partitioned table.
m_db=# 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.
m_db=# INSERT INTO tpcds.reason_p SELECT * FROM tpcds.reason;

-- Truncate the p_05_before partition.
m_db=# ALTER TABLE tpcds.reason_p TRUNCATE PARTITION p_05_before;

-- Truncate the p_15 partition.
m_db=# ALTER TABLE tpcds.reason_p TRUNCATE PARTITION for (15);

-- Truncate the partitioned table.
m_db=# TRUNCATE TABLE tpcds.reason_p;

-- Drop the table.
m_db=# DROP TABLE tpcds.reason_p;

-- Drop the table.
m_db=# DROP TABLE tpcds.reason;

-- Drop the schema.
m_db=# DROP SCHEMA tpcds;