Manage a subpartitioned table group

After creating a subpartitioned table group, you can add subpartitioned tables to the group and perform partition management operations on the table group.

Support for partition management operations

Add a partition or subpartition

Drop a partition or subpartition

Notice

If you drop or add a partition or subpartition from or to a table group, the same operation is performed on the corresponding partition in the tables of the table group. If the tables in the table group have a global index, you need to add the UPDATE GLOBAL INDEXES keyword in the ALTER TABLEGROUP statement. If the UPDATE GLOBAL INDEXES keyword is not added, the global index on the partitioned tables of the table group will become unavailable.

Add a partition to a table group

After adding a subpartitioned table to a table group, you can add partitions to the table group. When you add partitions, you must also add subpartitions.

Syntax

ALTER TABLEGROUP tablegroup_name ADD PARTITION partition_option;

partition_option:
  {partition_name VALUES LESS THAN range_partition_expr (tg_subpartition_option)}
 |{partition_name VALUES list_partition_expr (tg_subpartition_option)}

tg_subpartition_option:
   SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...
 | SUBPARTITION subpartition_name VALUES list_partition_expr, ....
 | SUBPARTITION subpartition_name, ....

Parameters:

• tablegroup_name: the table group name.

• partition_option: the partition option.

• partition_name: the partition name.

• tg_subpartition_option: the subpartition option.

Examples

• Add a partition to a RANGE-RANGE-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_rr ADD PARTITION p3 VALUES LESS THAN(400)
         (SUBPARTITION sp7 VALUES LESS THAN (1000),
          SUBPARTITION sp8 VALUES LESS THAN (2000)
         );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE-LIST-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_rl ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
         (SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
         );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE-HASH-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_rh ADD PARTITION p2 VALUES LESS THAN (2022)
         (SUBPARTITION p2sp1,
          SUBPARTITION p2sp2
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST-RANGE-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_lr ADD PARTITION p2 VALUES ('D')
         (SUBPARTITION p1_nw_low VALUES LESS THAN(100),
          SUBPARTITION p1_nw_average VALUES LESS THAN(200),
          SUBPARTITION p1_nw_high VALUES LESS THAN (300)
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST-LIST-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_ll ADD PARTITION p2 VALUES ('E')
         (SUBPARTITION po_sc_bad VALUES ('01'),
          SUBPARTITION po_sc_average VALUES ('04'),
          SUBPARTITION po_sc_good VALUES ('07')
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST-HASH-subpartitioned table group.

  obclient>ALTER TABLEGROUP tg2_f_lh ADD PARTITION p_new VALUES  ('CN','JP')
         (SUBPARTITION sp5,
          SUBPARTITION sp6
         );
  

Add a subpartition to a table group

After adding a subpartitioned table to a table group, you can add subpartitions to the table group.

Syntax

ALTER TABLEGROUP tablegroup_name MODIFY PARTITION partition_name ADD SUBPARTITION tg_subpartition_option;

tg_subpartition_option:
  {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

Parameters:

• tablegroup_name: the table group name.

• partition_name: the partition name.

• tg_subpartition_option: the subpartition option.

Examples

• Add subpartitions sp7 and sp8 to the partition p2 of the RANGE-RANGE-subpartitioned table group tg2_f_rr.

  obclient> ALTER TABLEGROUP tg2_f_rr MODIFY PARTITION p2
         ADD SUBPARTITION sp7 VALUES LESS THAN(3000),
             SUBPARTITION sp8 VALUES LESS THAN(4000);
  Query OK, 0 rows affected
  

• Add subpartitions sp5 and sp6 to the partition p1 of the RANGE-LIST-subpartitioned table group tg2_f_rl.

  obclient> ALTER TABLEGROUP tg2_f_rl MODIFY PARTITION p1
         ADD SUBPARTITION sp5 VALUES ('04'),
             SUBPARTITION sp6 VALUES ('05');
  Query OK, 0 rows affected
  

• Add subpartitions sp5 and sp6 to the partition p1 of the LIST-RANGE-subpartitioned table group tg2_f_lr.

  obclient> ALTER TABLEGROUP tg2_f_lr MODIFY PARTITION p1
         ADD SUBPARTITION sp5 VALUES LESS THAN (400),
             SUBPARTITION sp6 VALUES LESS THAN (500);
  Query OK, 0 rows affected
  

• Add the subpartition sp6 to the partition p1 of the LIST-LIST-subpartitioned table group tg2_f_lr.

  obclient> ALTER TABLEGROUP tg2_f_ll MODIFY PARTITION p1
         ADD SUBPARTITION sp6 VALUES ('D');
  Query OK, 0 rows affected
  

Drop a partition from a table group

You cannot separately manage partitions on tables in a table group. Instead, you must manage the partitions by using the table group in a unified manner. You can drop a partition from a table group. Before dropping the partition, ensure that you have the ALTER TABLE privilege on the table group.

Syntax

ALTER TABLEGROUP tablegroup_name
DROP PARTITION partition_name[,partition_name]... [UPDATE GLOBAL INDEXES];

Parameters:

• tablegroup_name: the table group name.

• partition_name: the partition name.

• UPDATE GLOBAL INDEXES: specifies whether to maintain global indexes. This parameter is optional.

Examples

• Drop partitions p1 and p2 from the RANGE-RANGE-subpartitioned table group tg2_f_rr.

  obclient> ALTER TABLEGROUP tg2_f_rr DROP PARTITION p1,p2;
  Query OK, 0 rows affected
  

• Drop the partition p2 from the RANGE-HASH-subpartitioned table group tg2_f_rh.

  obclient> ALTER TABLEGROUP tg2_f_rh DROP PARTITION p2;
  Query OK, 0 rows affected
  

Drop a subpartition from a table group

After adding a subpartitioned table to a table group, you can drop a subpartition from the table group. If you want to drop multiple subpartitions at a time, they must belong to the same partition.

Syntax

ALTER TABLEGROUP tablegroup_name
DROP SUBPARTITION subpartition_name[, subpartition_name]... [UPDATE GLOBAL INDEXES];

Parameters:

• tablegroup_name: the table group name.

• subpartition_name: the subpartition name.

• UPDATE GLOBAL INDEXES: specifies whether to maintain global indexes. This parameter is optional.

Example

Drop subpartitions sp1 and sp2 from the RANGE-RANGE-subpartitioned table group tg2_f_rr.

obclient> ALTER TABLEGROUP tg2_f_rr DROP SUBPARTITION sp1,sp2;
Query OK, 0 rows affected