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 Oracle-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');

Description of dynamic partition management attributes and parameters

For more detailed information about the parameters in the ALTER TABLE statement, 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 VARCHAR2(1024)
       )
       PARTITION BY RANDOM SIZE('2GB');
   

2. View the definition of the tbl1 table.

   obclient> SHOW CREATE TABLE tbl1;
   

   The query result is as follows:

   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | TABLE | CREATE TABLE                                                                                                                                                                                                                                                            |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | TBL1  | CREATE TABLE "TBL1" (
   "COL1" NUMBER(*,0),
   "COL2" VARCHAR2(1024)
   ) COMPRESS FOR ARCHIVE 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 the table named tbl1 to 8 GB.

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

4. View the definition of the tbl1 table again.

   obclient> SHOW CREATE TABLE tbl1;
   

   The query result is as follows:

   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | TABLE | CREATE TABLE                                                                                                                                                                                                                                                            |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | TBL1  | CREATE TABLE "TBL1" (
   "COL1" NUMBER(*,0),
   "COL2" VARCHAR2(1024)
   ) COMPRESS FOR ARCHIVE 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