Column type change rules

When changing the column type in OceanBase Database in Oracle mode, you must first consider the column type change rules at the table level, especially the scenarios where some data types are disabled. Then, consider the rules for changing the source data type to the target data type.

Rules for changing column types at the table level

Disabled rules

• Foreign key constraints

  For Online DDL and Offline DDL, only the VARCHAR, VARCHAR2, and NVARCHAR2 data types can be changed in precision.

• Trigger constraints

  For Offline DDL, when a column has a trigger constraint that is not DISABLE, changing the column type is not allowed. If all triggers are DISABLE, the DDL operation is not affected, and the new table must include the DISABLE trigger.

• Partitioning key

  For Online DDL and Offline DDL, changing the type or length of a column that is part of the partitioning key is not allowed.

• For Online DDL and Offline DDL, if the column being changed is referenced by a generated column, changing the column type is not allowed.

Change rules

• When the data type remains the same but the length increases, such as changing a column from CHAR to CHAR(11), OceanBase Database can perform this operation online without rewriting data, provided that the column only has an index table. However, if the column is modified as a primary key or partitioning key, an offline operation is required. Additionally, if the change affects CHECK constraints due to expected data length changes, an offline operation is also required. This is summarized as: "Changing the length of a column of the same type with CHECK constraints, primary key, or partitioning key requires an offline operation."

  For VARCHAR type length changes, such as LENGTH(c1), the operation remains unchanged, so it can be performed online. However, the schema of related dependent objects like index tables must also be modified. Note that even if the primary key or partitioning key is modified, it can still be done online, summarized as: "If the user expects no data changes, but the schema of dependent objects must be modified."

• In OceanBase Database, changes to large data types (numerical, character, time, and ROWID) can be performed online if they are not referenced by index tables, foreign keys, CHECK constraints, or primary keys. However, if referenced by these dependent objects, an offline operation is required. This is summarized as: "Changing large data types with dependent objects requires an offline operation."

• For numerical data types, if there is a change between SIGNED and UNSIGNED, an offline operation is required when type changes are allowed.

Column conversion rules

In OceanBase Database, all column data types are categorized into five major types: numeric, character, temporal, interval, and ROWID.

In the current version of OceanBase Database, both ROWID and UROWID types are internally implemented as UROWID. Therefore, the conversion rules between ROWID and UROWID are the same as those between UROWID and UROWID.

In OceanBase Database's Oracle mode, conversions between major data types are not allowed. Therefore, we focus only on conversions within each major data type.

Conversion within the numeric data types

Numeric data types are categorized as follows:

• NUMBER

• FLOAT

  Notice

  FLOAT(p) is not a floating-point number. It is a subtype of the NUMBER data type. The binary precision range of FLOAT(p) is [1, 126].

  • BINARY_FLOAT
  • BINARY_DOUBLE

The following table describes the conversion between numeric data types.

Conversion between character data types

Character data types are divided into the following categories:

• CHAR

• VARCHAR and VARCHAR2: These are the same data type.

• NCHAR and NVARCHAR2: All characters are represented using two bytes.

• BLOB: The character set is BINARY.

• CLOB: The character set is UTF8MB4.

• RAW: Characters are stored in binary format regardless of the character set.

The following table describes the conversion between character data types.

Conversion within the datetime data types

The datetime data types are divided as follows:

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

The following table describes the conversion within the datetime data types.

Conversion within the interval data types

The interval data types are divided as follows:

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

The following table describes the conversion within the interval data types.

Conversion within the ROWID data types

The ROWID data types are divided as follows:

• ROWID

• UROWID

The following table describes the conversion within the ROWID data types.