Online and offline DDL operations

Online DDL

• Definition: Online DDL allows users to access the database, including reading and writing, during DDL operations.
• Advantages: It has minimal impact on business operations and can be executed while the system is running, making it suitable for high-availability scenarios.
• Characteristics: It typically supports concurrent access and less locking, reducing downtime.

Offline DDL

• Definition: Offline DDL locks the relevant tables during operations, preventing users from accessing the database's related data.
• Advantages: It is simple to implement and suitable for scenarios where the impact on business operations is minimal.
• Characteristics: It can cause downtime during DDL operations, affecting normal queries and transaction processing.

Transactional DDL

• Transaction: A transaction is a set of operations that either all succeed or all fail, ensuring data consistency during execution.
• Online DDL and offline DDL operations are typically separate. In some database management systems, DDL operations themselves may not be supported by transactions (especially offline DDL).

Transaction wait strategy

• Online DDL: Usually does not require waiting for the current transaction to complete and can be executed concurrently.
• Offline DDL: Usually requires waiting for the current transaction to complete, as the relevant tables are locked during execution.

The following table lists the Online DDL operations supported in Oracle mode of OceanBase Database V4.x.

The following table lists the Offline DDL operations supported in Oracle mode of OceanBase Database V4.x.

How to determine whether a DDL operation is online

In OceanBase Database, whether a DDL operation is online or offline significantly impacts business operations. This topic describes how to determine whether a DDL operation is online and provides specific steps and considerations. For operations such as column type changes or mixed DDL operations, it is recommended to verify whether they are online before execution.

Methods to determine whether a DDL operation is online

Some DDL operations cover a wide range of operations and cannot be fully listed as online or offline. Therefore, it is recommended to verify whether the operation is online when mixed DDL operations are involved.

Principle of offline DDL operations:

Offline DDL operations in OceanBase Database use the "rebuild table" method. Specifically, an offline DDL operation creates a temporary hidden table (invisible to users), migrates data from the original table to the new table, renames the temporary table to the original table name, and deletes the original table. Therefore, after an offline DDL operation, the table_id changes, and no DML operations are allowed during the DDL operation.

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

Method:

Execute the following SQL statements before and after the DDL operation to check whether the table_id changes. If it does not change, the DDL operation is online; if it changes, the DDL operation is offline.

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

Steps:

Follow the steps below to determine whether a DDL operation is online.

1. Create an empty table for verification:

   CREATE TABLE t10(a INT, b INT);
   

2. Check the current table_id:

   SELECT distinct(table_id)
   FROM 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. Check the table_id again:

   SELECT distinct(table_id)
   FROM 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 online; if the table_id changes, the DDL operation is offline.