The following table describes the online DDL operations supported by OceanBase Database V4.x in Oracle mode.
| Type | Operation | Time spent | Remarks | DDL support after creating mlog |
|---|---|---|---|---|
| Index operation | Add an index | Related to the data volume, because data is reorganized (or rewritten) | This operation mainly involves global indexes, local indexes, global indexes with specified partitions, and spatial indexes (supported in OceanBase Database V4.1.0 and later). | Supported |
| Index operation | Drop an index | Related to whether active transactions exist | N/A | Supported |
| Index operation | Rename an index | Only for metadata modification | N/A | Supported |
| Index operation | Hybrid index operations | Related to the operation with the longest execution time | For example, the ALTER TABLE t1 ADD INDEX i4(c1), DROP INDEX i2, RENAME INDEX i1 TO i1x syntax is not supported in Oracle mode. |
Not supported |
| Column operation | Add, change, or delete the skip index type | Only for table schema modification | N/A | Not supported |
| Column operation | Add a column to the end of a table | Only for metadata modification | For example, add a LOB (TEXT) column by executing a statement such as ALTER TABLE tbl1 ADD c3 LOB. |
Not supported |
| Column operation | Add a virtual column | Only for metadata modification | N/A | Not supported |
| Column operation | Drop a virtual column | Only for metadata modification | N/A | Not supported |
| Column operation | Set a NOT NULL constraint on a column |
Related to the data volume, because data is queried | N/A | Not supported |
| Column operation | Set a NULL constraint on a column |
Only for metadata modification | N/A | Not supported |
| Column operation | Set a default value for a column | Only for metadata modification | N/A | Not supported |
| Column operation | Drop the default value of a column | Only for metadata modification | N/A | Not supported |
| Column operation | Change the value of an auto-increment column | Only for metadata modification | N/A | Not supported |
| Column operation | Rename a column | Only for metadata modification | N/A | Not supported |
| Column operation | Increase the length or precision of the data type for a column | Only for metadata modification | For example, increase the length of the INT type, increase the length of the VARCHAR type, or convert the NUMBER type. |
Not supported |
| Column operation | Hybrid column operations | Related to the operation with the longest execution time | All single-column operations and most hybrid column operations are online DDL operations. For information about how to determine whether a DDL operation is an online operation, see the Determine whether a DDL operation is an online operation section in this topic. | Not supported |
| FOREIGN KEY constraint operation | Add a FOREIGN KEY, CHECK, or NOT NULL constraint | Related to the data volume, because data is queried | N/A | You can add a FOREIGN KEY or CHECK constraint, but not a NOT NULL constraint. |
| FOREIGN KEY constraint operation | Drop a FOREIGN KEY, CHECK, or NOT NULL constraint | Related to the data volume, because data is queried | N/A | You can drop a FOREIGN KEY constraint but not a CHECK or NOT NULL constraint. |
| Table operation | Rename a table | Only for metadata modification | N/A | Not supported |
| Table operation | Change the row format | Only for metadata modification | N/A | Not supported |
| Table operation | Change the block size | Only for metadata modification | N/A | Not supported |
| Table operation | Change the compression algorithm | Only for metadata modification | N/A | Not supported |
| Table operation | Optimize a tablespace | Only for metadata modification | N/A | Not supported |
| Partition operation | Add a partition | Only for metadata modification | N/A | Not supported |
The following table describes the offline DDL operations supported by OceanBase Database V4.x in Oracle mode.
| Type | Operation | Time spent | Remarks | DDL support after mlog creation |
|---|---|---|---|---|
| Column operation | Add an auto-increment column | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Column operation | Set a column as the primary key | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Column operation | Add or drop a STORED generated column |
Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Column operation | Drop a column | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Column operation | Hybrid column operations | If an offline column operation is involved, the operation is upgraded to an offline DDL operation. | N/A | Not supported |
| Primary key operation | Add or drop a primary key | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Truncate a table | Related to whether active transactions exist | N/A | Not supported |
| Table operation | Drop a table | Related to whether active transactions exist | N/A | Not supported |
| Table operation | Convert a rowstore table into a columnstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Convert a rowstore table into a hybrid rowstore-columnstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Convert a columnstore table into a rowstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Convert a columnstore table into a hybrid rowstore-columnstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Convert a hybrid rowstore-columnstore table into a columnstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Table operation | Convert a hybrid rowstore-columnstore table into a rowstore table | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Partition operation | Modify partitioning rules | Related to the data volume, because data is reorganized (or rewritten) | N/A | Not supported |
| Partition operation | Drop a partition | Related to whether active transactions exist | A partition-level table lock is added to the partition. | Not supported |
| Partition operation | Truncate a partition | Related to whether active transactions exist | A partition-level table lock is added to the partition. | Not supported |
| Partition operation | Exchange a partition | Related to whether active transactions exist | A partition-level table lock is added to the partition. | Not supported |
Determine whether a DDL operation is an online operation
In OceanBase Database, the online/offline attribute of a DDL operation has significant impact on business. This section describes how to determine whether a DDL operation is an online operation. For some DDL operations, such as changing the type of a column or a hybrid DDL operation, we recommend that you first determine whether the operation is an online DDL operation.
Method for determining whether a DDL operation is an online operation
It is impossible to list all online and offline operations. Before you perform a hybrid DDL operation, we recommend that you first determine whether the hybrid DDL operation is an online operation.
Technical mechanism of offline DDL operations:
In OceanBase Database, an offline DDL operation is performed in double write mode. Specifically, the system creates a temporary hidden table for double write, synchronizes the data from the original table to the new table in the background, renames the new table, and deletes the original table. Therefore, after an offline DDL operation is completed, the table ID changes. This way, you can determine whether a DDL operation is an online operation.
Method:
Execute the following SQL statement before and after a DDL operation is performed. If the table ID does not change, the DDL operation is an online operation. If the table ID changes, the DDL operation is an offline operation.
select distinct(table_id) from DBA_OB_TABLE_LOCATIONS where table_name='xxx';
Procedure:
Perform the following steps to determine whether a DDL operation is an online operation:
Create an empty table for verification.
CREATE TABLE t10(a INT, b INT);Query the current table ID.
SELECT distinct(table_id) FROM DBA_OB_TABLE_LOCATIONS WHERE table_name='t10';Perform a DDL operation.
ALTER TABLE t10 ADD c INT, ADD CONSTRAINT c_idx UNIQUE(c);Query the table ID again.
SELECT distinct(table_id) FROM DBA_OB_TABLE_LOCATIONS WHERE table_name='t10';If the table IDs returned in Step 2 and Step 4 are the same, the DDL operation is an online operation. If they are different, the DDL operation is an offline operation.
Note
You cannot use this method to determine whether a table or partition operation is an online DDL operation.