TRUNCATE TABLE-MaxCompute(MaxCompute)-阿里云帮助中心

Description

TRUNCATE TABLE removes all data from a table or specified partitions while preserving the table schema, permissions, and metadata. Unlike DROP TABLE, which deletes the table entirely, TRUNCATE TABLE keeps the table structure intact so data can be inserted immediately after. It is also significantly faster than DELETE because it does not scan or log individual row deletions.

Syntax

Truncate a non-partitioned table

TRUNCATE TABLE <table_name>;

Truncate specific partitions

TRUNCATE TABLE <table_name> PARTITION <partition_spec>[, PARTITION <partition_spec>...];

Truncate partitions using a filter condition

TRUNCATE TABLE <table_name> PARTITION <partition_filter_condition>;

Parameters

Parameter	Required	Description
`table_name`	Yes	The name of the table to truncate. For partitioned tables, this is the name of the partitioned table.
`partition_spec`	Yes (for partitioned tables without filter)	The partition specification. Format: `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. Specify multiple partitions by separating partition specifications with commas.
`partition_filter_condition`	Yes (when using filter)	The partition filter condition (case-insensitive). See Partition filter condition details below.

Partition spec components:

partition_col: The partition column name (case-insensitive).
partition_col_value: The partition value (case-sensitive).

Partition filter condition details

The partition_filter_condition parameter supports the following formats:

partition (<partition_col> <comparison_operator> <partition_col_value>) -- Compare a partition column directly.
partition (scalar(<partition_col>) <comparison_operator> <partition_col_value>) -- Apply a scalar function before comparison.
partition (<partition_filter_condition1> AND|OR <partition_filter_condition2>) -- Combine conditions with logical operators.
partition (NOT <partition_filter_condition>) -- Negate a condition.
partition (<partition_filter_condition1>)[, partition (<partition_filter_condition2>), ...] -- Separate multiple filter clauses with commas.

Filter condition components:

partition_col: The partition column name.
comparison_operator: A comparison operator (such as =, !=, >, <, LIKE, etc.).
partition_col_value: The comparison value or regular expression that matches the partition column data type.
scalar(): A scalar function that processes the partition column value before comparison.

Logical operators:

NOT: Returns the complement of a partition filter condition.
AND or OR: Combines multiple partition filter conditions.
Multiple partition filter clauses separated by commas are combined with OR logic.

Usage notes

TRUNCATE TABLE removes data but preserves the table schema and column structure. To remove the table entirely, use DROP TABLE. To remove specific rows based on a condition, use DELETE.
TRUNCATE TABLE is faster than DELETE without a WHERE clause because it does not scan rows individually. For large tables or partitions, use TRUNCATE TABLE when you need to remove all data.
MaxCompute supports partition-level truncation with filter conditions, enabling bulk cleanup of partitioned data without specifying each partition individually.
TRUNCATE TABLE is a DDL operation. The data removal cannot be undone once the statement executes successfully.

Limits

TRUNCATE TABLE applies only to tables. It cannot be used on views or materialized views.
For partitioned tables, a PARTITION clause is required. Running TRUNCATE TABLE on a partitioned table without specifying partitions returns an error.
The specified partition must exist. If a partition does not exist, the statement returns an error.

Examples

Truncate a non-partitioned table

-- Remove all data from the sales table
TRUNCATE TABLE sales;

Truncate a non-partitioned table (end-to-end)

The following example shows the full lifecycle: create a table, insert data, truncate it, and verify the result.

-- Create a simple table.
CREATE TABLE IF NOT EXISTS truncate_demo (id BIGINT, name STRING);

-- Insert sample data.
INSERT INTO TABLE truncate_demo VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');

-- Verify that the data exists.
SELECT * FROM truncate_demo;

Expected output:

+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |
+----+-------+

-- Truncate the table to remove all data.
TRUNCATE TABLE truncate_demo;

-- Verify that the table is now empty.
SELECT * FROM truncate_demo;

Expected output:

+----+------+
| id | name |
+----+------+
+----+------+

Truncate a single partition

-- Remove sales records for December 2013 in Hangzhou region
TRUNCATE TABLE sale_detail PARTITION (sale_date='201312', region='hangzhou');

Truncate multiple partitions

-- Remove sales records for December 2013 in both Hangzhou and Shanghai regions
TRUNCATE TABLE sale_detail
  PARTITION (sale_date='201312', region='hangzhou'),
  PARTITION (sale_date='201312', region='shanghai');

Truncate partitions using a filter condition

-- Remove all partitions in Hangzhou region where sale_date starts with '2013'
TRUNCATE TABLE sale_detail PARTITION (sale_date LIKE '2013%' AND region='hangzhou');

Truncate partitions using a scalar function

-- Remove partitions using a scalar function to process the partition column
TRUNCATE TABLE sale_detail PARTITION (scalar(sale_date) > '201300');

Truncate partitions using the NOT operator

-- Remove all partitions except those in Hangzhou region
TRUNCATE TABLE sale_detail PARTITION (NOT region='hangzhou');

Truncate partitions using OR logic

-- Remove partitions matching multiple conditions (OR logic)
TRUNCATE TABLE sale_detail
  PARTITION (region='hangzhou'),
  PARTITION (region='shanghai');

Related commands

CREATE TABLE: Create non-partitioned tables, partitioned tables, external tables, or clustered tables
ALTER TABLE: Modify table operations
DROP TABLE: Delete partitioned or non-partitioned tables
DESC TABLE/VIEW: View information about MaxCompute internal tables, views, materialized views, external tables, clustered tables, or transactional tables
SHOW: View SQL Data Definition Language (DDL) statements, list all tables and views in a project, or list all partitions in a table