Create a table with random distribution

In OceanBase Database, you can explicitly create a table with random distribution by specifying PARTITION BY RANDOM.

Limitations and considerations

• Primary key requirement: No primary key is required.
• Partition level: Only partitions are supported; subpartitions are not supported.
• Table type: Only user tables are supported; temporary tables and system tables are not supported.
• Temporary tables and CTAS (CREATE TABLE AS SELECT) are not supported.
• LOCAL unique indexes are not supported; unique indexes must be of the GLOBAL type.
• Joining table groups (TABLEGROUP) is not supported.
• Changing a non-randomly distributed table to a randomly distributed table using ALTER is not supported.
• Performing partition operations on a table with random distribution (DROP/SPLIT/EXCHANGE/TRUNCATE PARTITION) is not supported.

Privilege requirements

You must have the CREATE privilege to create a table with random distribution. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.

Explicitly create a table with random distribution

Syntax

The SQL statement for creating a table with random distribution is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (table_definition_list)
    PARTITION BY RANDOM [SIZE('size_value')];

Parameters

For more detailed parameter descriptions, see CREATE TABLE.

Examples

• Create a table with random distribution named tbl1 using the default partition size.

  obclient> CREATE TABLE tbl1(
      col1 INT,
      col2 VARCHAR2(1024)
      )
      PARTITION BY RANDOM;
  

• Create a table with random distribution named tbl2 and specify a partition size of 4 GB.

  obclient> CREATE TABLE tbl2(
      col1 INT,
      col2 VARCHAR2(1024)
      )
      PARTITION BY RANDOM SIZE('4GB');
  

References

• Overview of tables with random distribution
• Modify a table with random distribution