Modify a table with a random distribution

This topic describes how to modify a table with a random distribution using SQL statements.

Privilege requirements

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

Syntax

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

ALTER TABLE table_name PARTITION BY RANDOM SIZE('size_value');

Dynamic partition management attribute parameters

For more detailed parameter descriptions related to the table modification syntax, see ALTER TABLE.

Examples

1. Create a table named tbl1 with a random distribution and specify a partition size of 2 GB.

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

2. View the definition of table tbl1.

   obclient> SHOW CREATE TABLE tbl1;
   

   The query result is as follows:

   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                         |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | tbl1  | CREATE TABLE `tbl1` (
   `col1` int(11) DEFAULT NULL,
   `col2` datetime DEFAULT NULL,
   `col3` varchar(1024) DEFAULT NULL
   ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by random size ('2048MB') |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

3. Modify the partition size of table tbl1 to 8 GB.

   obclient> ALTER TABLE tbl1 PARTITION BY RANDOM SIZE('8GB');
   

4. View the definition of table tbl1 again.

   obclient> SHOW CREATE TABLE tbl1;
   

   The query result is as follows:

   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                         |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | tbl1  | CREATE TABLE `tbl1` (
   `col1` int(11) DEFAULT NULL,
   `col2` datetime DEFAULT NULL,
   `col3` varchar(1024) DEFAULT NULL
   ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by random size ('8192MB') |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

References

• Overview of Random Distribution Tables
• Create a Random Distribution Table