User-defined partitioning - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

This topic describes the conversion scope of CREATE TABLE DDL operations for defining user-defined partitions during data migration from an Oracle database to an Oracle tenant of OceanBase Database.

User-defined RANGE partitioning

The syntax of range_partition_desc is as follows:

PARTITION [partition]
range_values_clause
table_partition_description
[ ( { range_subpartition_desc [, range_subpartition_desc] ...
    | list_subpartition_desc [, list_subpartition_desc] ...
    | individual_hash_subparts [, individual_hash_subparts] ...
    }
  ) | hash_subparts_by_quantity ]
 
table_partition_description:
  [ { INTERNAL | EXTERNAL } ]
[ deferred_segment_creation ]
[ read_only_clause ]
[ indexing_clause ]
[ segment_attributes_clause ]
[ table_compression | prefix_compression ]
[ inmemory_clause ]
[ ilm_clause ]
[ OVERFLOW [ segment_attributes_clause ] ]
[ { LOB_storage_clause
  | varray_col_properties
  | nested_table_col_properties
  }...
]

User-defined RANGE partitioning and RANGE, LIST, and HASH subpartitioning are supported.
Specifying physical and storage characteristics for partitioned tables by using the table_partition_description option is not supported. If this option is used, it is ignored.

User-defined LIST partitioning

The syntax of list_partition_desc is as follows:

PARTITION [partition]
list_values_clause
table_partition_description
  [ ( range_subpartition_desc [, range_subpartition_desc]...
      | list_subpartition_desc, [, list_subpartition_desc]...
      | individual_hash_subparts [, individual_hash_subparts]...
    )
    | hash_subparts_by_quantity
  ]

User-defined LIST partitioning and RANGE, LIST, and HASH subpartitioning are supported.
Specifying physical and storage characteristics for partitioned tables by using the table_partition_description option is not supported. If this option is used, it is ignored.

User-defined HASH partitioning

The syntax of individual_hash_partitions is as follows:

( PARTITION [partition_name] [read_only_clause] [indexing_clause] [partitioning_storage_clause]
  [, PARTITION [partition] {read_only_clause] [indexing_clause] [partitioning_storage_clause]]... )

read_only_clause: 
{ READ ONLY } | { READ WRITE }

indexing_clause:
INDEXING { ON | OFF }

partitioning_storage_clause:
[ { { TABLESPACE tablespace | TABLESPACE SET tablespace_set }
  | OVERFLOW [ TABLESPACE tablespace] | TABLESPACE SET tablespace_set
  | table_compression
  | index_compression
  | inmemory_clause
  | ilm_clause
  | LOB_partitioning_storage
  | VARRAY varray_item STORE AS [SECUREFILE | BASICFILE] LOB LOB_segname
  }...
]

Defining HASH partitions by using the PARTITION [partition_name] option is supported.
Creating read-only or read/write tables, partitions, or subpartitions by using the read_only_clause clause is not supported. If this clause is used, it is ignored.
Specifying the index attribute for tables, partitions, or subpartitions by using the INDEXING { ON | OFF } option is not supported. If this option is used, it is ignored.
Specifying storage characteristics for HASH partitions and for RANGE, HASH, and LIST subpartitions by using the partitioning_storage_clause clause is not supported. If this clause is used, it is ignored.

Specifying the number of HASH partitions

The syntax of hash_partitions_by_quantity is as follows:

PARTITIONS hash_partition_quantity
[ STORE IN (tablespace [, tablespace ]...) ]
[ table_compression | index_compression ]
[ OVERFLOW STORE IN (tablespace [, tablespace ]...) ]

table_compression:
COMPRESS
| ROW STORE COMPRESS [ BASIC | ADVANCED ]
| COLUMN STORE COMPRESS [  FOR { QUERY | ARCHIVE } [ LOW | HIGH ] ]
  [ [NO] ROW LEVEL LOCKING ]
| NOCOMPRESS

index_compression:
{ prefix_compression
| advanced_index_compression
}

prefix_compression:
COMPRESS [ integer ] | NOCOMPRESS 

advanced_index_compression:
{ COMPRESS ADVANCED [ LOW | HIGH ] } | NOCOMPRESS

Specifying the number of HASH partitions by using the PARTITIONS hash_partition_quantity option is supported.
Specifying tablespaces by using the STORE IN (tablespace [, tablespace ]...) option is not supported. If this option is used, it is ignored.
Specifying tablespaces by using the OVERFLOW STORE IN (tablespace [, tablespace ]...) option is not supported. If this option is used, it is ignored.
Specifying whether to compress data segments and indexes by using the table_compression and index_compression options is not supported. If these options are used, they are ignored.

User-defined RANGE subpartitions

The syntax of range_subpartition_desc is as follows:

SUBPARTITION [subpartition_name] range_values_clause
  [read_only_clause] [indexing_clause] [partitioning_storage_clause]
  [external_part_subpart_data_props]

range_values_clause:
VALUES LESS THAN
  ({ literal | MAXVALUE }
     [, { literal | MAXVALUE } ]...
  )

read_only_clause: 
{ READ ONLY } | { READ WRITE }

indexing_clause:
INDEXING { ON | OFF }

partitioning_storage_clause:
[ { { TABLESPACE tablespace | TABLESPACE SET tablespace_set }
  | OVERFLOW [ TABLESPACE tablespace] | TABLESPACE SET tablespace_set
  | table_compression
  | index_compression
  | inmemory_clause
  | ilm_clause
  | LOB_partitioning_storage
  | VARRAY varray_item STORE AS [SECUREFILE | BASICFILE] LOB LOB_segname
  }...
]

external_part_subpart_data_props:
[ DEFAULT DIRECTORY directory ]
LOCATION
   ([ directory: ] 'location_specifier'
      [, [ directory: ] 'location_specifier' ]...
   )
]

Defining subpartitions by using the SUBPARTITION [subpartition_name] range_values_clause option is supported. Here is an example:
```
SUBPARTITION VALUES LESS THAN (MAXVALUE)
```
Creating read-only or read/write tables, partitions, or subpartitions by using the read_only_clause clause is not supported. If this clause is used, it is ignored.
Specifying the index attribute for tables, partitions, or subpartitions by using the indexing_clause option is not supported. If this option is used, it is ignored.
Specifying the storage characteristics for HASH partitions and for RANGE, HASH, and LIST subpartitions by using the partitioning_storage_clause clause is not supported. If this clause is used, it is ignored.
Specifying one or more external data sources by using the external_part_subpart_data_props option is not supported. If this option is used, it is ignored.

User-defined LIST subpartitions

The syntax of list_subpartition_desc is as follows:

SUBPARTITION [subpartition_name] list_values_clause
  [read_only_clause] [indexing_clause] [partitioning_storage_clause]
  [external_part_subpart_data_props]

list_values_clause:
VALUES ( list_values | DEFAULT )

read_only_clause: 
{ READ ONLY } | { READ WRITE }

indexing_clause:
INDEXING { ON | OFF }

partitioning_storage_clause:
[ { { TABLESPACE tablespace | TABLESPACE SET tablespace_set }
  | OVERFLOW [ TABLESPACE tablespace] | TABLESPACE SET tablespace_set
  | table_compression
  | index_compression
  | inmemory_clause
  | ilm_clause
  | LOB_partitioning_storage
  | VARRAY varray_item STORE AS [SECUREFILE | BASICFILE] LOB LOB_segname
  }...
]

external_part_subpart_data_props:
[ DEFAULT DIRECTORY directory ]
LOCATION
   ([ directory: ] 'location_specifier'
      [, [ directory: ] 'location_specifier' ]...
   )
]

Defining LIST subpartitions by using the SUBPARTITION [subpartition_name] list_values_clause clause is supported. Here is an example:
```
SUBPARTITIONS P0 VALUES (DEFAULT)
```
Creating read-only or read/write tables, partitions, or subpartitions by using the read_only_clause clause is not supported. If this clause is used, it is ignored.
Specifying the index attribute for tables, partitions, or subpartitions by using the indexing_clause clause is not supported. If this clause is used, it is ignored.
Specifying the storage characteristics for HASH partitions and for RANGE, HASH, and LIST subpartitions by using the partitioning_storage_clause clause is not supported. If this clause is used, it is ignored.
Specifying one or more external data sources by using the external_part_subpart_data_props option is not supported. If this option is used, it is ignored.

User-defined HASH subpartitions

The syntax of individual_hash_subparts is as follows:

SUBPARTITION [subpartition_name] [read_only_clause] [indexing_clause] [partitioning_storage_clause]

read_only_clause: 
{ READ ONLY } | { READ WRITE }

indexing_clause:
INDEXING { ON | OFF }

partitioning_storage_clause:
[ { { TABLESPACE tablespace | TABLESPACE SET tablespace_set }
  | OVERFLOW [ TABLESPACE tablespace] | TABLESPACE SET tablespace_set
  | table_compression
  | index_compression
  | inmemory_clause
  | ilm_clause
  | LOB_partitioning_storage
  | VARRAY varray_item STORE AS [SECUREFILE | BASICFILE] LOB LOB_segname
  }...
]

Defining HASH subpartitions by using the SUBPARTITION [subpartition_name] option is supported. Here is an example:
```
(SUBPARTITION SP0,SUBPARTITION SP1)
```
Creating read-only or read/write tables, partitions, or subpartitions by using the read_only_clause clause is not supported. If this clause is used, it is ignored.
Specifying the index attribute for tables, partitions, or subpartitions by using the INDEXING { ON | OFF } option is not supported. If this option is used, it is ignored.
Specifying the storage characteristics for HASH partitions and for RANGE, HASH, and LIST subpartitions by using the partitioning_storage_clause clause is not supported. If this clause is used, it is ignored.

Specifying the number of HASH subpartitions

The syntax of hash_subparts_by_quantity is as follows:

SUBPARTITIONS integer [STORE IN ( tablespace [, tablespace]... )]

Specifying the number of HASH subpartitions by using the SUBPARTITIONS integer clause is supported. Here is an example:
```
SUBPARTITIONS 2
```
Specifying tablespaces for subpartitions by using the STORE IN (tablespace [, tablespace ]...) option is not supported. If this option is used, it is ignored.