ApsaraDB for OceanBase:Partitioning

RANGE partitioning

The syntax of range_partitions is as follows:

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 DDL

Defining RANGE partitions by using the PARTITION BY RANGE (column[, column ]...) (PARTITION [ partition ] range_values_clause) option is supported. Here is an 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 DDL

• Specifying partition intervals by using the INTERVAL (expr) option is not supported. If this option is used, it is ignored.

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

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

LIST partitioning

The syntax of LIST partitioning is as follows:

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 DDL

Defining LIST partitions by using the PARTITION BY LIST ( column [, column]... ) (PARTITION [ partition ] list_values_clause) option is supported. Here is an example:

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

Unsupported DDL

• Specifying automatic LIST partition creation by using the AUTOMATIC option is not supported. If this option is used, it is ignored.

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

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

HASH partitioning

The syntax of HASH partitioning is as follows:

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 DDL

• 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. Here is an example:

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

Unsupported DDL

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. 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. If this clause is used, it is ignored.

• Specifying tablespaces by using the STORE IN (tablespace [, tablespace ]...) option. 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. If these options are used, they are ignored.

• Specifying tablespaces by using the OVERFLOW STORE IN (tablespace [, tablespace ]...) option. If this option is used, it is ignored.