Modify a table

After a table is created, you can use the ALTER TABLE statement to modify it.

Considerations

When you modify the primary key or column type of a table, you cannot perform other DDL operations. Similarly, when performing other DDL operations, you cannot modify the primary key or column type of the table.

Add, modify, drop columns, and clear obsolete columns

OceanBase Database supports adding columns, modifying column attributes, dropping columns, and clearing obsolete columns.

Add a column

OceanBase Database supports adding columns to a table, but does not support directly adding a primary key column. If you need to add a primary key column, it is recommended to add the column first and then add a primary key to it. For related operations on adding a primary key, see Define column constraints.

The SQL syntax for adding a column is as follows:

ALTER TABLE table_name ADD column_definition;

Example:

1. Create a test table named test_tbl1.

   CREATE TABLE test_tbl1 (
     col1 NUMBER(38),
     col2 VARCHAR2(50),
     PRIMARY KEY(col1));
   

2. Add a column named col3 to the test_tbl1 table.

   ALTER TABLE test_tbl1 ADD col3 NUMBER(38);
   

3. View the structure of the test_tbl1 table.

   DESCRIBE test_tbl1;
   

   The return result is as follows:

   +-------+--------------+------+------+---------+-------+
   | FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +-------+--------------+------+------+---------+-------+
   | COL1  | NUMBER(38)   | NO   | PRI  | NULL    | NULL  |
   | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
   | COL3  | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
   +-------+--------------+------+------+---------+-------+
   3 rows in set
   

Modify a column

Modify column attributes

OceanBase Database supports conversions between different column types, as well as modifications to default values and NOT NULL constraints. For the conversion rules and detailed information about column types in OceanBase Database, see Column type conversion rules.

The SQL syntax for modifying a column type is as follows:

ALTER TABLE table_name MODIFY [COLUMN] column_definition;

Example:

1. Create a test table named test_tbl2.

   CREATE TABLE test_tbl2 (
     col1 NUMBER(38),
     col2 VARCHAR2(20));
   

2. View the structure of the test_tbl2 table.

   DESCRIBE test_tbl2;
   

   The result is as follows:

   +-------+--------------+------+------+---------+-------+
   | FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +-------+--------------+------+------+---------+-------+
   | COL1  | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
   | COL2  | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
   +-------+--------------+------+------+---------+-------+
   2 rows in set
   

3. Change the type of column col2 in the test_tbl2 table to CHAR(50).

   ALTER TABLE test_tbl2 MODIFY col2 CHAR(50);
   

4. Set the default value of column col1 in the test_tbl2 table to 0.

   ALTER TABLE test_tbl2 MODIFY col1 DEFAULT 0;
   

5. Make the value of column col2 in the test_tbl2 table not null.

   ALTER TABLE test_tbl2 MODIFY col2 NOT NULL;
   

6. View the structure of the test_tbl2 table.

   DESCRIBE test_tbl2;
   

   The result is as follows:

   +-------+------------+------+------+---------+-------+
   | FIELD | TYPE       | NULL | KEY  | DEFAULT | EXTRA |
   +-------+------------+------+------+---------+-------+
   | COL1  | NUMBER(38) | YES  | NULL | 0       | NULL  |
   | COL2  | CHAR(50)   | NO   | NULL | NULL    | NULL  |
   +-------+------------+------+------+---------+-------+
   2 rows in set
   

Rename a column

The SQL syntax for renaming a column is as follows:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

Example:

1. Create a test table named test_tbl3.

   CREATE TABLE test_tbl3 (
     col1 NUMBER(38),
     col2 VARCHAR2(20),
     PRIMARY KEY(col1));
   

2. Rename column col1 in the test_tbl3 table to col1_new.

   ALTER TABLE test_tbl3 RENAME COLUMN col1 TO col1_new;
   

3. View the structure of the test_tbl3 table.

   DESCRIBE test_tbl3;
   

   The result is as follows:

   +----------+--------------+------+------+---------+-------+
   | FIELD    | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +----------+--------------+------+------+---------+-------+
   | COL1_NEW | NUMBER(38)   | NO   | PRI  | NULL    | NULL  |
   | COL2     | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
   +----------+--------------+------+------+---------+-------+
   2 rows in set
   

Delete columns

You can delete columns from a table, but you cannot delete a primary key column.

Delete a single column

The SQL syntax for deleting a single column is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

1. Create a test table named test_tbl4.

   CREATE TABLE test_tbl4 (
     col1 NUMBER(38),
     col2 VARCHAR2(50),
     PRIMARY KEY(col1));
   

2. Delete the column col2 from the test_tbl4 table.

   ALTER TABLE test_tbl4 DROP COLUMN col2;
   

3. View the structure of the test_tbl4 table.

   DESCRIBE test_tbl4;
   

   The result is as follows:

   +-------+------------+------+------+---------+-------+
   | FIELD | TYPE       | NULL | KEY  | DEFAULT | EXTRA |
   +-------+------------+------+------+---------+-------+
   | COL1  | NUMBER(38) | NO   | PRI  | NULL    | NULL  |
   +-------+------------+------+------+---------+-------+
   1 row in set
   

Delete multiple columns

The SQL syntax for deleting multiple columns is as follows:

ALTER TABLE table_name DROP (column_name1, column_name1, ...);

or

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

Example:

1. Create a test table named test_tbl5.

   CREATE TABLE test_tbl5 (
     col1 NUMBER(38),
     col2 VARCHAR2(50),
     col3 NUMBER(38),
     col4 NUMBER(38),
     col5 NUMBER(38),
     col6 NUMBER(38),
     col7 NUMBER(38),
     PRIMARY KEY(col1));
   

2. Delete the columns col6 and col7 from the test_tbl5 table.

   ALTER TABLE test_tbl5 DROP (col6, col7);
   

3. Delete columns col4 and col5 from table test_tbl5.

   ALTER TABLE test_tbl5 DROP COLUMN col4, DROP COLUMN col5;
   

4. View the structure of table test_tbl5.

   DESCRIBE test_tbl5;
   

   The return result is as follows:

   +-------+--------------+------+------+---------+-------+
   | FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +-------+--------------+------+------+---------+-------+
   | COL1  | NUMBER(38)   | NO   | PRI  | NULL    | NULL  |
   | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
   | COL3  | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
   +-------+--------------+------+------+---------+-------+
   3 rows in set
   

Clean up unused columns

When columns are dropped, they still occupy physical storage space even though they are no longer in use. To remove these unused columns and reclaim the associated space, you need to clean them up.

The SQL syntax for cleaning up unused columns is as follows:

ALTER TABLE table_name FORCE;

Example:

ALTER TABLE test_tbl5 FORCE;

Add a unique constraint

OceanBase Database supports adding a unique constraint to an existing table.

The SQL syntax for adding a unique constraint is as follows:

ALTER TABLE table_name ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...);

Example:

1. Create a test table named test_tbl6.

   CREATE TABLE test_tbl6 (
     col1 NUMBER(38),
     col2 VARCHAR2(50),
     PRIMARY KEY(col1));
   

2. Add a unique constraint on column col2 of table test_tbl6.

   ALTER TABLE test_tbl6 ADD UNIQUE(col2);
   

3. View the structure of table test_tbl6.

   DESCRIBE test_tbl6;
   

   The return result is as follows:

   +-------+--------------+------+------+---------+-------+
   | FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
   +-------+--------------+------+------+---------+-------+
   | COL1  | NUMBER(38)   | NO   | PRI  | NULL    | NULL  |
   | COL2  | VARCHAR2(50) | YES  | UNI  | NULL    | NULL  |
   +-------+--------------+------+------+---------+-------+
   2 rows in set
   

Rename a table

After a table is created, you can rename it.

The SQL syntax for renaming a table is as follows:

ALTER TABLE old_table_name RENAME TO new_table_name;

or

RENAME old_table_name TO new_table_name;

Example:

ALTER TABLE test RENAME TO t1;

or

RENAME test TO t1;

Modify the primary key and foreign keys of a table

After a table is created, OceanBase Database supports adding or dropping the primary key and foreign keys of a table. For specific operations and descriptions on modifying the primary key and foreign keys of a table, see Define column constraints.

Modify the skip index attribute of a column

OceanBase Database supports using the ALTER TABLE statement to add, modify, and drop the skip index attribute.

For more information about skip indexes, see Column skip index attribute.

Example:

1. Use the following SQL statement to create the table test_skidx.

   CREATE TABLE test_skidx(
     col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
     col2 FLOAT SKIP_INDEX(MIN_MAX),
     col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
     col4 CHAR(10)
     );
   

2. Modify the skip index attribute of column col2 in the test_skidx table to SUM skip index type.

   ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
   

3. Add a skip index attribute after table creation. Add a MIN_MAX skip index type for column col4 in the test_skidx table.

   ALTER TABLE test_skidx MODIFY col4 CHAR(10) SKIP_INDEX(MIN_MAX);
   

4. Drop a skip index attribute after table creation. Drop the skip index attribute for column col1 in the test_skidx table.

   ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
   

Change the table mode

The table mode (merge_engine) specifies the update model for a table. After a table is created, you can use the ALTER TABLE statement to perform an online conversion between different table modes without blocking normal read and write operations. The syntax is as follows:

ALTER TABLE table_name SET merge_engine = {partial_update | delete_insert | append_only};

Parameter description:

• table_name: The name of the table whose mode you want to change.

• merge_engine: The update model for the table. Valid values:

  • partial_update: The partial update model. Each update records only the modified columns. This mode saves storage space and is suitable for OLTP scenarios.
  • delete_insert: The full column update model. Each update writes the complete row. This mode is better for queries and is suitable for OLAP scenarios.
  • append_only: The append-only write model. In this mode, data is written only; data modification is not allowed. This mode is suitable for IoT, monitoring, financial transactions, and other scenarios.

  For more information about table modes, see Create a table and the parameter default_table_merge_engine.

Example:

1. Create the table mtbl1 without specifying MERGE_ENGINE. In this case, the table mode uses the value of the default_table_merge_engine parameter (which is partial_update by default).

   obclient> CREATE TABLE mtbl1(col1 NUMBER PRIMARY KEY, col2 VARCHAR2(30));
   

2. Convert the table mode of mtbl1 to delete_insert.

   obclient> ALTER TABLE mtbl1 SET merge_engine = delete_insert;
   

Convert a table between row-based and columnar storage

By default, when you create a table in OceanBase Database, it is a row-based table. You can explicitly specify it as a columnar table or a hybrid row-columnar table by setting the WITH COLUMN GROUP option.

After a table is created, you can use the ALTER TABLE statement to convert the table between row-based and columnar storage. The syntax is as follows:

• To change a table to a columnar table:

  ALTER TABLE table_name ADD COLUMN GROUP([all columns,] each column) [DELAYED];
  

• To remove the storage format from a table:

  ALTER TABLE table_name DROP COLUMN GROUP([all columns,] each column) [DELAYED];
  

Parameter description:

• table_name: The name of the table.

• ADD COLUMN GROUP(all columns, each column): Changes the table to a hybrid row-column storage format.

• ADD COLUMN GROUP(each column): Changes the table to a columnar storage format.

• DELAYED: Optional. Delays (asynchronously) the execution of the command to convert a row-store table to a column-store table. After the command is executed, the storage format in the table definition is modified, but the actual conversion from row-store to column-store format occurs during a major compaction task. This operation does not block current DML and is an online DDL operation. If DELAYED is not specified, the default is an offline DDL operation, which synchronously converts the row-store table to a column-store table.

  Notice

  • The DELAYED option is currently only supported when modifying a row-store table to a column-store table (each column) or to a hybrid row-column storage format table (all columns, each column).
  • After executing the DELAYED command to delay the conversion of a row-store table to a column-store table, query performance may not meet expectations before the baseline data is fully merged, as the data storage format has not yet been converted.

• DROP COLUMN GROUP(all columns, each column): Removes the hybrid row-column storage format from the table.

• DROP COLUMN GROUP(all columns): Removes the row-store format from the table.

• DROP COLUMN GROUP(each column): Removes the columnar storage format from the table.

• DELAYED: Optional. Converts a column-store table to a row-store table online (asynchronously, without blocking DML). After specifying DELAYED, the storage format in the table definition is modified after the command is executed, but the actual conversion from column-store to row-store format occurs during a major compaction task, making it an online DDL operation. If DELAYED is not specified, the default is an offline DDL operation, which synchronously converts the column-store table to a row-store table.

  Notice

  • Online conversion from column-store to row-store format (DROP COLUMN GROUP(...) DELAYED) can only be performed after all nodes in the cluster have been upgraded to OceanBase Database V5.0.1 or later.
  • Online conversion from column-store to row-store format is not supported for hybrid row-column storage format tables created before V5.0.1 (WITH COLUMN GROUP(all columns, each column)). Related SQL statements will return a NOT SUPPORTED error.

Convert a row-store table to a column-store table

Example:

• Create a row-store table by default.

  1. Create a row-store table named tbl1.

     CREATE TABLE tbl1(col1 NUMBER, col2 VARCHAR2(30));
     

  2. Convert the row-store table tbl1 to a column-store table.

     • Offline DDL modification:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
       

     • Online DDL (asynchronously convert a row-store table to a column-store table) modification:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column) DELAYED;
       

• Create a row-store table with the WITH COLUMN GROUP(all columns) option.

  1. Create a row-store table named tbl1.

     CREATE TABLE tbl1_ac(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns);
     

  2. Change the row-store table tbl1 to a column-store table.

     • The offline DDL is modified as follows:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column);
       

     • The online DDL (asynchronously converting a row-store table to a column-store table) is modified as follows:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column) DELAYED;
       

  3. Set DROP COLUMN GROUP(all columns) to drop the row-store format.

     ALTER TABLE tbl1_ac DROP COLUMN GROUP(all columns);
     

Convert a row-store table into a hybrid row-store and column-store table

Example:

1. Create a row-store table tbl2.

   CREATE TABLE tbl2(col1 NUMBER, col2 VARCHAR2(30));
   

2. Convert the row-store table tbl2 into a hybrid row-store and column-store table.

   • The offline DDL is modified as follows:

     ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);
     

   • The online DDL (asynchronously converting a row-store table into a hybrid row-store and column-store table) is modified as follows:

     ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column) DELAYED;
     

Convert a hybrid row-store and column-store table into a column-store table

Example:

1. Create a hybrid row-store and column-store table tbl3.

   CREATE TABLE tbl3(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the hybrid row-store and column-store table tbl3 into a column-store table.

   • The offline DDL is modified as follows:

     ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
     

   • The online DDL (asynchronously executed, without blocking business read/write) is modified as follows:

     ALTER TABLE tbl3 DROP COLUMN GROUP(all columns) DELAYED;
     

Convert a hybrid row-column storage table to a row-based table

Example:

1. Create a hybrid row-column storage table named tbl4.

   CREATE TABLE tbl4(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the hybrid row-column storage table tbl4 to a row-based table.

   • The modified offline DDL statement is as follows:

     ALTER TABLE tbl4 DROP COLUMN GROUP(each column);
     

     or

     ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column);
     

   • The modified online DDL statement (asynchronously converts columnar storage to row-based storage without blocking business read/write operations) is as follows:

     ALTER TABLE tbl4 DROP COLUMN GROUP(each column) DELAYED;
     

     or

     ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column) DELAYED;
     

References

For more information about the ALTER TABLE statement, see ALTER TABLE.