AntDB-Oracle Compatibility Developer’s Manual P3–32

Deleting a Partition

Use the ALTER TABLE...DROP PARTITION command to delete the partition definition and the data stored in the partition.

Summary:

ALTER TABLE table_name DROP PARTITION partition_name; -- delete primary partition

ALTER TABLE table_name DROP SUBPARTITION partition_name;-- delete subpartition

argument:

table_name

The name of the partition table (optionally mode qualified).

partition_name

The name of the partition that needs to be deleted.

Example:

create table subpar_test1(
    id int,
    productID int,
    procuctType varchar(50)
    ) 
    partition by list(productID)
    subpartition by list(procuctType)
       (partition p1 values (1,2,3,4,5)
           (subpartition subp11 values('Bus','Car','Trunk'),
            subpartition subp12 values('Bycle','Trolley'),
            subpartition subp13 values('Ship','Yacht','Boat')
           ),
        partition p2 values (6,7,8,9,10)
           (subpartition subp21 values('Bus','Car','Trunk'),
            subpartition subp22 values('Bycle','Trolley'),
            subpartition subp23 values('Ship','Yacht','Boat')
           ),
        partition p3 values (11,12,13,14,15)
           (subpartition subp31 values('Bus','Car','Trunk'),
            subpartition subp32 values('Bycle','Trolley'),
            subpartition subp33 values('Ship','Yacht','Boat')
           )
       );
--Delete the primary partition
 alter table subpar_test1 drop partition p1;
--Delete the child partition
alter table subpar_test1 drop subpartition subp34;

Empty a Partition

Use the ALTER TABLE...TRUNCATE command to remove data from the specified partition, leaving the partition structure intact. You must have TRUNCATE privileges on the table to invoke the ALTER table...TRUNCATE partition.

Summary

ALTER TABLE table_name TRUNCATE PARTITION partition_name

ALTER TABLE table_name TRUNCATE SUBPARTITION partition_name

argument:

table_name

The name of the partition table (optionally mode qualified).

partition_name

The name of the partition that needs to be emptied.

Example:

create table subpar_test4(
    id int,
    entype varchar(50),
    dt date,
    pert number
    ) 
    partition by range(dt)
    subpartition by list(entype)
       (partition p1 values less than (to_date('2021-01-01','yyyy-mm-dd'))
           (subpartition subp11 values ('Solar','Wind','Hydropower'),
            subpartition subp12 values ('Geothermal','Tidal'),
            subpartition subp13 values ('Oil','Coal')
           ),
        partition p2 values less than (to_date('2022-01-01','yyyy-mm-dd'))
           (subpartition subp21 values ('Solar','Wind','Hydropower'),
            subpartition subp22 values ('Geothermal','Tidal'),
            subpartition subp23 values ('Oil','Coal')
           )
       );
insert into subpar_test4 values (1, to_date('2020-01-01','yyyy-mm-dd'), 'Wind', 0.1);
insert into subpar_test4 values (2, to_date('2020-02-25','yyyy-mm-dd'), 'Oil', 0.5);
insert into subpar_test4 values (3, to_date('2020-12-01','yyyy-mm-dd'), 'Geothermal', 0.1);
insert into subpar_test4 values (4, to_date('2021-01-01','yyyy-mm-dd'), 'Solar', 0.2);
insert into subpar_test4 values (5, to_date('2021-12-01','yyyy-mm-dd'), 'Geothermal', 0.05);
insert into subpar_test4 values (6, to_date('2021-04-01','yyyy-mm-dd'), 'Coal', 0.3);
insert into subpar_test4 values (7, to_date('2021-12-01','yyyy-mm-dd'), 'Oil', 0.3);

--Empty the data in the primary partition
alter table subpar_test4 truncate partition p2;
--Empty the data in the subpartition
alter table subpar_test4 truncate subpartition subp13;

Checking the structure of the subpar_test4 table, it still contains the p2 partition and subp13, but the partition data has been cleared.

postgres=# \d+ subpar_test4
                                 Partitioned table "public.subpar_test4"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 dt     | oracle.date           |           |          |         | plain    |              | 
 entype | character varying(50) |           |          |         | extended |              | 
 pert   | numeric               |           |          |         | main     |              | 
Partition key: RANGE (dt)
Partitions: p1 FOR VALUES FROM (MINVALUE) TO ('2021-01-01 00:00:00'), PARTITIONED,
            p2 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2022-01-01 00:00:00'), PARTITIONED
DISTRIBUTE BY HASH(id) TO NODE(dm1, dm2, dm3, dm4, dm5)
postgres=# \d+ p1
                                      Partitioned table "public.p1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 dt     | oracle.date           |           |          |         | plain    |              | 
 entype | character varying(50) |           |          |         | extended |              | 
 pert   | numeric               |           |          |         | main     |              | 
Partition of: subpar_test4 FOR VALUES FROM (MINVALUE) TO ('2021-01-01 00:00:00')
Partition constraint: ((dt IS NOT NULL) AND ((dt)::timestamp without time zone < '2021-01-01 00:00:00'::oracle.date))
Partition key: LIST (entype)
Partitions: subp11 FOR VALUES IN ('Solar', 'Wind', 'Hydropower'),
            subp12 FOR VALUES IN ('Geothermal', 'Tidal'),
            subp13 FOR VALUES IN ('Oil', 'Coal')
DISTRIBUTE BY HASH(id) TO NODE(dm1, dm2, dm3, dm4, dm5)
postgres=# select * from subpar_test4;
 ID |         DT          |   ENTYPE   | PERT 
----+---------------------+------------+------
  1 | 2020-01-01 00:00:00 | Wind       |  0.1
  3 | 2020-12-01 00:00:00 | Geothermal |  0.1
  2 | 2020-02-25 00:00:00 | Oil        |  0.5
(3 rows)