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-compatible 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("1","2"),
    PARTITION P1 VALUES("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.