Online DDL (Online DDL)
- Definition: During DDL operations, users can still access the database, including read and write operations.
- Advantages: It has minimal impact on business operations and allows DDL operations to be executed at runtime. It is typically used in scenarios requiring high availability.
- Features: Typically support concurrent access and require fewer locks, which can reduce downtime.
Offline DDL
- Definition: When performing DDL operations, the relevant tables are locked, preventing users from accessing the corresponding data in the database.
- Advantages: Simple and easy to implement, suitable for scenarios where the impact on business is minimal.
- Characteristics: DDL operations result in a certain degree of downtime, impacting normal queries and transaction processing.
Transaction affinity
- Transaction: A sequence of operations that either all succeed or all fail, ensuring data consistency throughout the transaction.
- Online DDL and offline DDL operations are typically separate, and DDL operations themselves may not be supported for transaction control in some database management systems (especially offline DDL).
Transaction waiting strategies
- Online DDL: Typically does not require waiting for the current transaction to end and can be executed concurrently.
- Offline DDL: Typically requires waiting for the current transaction to complete, as it locks the relevant tables during execution.
The following table describes the online DDL operations supported by OceanBase AI Database.
Category |
Action |
Time Consumed |
Remarks |
DDL Support after Creating a Materialized View Log (MLOG) |
|---|---|---|---|---|
| Index operations | Add Index | Depends on the data volume. Data reorganization is required. | It mainly involves global/local indexes, global indexes with partitions, and spatial indexes. | Supported |
| Index operations | Delete index | Related to whether there are active transactions | None | Supported |
| Index operations | Rename index | Only metadata needs to be modified | None | Supported |
| Index operations | Mixed Index Operations | Depends on the data volume. Index data needs to be populated. | For example, ALTER TABLE t1 ADD INDEX i4(c1), DROP INDEX i2, RENAME INDEX i1 TO i1x. |
Supported |
| Column operations | Add/Change/Delete Skip Index Type | Change only the table schema | None | Not supported |
| Column operations | Append Column | Only metadata needs to be modified | For example, to add LOB(TEXT) columns, ALTER TABLE tbl1 ADD c3 LOB. |
Supported |
| Column operations | Add a column in the middle (BEFORE/AFTER/FIRST) |
Depends on the data volume. Data reorganization is required (that is, the original data needs to be rewritten). | None | Supported |
| Column operations | Add VIRTUAL Column |
Only metadata needs to be modified | None | Not supported |
| Column operations | Modify column as NOT NULL |
Depends on the data volume. Data query is required. | None | Not supported |
| Column operations | Modify column as NULL |
Only metadata needs to be modified | None | Not supported |
| Column operations | Set column default value | Only metadata needs to be modified | None | Not supported |
| Column operations | Delete Column Default Value | Only metadata needs to be modified | None | Not supported |
| Column operations | Modify auto-increment column value | Only metadata needs to be modified | None | Not supported |
| Column operations | Rename column | Only metadata needs to be modified | None | Not supported |
| Column operations | Increase column type length or precision | Only metadata needs to be modified | For example, INT Length increase, VARCHAR Variable length. |
Not supported |
| Column operations | Hybrid Column Operation | Related to the operation with the longest duration | Single-column operations are online, and most mixed-column operations are online. To confirm whether a DDL operation is online, refer to the method described in the How to determine if a DDL operation is online (Online DDL) section below. | Not supported |
| Foreign key constraints | Adding a foreign key, CHECK/NOT NULL Constraints |
This depends on the data volume and whether data needs to be queried. | None | Adding foreign key constraints is supported, but adding CHECK/NOT NULL constraints is not supported. |
| Foreign key constraints | Drop foreign keys, CHECK/NOT NULL Constraints |
This depends on the data volume and whether data needs to be queried. | None | Foreign key constraints can be dropped, but CHECK/NOT NULL constraints cannot be dropped. |
| Table operations | Rename Table | Only metadata needs to be modified | None | Not supported |
| Table operations | Modify row format | Only metadata needs to be modified | None | Not supported |
| Table operations | Modify Block Size | Only metadata needs to be modified | None | Not supported |
| Table operations | Modify compression algorithm | Only metadata needs to be modified | None | Not supported |
| Table operations | Optimize tablespaces | Only metadata needs to be modified | None | Not supported |
| Table operations | Row-based storage to columnar storage conversion
NoticeAdding the |
Change only the table schema | None | Not supported |
| Table operations | Row-to-column conversion and hybrid row-column storage
NoticeAdding the |
Change only the table schema | None | Not supported |
| Partition operations | Add Partition | Only metadata needs to be modified | None | Not supported |
| Partition operations | Modify auto-partition attributes only | Only metadata needs to be modified | For example:
|
Not supported |
The following table describes the offline DDL operations supported by OceanBase AI Database.
Notice
- The DDL support for creating full-text indexes, multi-valued indexes, and vector indexes in the following table applies to V4.6.2 and later versions.
- Full-text, multi-valued, and vector indexes in shared storage mode do not support offline DDL.
- Offline DDL operations involving outrow data in LOB columns apply to V4.6.2.1 and later versions.
Category |
Action |
Time Consumed |
Remarks |
DDL Support after Creating a Materialized View Log (MLOG) |
DDL Support for Creating Full-Text, Multi-Valued, and Vector Indexes |
|---|---|---|---|---|---|
| Column operations | Reorder (BEFORE/AFTER/FIRST) |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Add an auto-increment column | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Modify to auto-increment column | Depends on the data volume. Data query is required. | None | Not supported | Supported |
| Column operations | Modify column type | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Modify a column to the primary key | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Add/Delete STORED Generated columns |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Delete Column | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | delete VIRTUAL Column |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Column operations | Hybrid Column Operation | If there are offline column operations, they will be upgraded to offline DDL. | None | Not supported | Supported |
| Column operations | Modify decimal Accuracy of the data type |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Primary Key Operations | Add/Delete Primary Key | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | TRUNCATE Table |
Related to whether there are active transactions | None | Not supported | Supported |
| Table operations | Convert character set | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Drop Table | Related to whether there are active transactions | None | Not supported | Supported |
| Table operations | Row-to-column storage conversion
NoticeThe |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Row-store to row-column hybrid storage conversion
NoticeThe |
Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Convert columnar storage to row-based storage | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Columnar to hybrid columnar-row storage | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Hybrid Row-Column Storage to Columnar Storage | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Table operations | Row-column hybrid storage to row storage | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Partition operations | Modify partitioning rule | Depends on the data volume. Data reorganization is required. | None | Not supported | Supported |
| Partition operations | Delete partition | Related to whether there are active transactions | Acquires a partition-level table lock on the partition. | Not supported | Supported |
| Partition operations | TRUNCATE partition |
Related to whether there are active transactions | Acquires a partition-level table lock on the partition. | Not supported | Supported |
| Partition operations | Partition exchange | Related to whether there are active transactions | Acquires a partition-level table lock on the partition. | Not supported | Not supported |
| Partition operations | Manual Partition Split | Depends on the data volume. Data reorganization is required. | None | Not supported | Not supported |
| Partition operations | Modify auto-partition attributes and partitioning rules | Depends on the data volume | For example: ALTER TABLE t1 PARTITION BY RANGE(xxx) SIZE('xxx') (PARTITION...); |
Not supported | Supported |
How to determine if a DDL operation is online (Online DDL)
In OceanBase AI Database, the online or offline status of DDL operations has a significant impact on business operations. This topic describes how to determine whether a DDL operation is an Online DDL, and provides specific steps and considerations. For some operations, such as column type changes or hybrid DDL operations, it is uncertain whether they are Online DDLs before execution. It is recommended to verify and determine this first using the following methods.
How to determine if a DDL operation is online
For some operations with a wide scope, it is not possible to categorize all DDL operations as either online or offline. Therefore, we recommend that users, when performing mixed DDL operations, explicitly determine whether the operation is online.
Offline DDL principle:
Offline DDL operations in OceanBase AI Database are performed by "rebuilding a table". Specifically, offline DDL creates a temporary hidden table (invisible to users) and migrates the data from the original table to the new table. After the data migration is complete, the temporary table is renamed to the name of the original table, and the old table is deleted. Therefore, after an offline DDL operation is completed, the table_id changes. Note that DML operations are not allowed during DDL execution.
Using this principle, you can determine whether a DDL operation is an online DDL.
Detection method:
Execute the following SQL statements before and after the DDL operation to check if the table_id changes. If it does not change, the operation is an Online DDL; if it changes, the operation is an Offline DDL.
select distinct(table_id) from oceanbase.DBA_OB_TABLE_LOCATIONS where table_name='xxx';
Procedure:
Use the following procedure to determine whether a DDL operation is an Online DDL.
Create an empty table for verification:
CREATE TABLE t10(a INT, b INT);Check the current
table_id:SELECT distinct(table_id) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name='t10';Perform the expected DDL operation:
ALTER TABLE t10 ADD c INT, ADD CONSTRAINT c_idx UNIQUE(c);Check the
table_idagain:SELECT distinct(table_id) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name='t10';If the
table_idreturned in step 2 and step 4 is the same, the DDL operation is online; if thetable_idhas changed, the corresponding DDL operation is offline.
Note
This detection method is invalid for table operations and partition operations.