Partitioning

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

RANGE partitioning

Syntax of range_partitions:

PARTITION BY RANGE (column[, column ]...)
  [ INTERVAL ( expr ) [ STORE IN ( tablespace [, tablespace]...) ]]
( PARTITION [ partition ]
    range_values_clause table_partition_description
      [, PARTITION [ partition ]
        range_values_clause table_partition_description
        [ external_part_subpart_data_props ]
      ]...
)

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

table_partition_description:
PARTITION [partition] [table_partition_description]

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

Supported operations

Defining RANGE partitions by using the PARTITION BY RANGE (column[, column ]...) (PARTITION [ partition ] range_values_clause) clause is supported. Example:

CREATE TABLE T (C1 DATE DEFAULT SYSDATE, C2 NUMBER) PARTITION BY RANGE(C1)
 (  
   PARTITION P0 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
   PARTITION P1 VALUES LESS THAN (MAXVALUE)
 );

Unsupported operations

• Specifying partition intervals by using the INTERVAL (expr) option is not supported. This option will be ignored.

• Specifying tablespaces by using the STORE IN (tablespace [, tablespace ]...) option is not supported. This option 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.

• Specifying physical and storage characteristics for partitioned tables by using the table_partition_description option is not supported. This option will be ignored.

LIST partitioning

Syntax for LIST partitioning:

PARTITION BY LIST ( column [, column]... )
[ AUTOMATIC [ STORE IN ( tablespace [, tablespace ]... ) ] ]
(PARTITION [ partition ]
    list_values_clause table_partition_description
  [, PARTITION [ partition ]
        list_values_clause table_partition_description
        [ external_part_subpart_data_props ]
  ]...
)

list_values_clause:
VALUES ( list_values | DEFAULT )

table_partition_description:
PARTITION [partition] [table_partition_description]

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

Supported operations

Defining LIST partitions by using the PARTITION BY LIST ( column [, column]... ) (PARTITION [ partition ] list_values_clause) clause is supported. Example:

CREATE TABLE T (C1 VARCHAR2(100)) PARTITION BY LIST(C1)
 (  
  PARTITION P0 VALUES IN ("1","2"),
  PARTITION P1 VALUES IN ("3","4")
 );

Unsupported operations

• Specifying automatic LIST partition creation by using the AUTOMATIC option is not supported. This option will be ignored.

• Specifying tablespaces by using the STORE IN (tablespace [, tablespace ]...) option is not supported. This option 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.

• Specifying physical and storage characteristics for partitioned tables by using the table_partition_description option is not supported. This option will be ignored.

HASH partitioning

Syntax for HASH partitioning:

PARTITION BY HASH (column [, column ] ...)
{ individual_hash_partitions
| hash_partitions_by_quantity
}

individual_hash_partitions:
( PARTITION [partition] [read_only_clause] [indexing_clause] [partitioning_storage_clause]
  [, PARTITION [partition] [read_only_clause] [indexing_clause] [partitioning_storage_clause]]... )

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
  }...
]

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

Supported operations

• Defining HASH partitions by using the individual_hash_partitions option is supported. For more information, see User-defined partitions.

• Defining the number of HASH partitions by using the partitioning_storage_clause clause is supported. Only the PARTITION BY HASH (column [, column ] ...) PARTITIONS hash_partition_quantity option is supported for defining the number of HASH partitions. Example:

  CREATE TABLE T (C NUMBER) PARTITION BY HASH(C) PARTITIONS 4
  

Unsupported operations

The following operations that use the partitioning_storage_clause clause are not supported:

• 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.

• Specifying tablespaces by using the 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.

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