3.5.14 ALTER TABLE… TRUNCATE SUBPARTITION

Table of Contents Previous Next


3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.14 ALTER TABLE… TRUNCATE SUBPARTITION

Use the ALTER TABLETRUNCATE SUBPARTITION command to remove all of the data from the specified subpartition, leaving the subpartition structure intact. The syntax is:
ALTER TABLE table_name
TRUNCATE SUBPARTITION
subpartition_name
[{DROP|REUSE} STORAGE]
The ALTER TABLETRUNCATE SUBPARTITION command removes all data from a specified subpartition, leaving the subpartition structure intact.
ALTER TABLETRUNCATE SUBPARTITION will not cause ON DELETE triggers that might exist for the table to fire, but it will fire ON TRUNCATE triggers. If an ON TRUNCATE trigger is defined for the subpartition, all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation occurs.
You must have the TRUNCATE privilege on a table to invoke ALTER TABLETRUNCATE SUBPARTITION.
The DROP STORAGE and REUSE STORAGE clauses are included for compatibility only; the clauses are parsed and ignored.
The example that follows removes the data from a subpartition of the sales table. Use the following command to create the sales table:
(
PARTITION
"2011" VALUES LESS THAN('01-JAN-2012')
SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2011 VALUES ('US', 'CANADA')
PARTITION "2012" VALUES LESS THAN('01-JAN-2013')
SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2012 VALUES ('US', 'CANADA')
),
PARTITION
"2013" VALUES LESS THAN('01-JAN-2014')
SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
Populate the sales table with the command:
Querying the sales table shows that the rows have been distributed amongst the subpartitions:
To delete the contents of the 2012_americas partition, invoke the following command:
ALTER TABLE sales TRUNCATE SUBPARTITION "americas_2012";
Now, querying the sales table shows that the content of the americas_2012 partition has been removed:
While the rows have been removed, the structure of the 2012_americas partition is still intact:

3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.14 ALTER TABLE… TRUNCATE SUBPARTITION

Table of Contents Previous Next