User-defined partitioning

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

Syntax of range_partition_desc:

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. This option will be ignored.

User-defined LIST partitioning

Syntax of list_partition_desc:

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. This option will be ignored.

User-defined HASH partitioning

Syntax of individual_hash_partitions:

( 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. This clause will be ignored.

• Specifying the index attribute for tables, partitions, or subpartitions by using the INDEXING { ON | OFF } option is not supported. This option will be ignored.

• Specifying storage characteristics for HASH partitions and for RANGE, HASH, and LIST subpartitions by using the partitioning_storage_clause clause is not supported. This clause will be ignored.

Number of HASH partitions

Syntax of hash_partitions_by_quantity:

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

• Defining 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. This option will be ignored.

• Specifying tablespaces by using the OVERFLOW STORE IN (tablespace [, tablespace ]...) option is not supported. This option will be ignored.

• Specifying whether to compress data segments and indexes by using the table_compression and index_compression options is not supported. These options will be ignored.

User-defined RANGE subpartitioning

Syntax of range_subpartition_desc:

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. 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. This clause will be ignored.

• Specifying the index attribute for tables, partitions, or subpartitions by using the indexing_clause option is not supported. This option will be 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. This clause will be ignored.

• Specifying one or more external data sources by using the external_part_subpart_data_props option is not supported. This option will be ignored.

User-defined LIST subpartitioning

Syntax of list_subpartition_desc:

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. 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. This clause will be ignored.

• Specifying the index attribute for tables, partitions, or subpartitions by using the indexing_clause clause is not supported. This clause will be 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. This clause will be ignored.

• Specifying one or more external data sources by using the external_part_subpart_data_props option is not supported. This option will be ignored.

User-defined HASH subpartitioning

Syntax of individual_hash_subparts:

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. 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. This clause will be ignored.

• Specifying the index attribute for tables, partitions, or subpartitions by using the INDEXING { ON | OFF } option is not supported. This option will be 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. This clause will be ignored.

Number of HASH subpartitions

Syntax of hash_subparts_by_quantity:

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

• Specifying the number of HASH subpartitions by using the SUBPARTITIONS integer clause is supported. Example:

  SUBPARTITIONS 2
  

• Specifying tablespaces for subpartitions by using the STORE IN (tablespace [, tablespace ]...) option is not supported. This option will be ignored.