Drop partition data

This topic describes the supported conversion scope of ALTER TABLE DDL operations for dropping partition data during data migration from an Oracle database to the Oracle compatible mode of OceanBase Database.

Syntax

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

partition_extended_names:
{ PARTITION | PARTITIONS }
partition | { FOR ( partition_key_value [, partition_key_value ]... ) }
  [, partition | { FOR ( partition_key_value [, partition_key_value ]... ) } ]...

subpartition_extended_names:
{ SUBPARTITION | SUBPARTITIONS }
subpartition | { FOR ( subpartition_key_value [, subpartition_key_value ]... ) }
  [, subpartition | { FOR ( subpartition_key_value [, subpartition_key_value ]... ) } ]...

update_index_clauses:
{ { UPDATE | INVALIDATE } GLOBAL INDEXES
| UPDATE INDEXES
   [ ( index ( update_index_partition
             | update_index_subpartition
             )
        [, index ( update_index_partition
                 | update_index_subpartition
                 )
        ]...
      )
   ]
}

parallel_clause:
{ NOPARALLEL | PARALLEL [ integer ] }

Supported operations

• Dropping rows in a partition or subpartition by specifying the partition or subpartition name is supported. Example:

  ALTER TABLE T TRUNCATE PARTITION P0;
  ALTER TABLE T TRUNCATE PARTITIONS P0,P1;
  ALTER TABLE T TRUNCATE SUBPARTITION SP0;
  ALTER TABLE T TRUNCATE SUBPARTITIONS SP0,SP1;
  

• UPDATE GLOBAL INDEXES is supported to update indexes in a table.

Unsupported operations

INVALIDATE GLOBAL INDEXES is not supported. This operation will be converted into UPDATE GLOBAL INDEXES.

Ignored clauses and options

• The UPDATE INDEXES clause.

• The CASCADE clause.

• The { DROP [ ALL ] | REUSE } STORAGE clause for releasing all storage space of a row.

• The parallel_clause clause for modifying the default query and DML parallelism for a table.

• The option for dropping rows in a partition by specifying the partition or subpartition key value.