Modify a table

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

Considerations

Do not perform other DDL operations when you change the primary key or column type of a table. Do not change the primary key or column type of a table when performing other DDL operations.

Add, modify, delete columns, and clear abandoned columns

OceanBase Database supports adding columns, modifying columns and their attributes, deleting columns, and clearing abandoned columns.

Add a column

OceanBase Database allows you to add columns to a table, but does not allow you to directly add a primary key column. If you want to add a primary key column, we recommend that you first add the column and then add the primary key. For more information about how to add a primary key, see Define a constraint type for a column.

The SQL syntax for adding a column is as follows:

ALTER TABLE table_name ADD column_definition;

Here is an 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 schema 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 conversion between various column types and default values and NOT NULL constraints. For more information about column type conversion in OceanBase Database, see Column type change rules.

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

ALTER TABLE table_name MODIFY [COLUMN] column_definition;

Here is an example:

1. Create a test table named test_tbl2.

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

2. View the schema of the test_tbl2 table.

   DESCRIBE test_tbl2;
   

   The return 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. Modify the type of the col2 column in the test_tbl2 table to CHAR(50).

   ALTER TABLE test_tbl2 MODIFY col2 CHAR(50);
   

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

   ALTER TABLE test_tbl2 MODIFY col1 DEFAULT 0;
   

5. Set the values of the col2 column in the test_tbl2 table to not allow NULL values.

   ALTER TABLE test_tbl2 MODIFY col2 NOT NULL;
   

6. View the schema of the test_tbl2 table.

   DESCRIBE test_tbl2;
   

   The return 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
   

Modify the column name

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

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

Here is an example:

1. Create a test table named test_tbl3.

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

2. Modify the name of the col1 column in the test_tbl3 table to col1_new.

   ALTER TABLE test_tbl3 RENAME COLUMN col1 TO col1_new;
   

3. View the schema of the test_tbl3 table.

   DESCRIBE test_tbl3;
   

   The return 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
   

Drop columns

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

Drop a single column

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

ALTER TABLE table_name DROP COLUMN column_name;

Here is an example:

1. Create a test table named test_tbl4.

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

2. Drop the col2 column from the test_tbl4 table.

   ALTER TABLE test_tbl4 DROP COLUMN col2;
   

3. View the schema of the test_tbl4 table.

   DESCRIBE test_tbl4;
   

   The return result is as follows:

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

Drop multiple columns

The SQL syntax for dropping 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, ...;

Here is an 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. Drop the col6 and col7 columns from the test_tbl5 table.

   ALTER TABLE test_tbl5 DROP (col6, col7);
   

3. Drop the col4 and col5 columns from the test_tbl5 table.

   ALTER TABLE test_tbl5 DROP COLUMN col4, DROP COLUMN col5;
   

4. View the schema of the test_tbl5 table.

   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
   

Purge abandoned columns

Abandoned columns still occupy physical storage space even though they are no longer in use. To remove these abandoned columns and reclaim the storage space, you need to purge them.

The SQL syntax for purging abandoned columns is as follows:

ALTER TABLE table_name FORCE;

Here is an example:

ALTER TABLE test_tbl5 FORCE;

Add a unique constraint

OceanBase Database allows you to add 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 ]...);

Here is an 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 to the col2 column of the test_tbl6 table.

   ALTER TABLE test_tbl6 ADD UNIQUE(col2);
   

3. View the schema of the test_tbl6 table.

   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

You can rename a table after the table is created.

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;

Here is an example:

ALTER TABLE test RENAME TO t1;

or

RENAME test TO t1;

Change the primary key and foreign key of a table

After a table is created, OceanBase Database allows you to add or drop the primary key and foreign key of the table. For more information about how to change the primary key and foreign key, see Define the constraint type for a column.

Modify the skip index attribute of a column

OceanBase Database allows you to use the ALTER TABLE statement to add, modify, and drop the skip index attribute.

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

Here is an example:

1. Run the following SQL statement to create a table named 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. Run the following SQL statement to 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. Run the following SQL statement to add the skip index attribute to a column after the table is created. Add the 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. Run the following SQL statement to drop the skip index attribute from a column after the table is created. Drop the skip index attribute from column col1 in the test_skidx table.

   ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
   

Conversion between row-based storage and columnar storage

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

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

• Convert a table to a columnar table:

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

• Remove the storage format of a table:

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

Parameter description:

• table_name: the name of the table.

• ADD COLUMN GROUP(all columns, each column): specifies to convert a table to a hybrid row-based and columnar storage table.

• ADD COLUMN GROUP(each column): specifies to convert a table to a columnar table.

• DELAYED: an optional parameter that specifies to asynchronously convert a row-based table to a columnar table. After this parameter is specified, the stored format in the table definition is modified, but the row-based to columnar conversion is actually performed when a major compaction is performed. This operation does not block current DML operations. It is an online DDL operation. If this parameter is not specified, it is an offline DDL operation, and the row-based to columnar conversion is performed synchronously.

  Notice

  • Currently, you can specify the DELAYED parameter only when you convert a row-based table to a columnar table (each column) or a hybrid row-based and columnar storage table (all columns, each column).
  • After the DELAYED statement is executed to asynchronously convert a row-based table to a columnar table, if the baseline data is not actually compacted before you query the data, the query performance may not meet your expectations because the data storage format does not actually change.

• DROP COLUMN GROUP(all columns, each column): specifies to remove the hybrid row-based and columnar storage format from a table.

• DROP COLUMN GROUP(all columns): specifies to remove the row-based storage format from a table.

• DROP COLUMN GROUP(each column): specifies to remove the columnar storage format from a table.

Convert a rowstore table to a columnstore table

Here is an example:

• Create a rowstore table by default.

  1. Create a rowstore table named tbl1.

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

  2. Convert the rowstore table tbl1 to a columnstore table.

     • Perform offline DDL operations as follows:

       ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
       

     • Perform online DDL operations as follows:

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

• Create a rowstore table by using the WITH COLUMN GROUP(all columns) option.

  1. Create a rowstore table named tbl1.

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

  2. Convert the rowstore table tbl1 to a columnstore table.

     • Perform offline DDL operations as follows:

       ALTER TABLE tbl1_ac ADD COLUMN GROUP(each column);
       

     • Perform online DDL operations as follows:

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

  3. Drop the rowstore format by using the DROP COLUMN GROUP(all columns) statement.

     ALTER TABLE tbl1_ac DROP COLUMN GROUP(all columns);
     

Convert a rowstore table to a hybrid rowstore and columnstore table

Here is an example:

1. Create a rowstore table named tbl2.

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

2. Convert the rowstore table tbl2 to a hybrid rowstore and columnstore table.

   • Perform offline DDL operations as follows:

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

   • Perform online DDL operations as follows:

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

Convert a hybrid rowstore and columnstore table to a columnstore table

Here is an example:

1. Create a hybrid rowstore and columnstore table named tbl3.

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

2. Convert the hybrid rowstore and columnstore table tbl3 to a columnstore table.

   ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
   

Convert a hybrid rowstore and columnstore table to a rowstore table

Here is an example:

1. Create a hybrid rowstore and columnstore table named tbl4.

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

2. Convert the hybrid rowstore and columnstore table tbl4 to a rowstore table.

   ALTER TABLE tbl4 DROP COLUMN GROUP(each column);
   

   or

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

References

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