The following table describes the online DDL operations supported by the MySQL mode of OceanBase Database V4.x.
| Type | Operation | Time spent | Remarks |
|---|---|---|---|
| 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 (supported in OceanBase Database V4.0.0 and later), and spatial indexes (supported in OceanBase Database V4.2.0 and later). |
| Index operation | Drop an index | Related to whether active transactions exist | N/A |
| Index operation | Rename an index | Only for metadata modification | N/A |
| Index operation | Hybrid index operations | Related to the operation with the longest execution time | For example, ALTER TABLE t1 ADD INDEX i4(c1), DROP INDEX i2, RENAME INDEX i1 TO i1x involves hybrid index operations. |
| 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 similar to ALTER TABLE tbl1 ADD c3 LOB. |
| Column operation | Add a VIRTUAL column |
Only for metadata modification | N/A |
| Column operation | Drop a VIRTUAL column |
Only for metadata modification | N/A |
| Column operation | Set a NOT NULL constraint on a column |
Related to the data volume, because data is queried | N/A |
| Column operation | Set a NULL constraint on a column |
Only for metadata modification | N/A |
| Column operation | Set a default value for a column | Only for metadata modification | N/A |
| Column operation | Drop the default value of a column | Only for metadata modification | N/A |
| Column operation | Change the value of an auto-increment column | Only for metadata modification | N/A |
| Column operation | Rename a column | Only for metadata modification | N/A |
| 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. |
| Column operation | Hybrid column operations | Related to the operation with the longest execution time | If an offline column operation is involved, the operation is upgraded to an offline DDL operation. |
| Foreign key operation | Add a foreign key or a CHECK/NOT NULL constraint |
Related to the data volume, because data is queried | N/A |
| Foreign key operation | Drop a foreign key or a CHECK/NOT NULL constraint |
Related to the data volume, because data is queried | N/A |
| Table operation | Rename a table | Only for metadata modification | N/A |
| Table operation | Change the row format | Only for metadata modification | N/A |
| Table operation | Change the block size | Only for metadata modification | N/A |
| Table operation | Switch the compression algorithm | Only for metadata modification | N/A |
| Table operation | Optimize a tablespace | Only for metadata modification | N/A |
| Partition operation | Add a partition | Only for metadata modification | N/A |
The following table describes the offline DDL operations supported by the MySQL mode of OceanBase Database V4.x.
| Type | Operation | Time spent | Remarks |
|---|---|---|---|
| Column operation | Add a column (BEFORE/AFTER/FIRST) |
Related to the data volume, because data is reorganized | This operation is not supported in Oracle mode. |
| Column operation | Reorder columns (BEFORE/AFTER/FIRST) |
Related to the data volume, because data is reorganized (or rewritten) | This operation is not supported in Oracle mode. |
| Column operation | Add an auto-increment column | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Column operation | Change a column to an auto-increment column | Related to the data volume, because data is queried | N/A |
| Column operation | Change the type of a column | Related to the data volume, because data is reorganized (or rewritten) | This operation is not supported in Oracle mode. |
| Column operation | Set a column as a primary key | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Column operation | Add or drop a STORED column |
Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Column operation | Drop a column | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Column operation | Hybrid column operations | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Primary key operation | Add or drop a primary key | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Table operation | Truncate a table | Related to whether active transactions exist | N/A |
| Table operation | Change the character set | Related to the data volume, because data is reorganized (or rewritten) | This operation is not supported in Oracle mode. |
| Table operation | Drop a table | Related to whether active transactions exist | N/A |
| Partition operation | Modify partitioning rules | Related to the data volume, because data is reorganized (or rewritten) | N/A |
| Partition operation | Drop a partition | Related to whether active transactions exist | A partition-level table lock is added to the partition. |
| Partition operation | Truncate a partition | Related to whether active transactions exist | A partition-level table lock is added to the partition. |