Partitioning

This topic describes the conversion scope of CREATE TABLE DDL operations for defining partitions during data migration from an Oracle database to the Oracle compatible mode 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) clause is not supported. This clause will be ignored.

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

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

• Defining physical and storage characteristics of partitioned tables by using the table_partition_description clause is not supported. This clause will be ignored.

List partitioning

Syntax of list partitions:

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 creation of list partitions by using the AUTOMATIC option is not supported. This option will be ignored.

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

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

• Defining physical and storage characteristics of partitioned tables by using the table_partition_description clause is not supported. This clause will be ignored.

Hash partitioning

Syntax of hash partitions:

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 custom hash partitions by using the individual_hash_partitions clause is supported. For more information, see User-defined partitioning.

• 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 clause 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 options in the partitioning_storage_clause clause are not supported:

• Specifying the index attribute for tables, table partitions, or table 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 attribute is not supported. This attribute 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.