Create a table with random distribution

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

Limitations and considerations

• Primary key requirement: No primary key, or a single-column BIGINT AUTO_INCREMENT auto-increment primary key, and the auto-increment primary key must use the NOORDER mode.
• Partition level: Only supports partitions; subpartitions are not supported.
• Table type: Only supports user tables; temporary tables and system tables are not supported.
• Does not support temporary tables or CTAS (CREATE TABLE AS SELECT).
• Does not support LOCAL unique indexes; unique indexes must be GLOBAL type.
• Does not support joining tablegroups (TABLEGROUP).
• Does not support altering a non-randomly distributed table to a randomly distributed table.
• Does not support partition operations on a table with random distribution (DROP/SPLIT/EXCHANGE/TRUNCATE PARTITION).

Additional limitations on auto-increment columns

• Not compatible with native MySQL auto-increment behavior; only supports NOORDER mode.
• Does not guarantee increment; only guarantees uniqueness at the table level.
• Does not support LAST_INSERT_ID()/mysql_insert_id().
• Not affected by system variables auto_increment_increment or auto_increment_offset.
• Supports NO_AUTO_VALUE_ON_ZERO.
• Supports writing a specified value (ensuring no conflicts in subsequent assignments).

Privilege requirements

Creating a table with random distribution requires the CREATE privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Explicitly create a table with random distribution

Syntax

The SQL statement format 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')];

Parameter description

For more detailed parameter descriptions related to the table creation syntax, see CREATE TABLE.

Examples

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

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

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

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

• Create a random distribution table named tbl3 with an auto-increment primary key and specify a partition size of 32 GB for it.

  obclient> CREATE TABLE tbl3(
      col1 BIGINT AUTO_INCREMENT PRIMARY KEY,
      col2 DECIMAL(10,2),
      col3 VARCHAR(256)
      )
      AUTO_INCREMENT_MODE = 'NOORDER'
      PARTITION BY RANDOM SIZE('32GB');
  

References

• Random distribution tables
• Modify a random distribution table