In Oracle mode, OceanBase Database allows you to change primary keys, partitioning types, and data types of columns through DDL operations.
Note
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Change primary keys
In Oracle mode, OceanBase Database allows you to add, modify, and drop primary keys.
Add a primary key
Syntax:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Sample code:
obclient> CREATE TABLE tbl1(c1 INT,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
obclient> ALTER TABLE tbl1 ADD PRIMARY KEY(c1);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Change a primary key
Syntax:
ALTER TABLE table_name MODIFY PRIMARY KEY (column_name);
Sample code:
obclient> ALTER TABLE tbl1 MODIFY PRIMARY KEY (c2);
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Drop a primary key
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY;
Sample code:
obclient> ALTER TABLE tbl1 DROP PRIMARY KEY;
Query OK, 0 rows affected
obclient [test]> SHOW CREATE TABLE tbl1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1 | CREATE TABLE `tbl1` (
`c1` int(11) NOT NULL,
`c2` varchar(50) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Change partitioning types
In Oracle mode, OceanBase Database allows you to convert a non-partitioned table into a partitioned or subpartitioned table. Syntax:
ALTER TABLE table_name MODIFY partition_option;
Notice
The current version of OceanBase Database supports only conversion from non-partitioned tables into partitioned or subpartitioned tables.
Examples
Example 1: Convert a non-partitioned table into a partitioned table.
obclient> CREATE TABLE tbl1(c1 INT PRIMARY KEY, c2 DATE);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 MODIFY PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL1 | CREATE TABLE "TBL1" (
"C1" NUMBER(*,0),
"C2" DATE,
CONSTRAINT "TBL1_OBPK_1668762793014376" PRIMARY KEY ("C1")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by hash(c1)
(partition P0,
partition P1,
partition P2,
partition P3) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 2: Convert a non-partitioned table into a template-based subpartitioned table.
obclient> CREATE TABLE tbl2(c1 INT, c2 DATE, PRIMARY KEY(c1, c2));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 MODIFY PARTITION BY HASH(c1)
SUBPARTITION BY RANGE (c2)
SUBPARTITION TEMPLATE(
SUBPARTITION p1 VALUES LESS THAN (TO_DATE('2016/02/01','YYYY/MM/DD')),
SUBPARTITION p2 VALUES LESS THAN (TO_DATE('2116/02/01','YYYY/MM/DD'))
);
Query OK, 0 rows affected
obclient [SYS]> SHOW CREATE TABLE tbl2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL2 | CREATE TABLE "TBL2" (
"C1" NUMBER(*,0),
"C2" DATE,
CONSTRAINT "TBL2_OBPK_1668762841207762" PRIMARY KEY ("C1", "C2")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by hash(c1) subpartition by range(c2) subpartition template (
subpartition P1 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
subpartition P2 values less than (TO_DATE(' 2116-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
(partition P0) |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 3: Convert a non-partitioned table into a non-template-based subpartitioned table.
obclient> CREATE TABLE tbl3(c1 INT, c2 DATE, PRIMARY KEY(c1, c2));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3 MODIFY PARTITION BY RANGE(c1)
SUBPARTITION BY RANGE(c2) (
PARTITION p0 VALUES LESS THAN(0),
PARTITION p1 VALUES LESS THAN(100));
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE tbl3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL3 | CREATE TABLE "TBL3" (
"C1" NUMBER(*,0),
"C2" DATE,
CONSTRAINT "TBL3_OBPK_1668762883951475" PRIMARY KEY ("C1", "C2")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range(c1) subpartition by range(c2)
(partition P0 values less than (0) (
subpartition P8192 values less than (MAXVALUE)),
partition P1 values less than (100) (
subpartition P8193 values less than (MAXVALUE))) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Change the data type of columns
In Oracle mode, OceanBase Database allows you to convert the data types of columns:
- Data type conversion for character data types
CHARandVARCHAR2 - Precision change for the numeric data type
NUMBER. Note that you can only increase the precision. - Precision change for character data types
CHAR(only precision increase supported),VARCHAR2,NVARCHAR2, andNCHAR
Syntax:
ALTER TABLE table_name MODEFY column_name data_type;
Examples
Conversion between character data types
Example 1: Change the data type and increase the length of a character column.
obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 VARCHAR2(32));
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(64);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY c3 CHAR(256);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test01;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TEST01 | CREATE TABLE "TEST01" (
"C1" NUMBER(*,0),
"C2" VARCHAR2(20),
"C3" CHAR(256),
CONSTRAINT "TEST01_OBPK_1668762938184544" PRIMARY KEY ("C1")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 2: Decrease the length of a character column.
obclient> CREATE TABLE test02(c1 VARCHAR2(128));
Query OK, 0 rows affected
obclient> ALTER TABLE test02 MODIFY c1 VARCHAR2(64);
Query OK, 0 rows affected
obclient [SYS]> SHOW CREATE TABLE test02;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TEST02 | CREATE TABLE "TEST02" (
"C1" VARCHAR2(64)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
obclient> CREATE TABLE test03(c1 CHAR(10));
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c1 CHAR(20);
Query OK, 0 rows affected
obclient> SHOW CREATE TABLE test03;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TEST03 | CREATE TABLE "TEST03" (
"C1" CHAR(20)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Precision change for a numeric data type
Example 1: Change the precision of a numeric column with a specified precision.
obclient> CREATE TABLE test05(c1 NUMBER(10,2));
Query OK, 0 rows affected
obclient> ALTER TABLE test05 MODIFY c1 NUMBER(11,3);
Query OK, 0 rows affected
obclient [SYS]> SHOW CREATE TABLE test05;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TEST05 | CREATE TABLE "TEST05" (
"C1" NUMBER(11,3)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set