Online DDL and offline DDL operations

Online DDL

• Definition: During DDL operations, users can still access the database for reading and writing.
• Advantages: Minimal impact on business operations, allows DDL operations while the system is running, suitable for high-availability scenarios.
• Characteristics: Typically supports concurrent access and fewer locks, reducing downtime.

Offline DDL

• Definition: During DDL operations, the relevant tables are locked, preventing user access to the database's data.
• Advantages: Simple and easy to implement, suitable for scenarios with minimal business impact.
• Characteristics: Causes some downtime during DDL operations, affecting normal queries and transaction processing.

Transactional Consistency

• Transaction: A set of operations that either all succeed or all fail, ensuring data consistency during execution.
• Online DDL and offline DDL operations are typically independent, and DDL operations may not be transactionally controlled in some database management systems (especially offline DDL).

Transaction waiting strategy

• Online DDL: Usually, you do not need to wait for the current transaction to end. It can be executed concurrently.
• Offline DDL: Usually, you need to wait for the current transaction to end. This is because the relevant tables are locked during execution.

The following table lists the Online DDL operations supported by seekdb.

Category	Operation	Time Consumption	Remarks	DDL Support After Creating a Materialized View Log (mlog)
Index Operations	Add Index	Depends on the data volume. Data reorganization is required.	Mainly involves global and local indexes, global indexes with partitions, and spatial indexes.	Supported
Index Operations	Drop Index	Depends on whether there are active transactions.	N/A	Supported
Index Operations	Rename Index	Only metadata modification is required.	N/A	Supported
Index Operations	Mixed Index Operations	Depends on the data volume. Index data completion is required.	For example, ALTER TABLE t1 ADD INDEX i4(c1), DROP INDEX i2, RENAME INDEX i1 TO i1x.	Supported
Column Operations	Add/Modify/Delete Skip Index Type	Only table schema modification is required.	N/A	Not supported
Column Operations	Add Column at the End	Only metadata modification is required.	For example, adding a LOB (TEXT) column, such as ALTER TABLE tbl1 ADD c3 LOB.	Supported
Column Operations	Add Column in the Middle (BEFORE/AFTER/FIRST)	Depends on the data volume. Data reorganization is required.		Supported
Column Operations	Add VIRTUAL Column	Only metadata modification is required.	N/A	Not supported
Column Operations	Modify Column to NOT NULL	Depends on the data volume. Data query is required.	N/A	Not supported
Column Operations	Modify Column to NULL	Only metadata modification is required.	N/A	Not supported
Column Operations	Set Column Default Value	Only metadata modification is required.	N/A	Not supported
Column Operations	Remove Column Default Value	Only metadata modification is required.	N/A	Not supported
Column Operations	Modify Auto-Increment Column Value	Only metadata modification is required.	N/A	Not supported
Column Operations	Rename Column	Only metadata modification is required.	N/A	Not supported
Column Operations	Increase Column Type Length or Precision	Only metadata modification is required.	For example, increasing the length of INT, extending VARCHAR, or converting NUMBER types.	Not supported
Column Operations	Mixed Column Operations	Depends on the longest time-consuming operation.	For individual column operations, Online DDL is supported. For mixed column operations, most are Online DDL. To confirm whether an operation is Online DDL, refer to How to Determine Whether a DDL Operation is Online DDL in the following section.	Not supported
Foreign Key Constraint Operations	Add Foreign Key, CHECK, or NOT NULL Constraint	Depends on the data volume. Data query is required.	N/A	Supported for adding foreign key constraints, not supported for adding CHECK or NOT NULL constraints
Foreign Key Constraint Operations	Drop Foreign Key, CHECK, or NOT NULL Constraint	Depends on the data volume. Data query is required.	N/A	Supported for dropping foreign key constraints, not supported for dropping CHECK or NOT NULL constraints
Table Operations	Rename Table	Only metadata modification is required.	N/A	Not supported
Table Operations	Modify Row Format	Only metadata modification is required.	N/A	Not supported
Table Operations	Modify Block Size	Only metadata modification is required.	N/A	Not supported
Table Operations	Modify Compression Algorithm	Only metadata modification is required.	N/A	Not supported
Table Operations	Optimize Tablespace	Only metadata modification is required.	N/A	Not supported
Table Operations	Convert Rowstore to Columnstore :::tip Add the DELAYED keyword when you execute the ALTER TABLE statement. This is Online DDL. ::: .	Only table schema modification is required.	N/A	Not supported
Table Operations	Convert Rowstore to Hybrid Rowstore/Columnstore :::tip Add the DELAYED keyword when you execute the ALTER TABLE statement. This is Online DDL. ::: .	Only table schema modification is required.	N/A	Not supported
Partition Operations	Add Partition	Only metadata modification is required.	N/A	Not supported
Partition Operations	Modify Automatic Partitioning Attributes	Only metadata modification is required.	For example: ALTER TABLE t1 PARTITION BY RANGE() SIZE('xxx'); ALTER TABLE t1 PARTITION BY RANGE(); ALTET TABLE t1 PARTITION BY RANGE() SIZE('ulimited'); ALTER TABLE t1 PARTITION BY RANGE (xxx) SIZE('xxx');	Not supported

The following table lists the Offline DDL operations supported by seekdb.

Category	Operation	Time Consumed	Remarks	DDL Support After Creating a Materialized Log (Mlog)	DDL Support After Creating a Full-Text/Multivalued/Vector Index
Column Operation	BEFORE/AFTER/FIRST	Related to the amount of data, and data reorganization is required.		Not supported	Supported
Column Operation	Add an auto-increment column	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Change a column to an auto-increment column	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Change the column type	Related to the amount of data, and data reorganization is required.		Not supported	Supported
Column Operation	Change a column to a primary key	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Add or drop a STORED generated column	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Drop a column	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Drop a VIRTUAL column	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Column Operation	Mixed column operations	If offline column operations are performed, they will be upgraded to offline DDL.	None	Not supported	Supported
Primary Key Operation	Add or drop a primary key	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	TRUNCATE a table	Related to whether there are active transactions.	None	Not supported	Supported
Table Operation	Change the character set	Related to the amount of data, and data reorganization is required.		Not supported	Supported
Table Operation	Drop a table	Related to whether there are active transactions.	None	Not supported	Supported
Table Operation	Convert a rowstore table to a columnstore table :::tip If the DELAYED option is not specified in the ALTER TABLE statement, the operation is offline DDL. ::: .	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	Convert a rowstore table to a row-column hybrid table :::tip If the DELAYED option is not specified in the ALTER TABLE statement, the operation is offline DDL. ::: .	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	Convert a columnstore table to a rowstore table	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	Convert a columnstore table to a row-column hybrid table	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	Convert a row-column hybrid table to a columnstore table	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Table Operation	Convert a row-column hybrid table to a rowstore table	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Partition Operation	Modify the partitioning rule	Related to the amount of data, and data reorganization is required.	None	Not supported	Supported
Partition Operation	Drop a partition	Related to whether there are active transactions.	A table lock at the partition level is added to the partition.	Not supported	Supported
Partition Operation	TRUNCATE a partition	Related to whether there are active transactions.	A table lock at the partition level is added to the partition.	Not supported	Supported
Partition Operation	Partition exchange	Related to whether there are active transactions.	A table lock at the partition level is added to the partition.	Not supported	Not supported
Partition Operation	Manually split a partition	Related to the amount of data, and data reorganization is required.	None	Not supported	Not supported
Partition Operation	Modify the auto-partitioning attributes and partitioning rule	Related to the amount of data.	For example: ALTER TABLE t1 PARTITION BY RANGE(xxx) SIZE('xxx') (PARTITION...);	Not supported	Supported

How to determine whether a DDL operation is an online DDL

In seekdb, whether a DDL operation is online or offline has a significant impact on business operations. This topic describes how to determine whether a DDL operation is an online DDL, and provides the operation steps and considerations. For some DDL operations, such as column type changes or hybrid DDL operations, it is uncertain whether they are online DDL before execution. We recommend that you verify the operations by using the following methods.

Methods for determining whether a DDL operation is an online DDL

Some DDL operations cover a wide range of operations and cannot be completely listed as online or offline DDL. We recommend that you determine whether a DDL operation is online when it involves multiple DDL operations.

Principle of offline DDL:

Offline DDL operations in seekdb use the "rebuild table" method. Specifically, an offline DDL operation creates a temporary hidden table (which is invisible to users), and then migrates the data from the original table to the new table. After the data migration is completed, the temporary table is renamed to the original table name, and the original table is deleted. Therefore, after an offline DDL operation is completed, the table_id changes. During the execution of DDL operations, DML operations are not allowed.

Based on this principle, you can determine whether a DDL operation is an online DDL.

Method:

Run the following SQL statements before and after the DDL operation to check whether the table_id changes. If the table_id does not change, the DDL operation is an online DDL. If the table_id changes, the DDL operation is an offline DDL.

select distinct(table_id) from oceanbase.DBA_OB_TABLE_LOCATIONS where table_name='xxx';

Procedure:

Determine whether a DDL operation is an online DDL by following the procedure below.

1. Create an empty table for verification:

   CREATE TABLE t10(a INT, b INT);

2. View the current table_id:

   SELECT distinct(table_id)
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE table_name='t10';

3. Execute the expected DDL operation:

   ALTER TABLE t10 ADD c INT, ADD CONSTRAINT c_idx UNIQUE(c);

4. View the table_id again:

   SELECT distinct(table_id)
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE table_name='t10';

5. If the table_id values returned in steps 2 and 4 are the same, the DDL operation is an online DDL. If the table_id values are different, the DDL operation is an offline DDL.

info

This method cannot be used for table operations or partition operations.