ApsaraDB for OceanBase:Modify, drop, and add partitions

Syntax

alter_table_partitioning:
{ modify_table_default_attrs
| alter_automatic_partitioning
| alter_interval_partitioning
| set_subpartition_template
| modify_table_partition
| modify_table_subpartition
| move_table_partition
| move_table_subpartition
| add_external_partition_attrs
| add_table_partition
| coalesce_table_partition
| drop_external_partition_attrs
| drop_table_partition
| drop_table_subpartition
| rename_partition_subpart
| truncate_partition_subpart
| split_table_partition
| split_table_subpartition
| merge_table_partitions
| merge_table_subpartitions
| exchange_partition_subpart
}

modify_table_default_attrs:
MODIFY DEFAULT ATTRIBUTES
   [ FOR partition_extended_name ]
   [ DEFAULT DIRECTORY directory ]
   [ deferred_segment_creation ]
   [ read_only_clause ]
   [ indexing_clause ]
   [ segment_attributes_clause ]
   [ table_compression ]
   [ inmemory_clause ]
   [ PCTTHRESHOLD integer ]
   [ prefix_compression ]
   [ alter_overflow_clause ]
   [ { LOB (LOB_item) | VARRAY varray } (LOB_parameters) ]...

alter_automatic_partitioning:
{ SET PARTITIONING { AUTOMATIC | MANUAL }
| SET STORE IN ( tablespace [, tablespace ]... )
}

alter_interval_partitioning:
{ SET INTERVAL ( [expr] )
| SET STORE IN ( tablespace [, tablespace]... )
}

set_subpartition_template:
SET SUBPARTITION TEMPLATE
   { ( range_subpartition_desc [, range_subpartition_desc]... )
   | ( list_subpartition_desc [, list_subpartition_desc]... )
   | ( individual_hash_subparts [, individual_hash_subparts]... )
   | ()
   | hash_subpartition_quantity
   }
modify_table_partition:
{ modify_range_partition
| modify_hash_partition
| modify_list_partition
}

modify_table_subpartition:
MODIFY subpartition_extended_name
{ allocate_extent_clause
| deallocate_unused_cluse
| shrink_clause
| { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| { ADD | DROP } VALUES ( list_values )
| read_only_clause
| indexing_clause
}

move_table_partition:
MOVE partition_extended_name
   [ MAPPING TABLE ]
   [ table_partition_description ]
   [ filter_condition]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]
   [ ONLINE ]

move_table_subpartition:
MOVE subpartition_extended_name [ indexing_clause ]
     [ partitioning_storage_clause ]
     [ update_index_clauses ]
     [ filter_condition ]
     [ parallel_clause ]
     [ allow_disallow_clustering ]
     [ ONLINE ]

add_external_partition_attrs:
ADD EXTERNAL PARTITION ATTRIBUTES external_table_clause 
 [ REJECT LIMIT ]pre

add_table_partition:
ADD {
PARTITION [ partition ] add_range_partition_clause
  [, PARTITION [ partition ] add_range_partition_clause ]...
| PARTITION [ partition ] add_list_partition_clause
  [, PARTITION [ partition ] add_list_partition_clause ]...
| PARTITION [ partition ] add_system_partition_clause
  [, PARTITION [ partition ] add_system_partition_clause ]...
  [ BEFORE { partition_name | partition_number } ]
| PARTITION [ partition ] add_hash_partition_clause
} [ dependent_tables_clause ]

coalesce_table_partition:
COALESCE PARTITION
  [ update_index_clauses ]
  [ parallel_clause ]
  [ allow_disallow_clustering ]

drop_external_partition_attrs:
DROP EXTERNAL PARTITION ATTRIBUTES

drop_table_partition:
DROP partition_extended_names
  [ update_index_clauses [ parallel_clause ] ]

drop_table_subpartition:
DROP subpartition_extended_names
  [ update_index_clauses [ parallel_clause ] ]

rename_partition_subpart:
RENAME { partition_extended_name
       | subpartition_extended_name
       } TO new_name

truncate_partition_subpart:
TRUNCATE { partition_extended_names | subpartition_extended_names }
   [ { DROP [ ALL ] | REUSE } STORAGE ]
   [ update_index_clauses [ parallel_clause ] ] [ CASCADE ]

split_table_partition:
SPLIT partition_extended_name
  { AT (literal [, literal]... )
    [ INTO ( range_partition_desc, range_partition_desc ) ]
  | VALUES ( list_values )
    [ INTO ( list_partition_desc, list_partition_desc ) ]
  | INTO ( { range_partition_desc [, range_partition_desc ]...
           | list_partition_desc [, list_partition_desc ]... }
         , partition_spec )
  } [ split_nested_table_part ]
    [ filter_condition ]
    [ dependent_tables_clause ]
    [ update_index_clauses ]
    [ parallel_clause ]
    [ allow_disallow_clustering ]
    [ ONLINE ]

split_table_subpartition:
SPLIT subpartition_extended_name
  { AT ( literal [, literal]... )
    [ INTO ( range_subpartition_desc, range_subpartition_desc ) ]
  | VALUES ( list_values )
    [ INTO ( list_subpartition_desc, list_subpartition_desc ) ]
  | INTO ( { range_subpartition_desc [, range_subpartition_desc ]...
           | list_subpartition_desc [, list_subpartition_desc ]... }
         , subpartition_spec )
  } [ filter_condition ]
    [ dependent_tables_clause ]
    [ update_index_clauses ]
    [ parallel_clause ]
    [ allow_disallow_clustering ]
    [ ONLINE ]

merge_table_partitions:
MERGE PARTITIONS partition_or_key_value
   { , partition_or_key_value [, partition_or_key_value ]...
   | TO partition_or_key_value }
   [ INTO partition_spec ]
   [ filter_condition ]
   [ dependent_tables_clause ]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]

merge_table_subpartitions:
MERGE SUBPARTITIONS subpartition_or_key_value
   { , subpartition_or_key_value [, subpartition_or_key_value ]...
   | TO subpartition_or_key_value }
   [ INTO { range_subpartition_desc 
          | list_subpartition_desc
          }
   ]
   [ filter_condition ]
   [ dependent_tables_clause ]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]

exchange_partition_subpart:
EXCHANGE { partition_extended_name
         | subpartition_extended_name
         }
   WITH TABLE [ schema. ] table
   [ { INCLUDING | EXCLUDING } INDEXES ]
   [(WITH | WITHOUT) VALIDATION];
   [ exceptions_clause ]
   [ update_index_clauses [ parallel_clause ] ]
   [ CASCADE ]

Supported operations

• Dropping a partition by using the drop_table_partition clause is supported. For more information, see Drop a partition.

• Dropping a subpartition by using the drop_table_subpartition clause is supported. For more information, see Drop a subpartition.

• Adding partitions and subpartitions by using the add_table_partition clause is supported. For more information, see Add partitions and subpartitions.

• Modifying a partition by using the modify_table_partition clause is supported. For more information, see Modify a partition.

• Truncating a partition by using the truncate_partition_subpart clause is supported. For more information, see Truncate a partition.

Unsupported operations

• Modifying a subpartition by using the modify_table_subpartition clause is not supported. An error will be returned when you perform this operation.

• Specifying new default values for table attributes by using the modify_table_default_attrs clause is not supported. An error will be returned when you perform this operation.

• Modifying an automatically partitioned table by using the alter_automatic_partitioning clause is not supported. An error will be returned when you perform this operation.

• Modifying an INTERVAL partitioned table by using the alter_interval_partitioning clause is not supported. An error will be returned when you perform this operation.

• Creating or replacing the current default RANGE, LIST, or HASH subpartitions in each table partition by using the set_subpartition_template clause is not supported. An error will be returned when you perform these operations.

• Moving a partition to another segment by using the move_table_partition clause is not supported. An error will be returned when you perform this operation.

• Moving a subpartition to another segment by using the move_table_subpartition clause is not supported. An error will be returned when you perform this operation.

• Adding external parameters to a partitioned table by using the add_external_partition_attrs clause is not supported. An error will be returned when you perform this operation.

• Selecting the last HASH partition, distributing the data on it into one or more remaining partitions determined by the hash function, and then dropping the last partition by using the coalesce_table_partition clause is not supported. An error will be returned when you perform these operations.

• Dropping external parameters in a partitioned table by using the drop_external_partition_attrs clause is not supported. An error will be returned when you perform this operation.

• Renaming a table partition or subpartition by using the rename_partition_subpart clause is not supported. An error will be returned when you perform this operation.

• Splitting a partition into multiple new partitions with a list of non-overlapping values by using the split_table_partition clause is not supported. An error will be returned when you perform this operation.

• Splitting a subpartition into multiple new subpartitions with a list of non-overlapping values by using the split_table_subpartition clause is not supported. An error will be returned when you perform this operation.

• Merging partitions by using the merge_table_partitions clause is not supported. An error will be returned when you perform this operation.

• Merging subpartitions by using the merge_table_subpartitions clause is not supported. An error will be returned when you perform this operation.

• Exchanging data and index segments by using the exchange_partition_subpart clause is not supported. An error will be returned when you perform this operation.