ALTER TABLE...TRUNCATE SUBPARTITION - PolarDB - Alibaba Cloud Documentation Center

You can use the ALTER TABLE...TRUNCATE SUBPARTITION command to remove all data from a specified subpartition, leaving the subpartition structure intact.

Overview

Syntax:

ALTER TABLE table_name 
  TRUNCATE SUBPARTITION subpartition_name 
  [{DROP|REUSE} STORAGE]

Description

The ALTER TABLE...TRUNCATE SUBPARTITION command removes all data from a specified subpartition, leaving the subpartition structure intact.

The ALTER TABLE...TRUNCATE SUBPARTITION command will not fire any ON DELETE triggers that may exist for the table. However, the command will fire ON TRUNCATE triggers. If an ON TRUNCATE trigger is defined for the subpartition, all BEFORE TRUNCATE triggers are fired before any truncation occurs, and all AFTER TRUNCATE triggers are fired after the last truncation is performed.

You must have the TRUNCATE permission on a table to run ALTER TABLE...TRUNCATE SUBPARTITION.

Parameters


Parameter	Description
table_name	The name (optionally schema-qualified) of the partitioned table.
subpartition_name	The name of the subpartition to be truncated.

Note DROP STORAGE and REUSE STORAGE are only included for compatibility. These clauses are parsed and ignored.

Example - empty a subpartition

The following example removes the data from a subpartition of the sales table. Run the following command to create the sales table:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date) SUBPARTITION BY LIST (country)
(
  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-2015')
  (
    SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
    SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
    SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
  )
);

Run the following command to add values to the sales table:

INSERT INTO sales VALUES
(10, '4519b', 'FRANCE', '17-Jan-2011', '45000'),
(20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
(40, '9519b', 'US', '12-Apr-2012', '145000'),
(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
(40, '4577b', 'US', '11-Nov-2012', '25000'),
(30, '7588b', 'CANADA', '14-Dec-2011', '50000'),
(30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
(40, '3788a', 'US', '12-May-2011', '4950'),
(20, '3788a', 'US', '04-Apr-2012', '37500'),
(40, '4577b', 'INDIA', '11-Jun-2011', '25000'),
(10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
(20, '4519b', 'INDIA', '2-Dec-2012', '5090');

Querying the sales table shows that the rows have been distributed among the subpartitions:

acctg=# SELECT tableoid::regclass, * FROM sales;
      tableoid      | dept_no| part_no| country  |        date       |amount 
--------------------+--------+--------+----------+-------------------+-------
 sales_2011_europe  |      10| 4519b  | FRANCE   | 17-JAN-11 00:00:00|  45000
 sales_2011_asia    |      40| 4577b  | INDIA    | 11-JUN-11 00:00:00|  25000
 sales_2011_americas|      30| 7588b  | CANADA   | 14-DEC-11 00:00:00|  50000
 sales_2011_americas|      40| 3788a  | US       | 12-MAY-11 00:00:00|   4950
 sales_2012_europe  |      10| 9519b  | ITALY    | 07-JUL-12 00:00:00|  15000
 sales_2012_asia    |      20| 3788a  | INDIA    | 01-MAR-12 00:00:00|  75000
 sales_2012_asia    |      20| 3788a  | PAKISTAN | 04-JUN-12 00:00:00|  37500
 sales_2012_asia    |      20| 4519b  | INDIA    | 02-DEC-12 00:00:00|   5090
 sales_2012_americas|      40| 9519b  | US       | 12-APR-12 00:00:00| 145000
 sales_2012_americas|      40| 4577b  | US       | 11-NOV-12 00:00:00| 25000
 sales_2012_americas|      30| 4519b  | CANADA   | 08-APR-12 00:00:00| 120000
 sales_2012_americas|      20| 3788a  | US       | 04-APR-12 00:00:00|  37500
(12 rows)

To delete the content of the 2012_americas partition, run the following command:

ALTER TABLE sales TRUNCATE SUBPARTITION "americas_2012";

Querying the sales table shows that the content of the americas_2012 partition has been removed:

acctg=# SELECT tableoid::regclass, * FROM sales;
      tableoid      | dept_no|part_no| country  |        date        | amount 
--------------------+--------+-------+----------+--------------------+-------
 sales_2011_europe  |      10| 4519b | FRANCE   | 17-JAN-11 00:00:00 |  45000
 sales_2011_asia    |      40| 4577b | INDIA    | 11-JUN-11 00:00:00 |  25000
 sales_2011_americas|      30| 7588b | CANADA   | 14-DEC-11 00:00:00 |  50000
 sales_2011_americas|      40| 3788a | US       | 12-MAY-11 00:00:00 |   4950
 sales_2012_europe  |      10| 9519b | ITALY    | 07-JUL-12 00:00:00 |  15000
 sales_2012_asia    |      20| 3788a | INDIA    | 01-MAR-12 00:00:00 |  75000
 sales_2012_asia    |      20| 3788a | PAKISTAN | 04-JUN-12 00:00:00 |  37500
 sales_2012_asia    |      20| 4519b | INDIA    | 02-DEC-12 00:00:00 |   5090
(8 rows)

Although the rows have been removed, the structure of the 2012_americas partition is still intact:

acctg=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS; 
 subpartition_name |     high_value      
-------------------+---------------------
 2013_europe       | 'ITALY', 'FRANCE'   
 2012_europe       | 'ITALY', 'FRANCE'   
 2011_europe       | 'ITALY', 'FRANCE'   
 2013_asia         | 'PAKISTAN', 'INDIA' 
 2012_asia         | 'PAKISTAN', 'INDIA' 
 2011_asia         | 'PAKISTAN', 'INDIA' 
 2013_americas     | 'US', 'CANADA'      
 2012_americas     | 'US', 'CANADA'      
 2011_americas     | 'US', 'CANADA'      
(9 
rows)