Online DDL and Related Operations

During traditional DDL operations, tables cannot provide read and write services. As a result, services may be interrupted for a long time. Online DDL operations have little impact on running services, improving service continuity and database availability.

By using the ONLINE keyword, you can perform the following online DDL operations: Change column types and precisions, reduce lengths, increase the CHAR type lengths, move tablespaces at the table level, add NOT NULL, CHECK, PRIMARY KEY, and UNIQUE constraints, and perform VACUUM FULL. An exclusive lock is temporarily added to online DDL operations only in the early and later phases of the execution and does not block concurrent read and write services for a long time. For details, see ALTER TABLE and VACUUM.

Table 1 lists the supported online DDL operations.

**Table 1** Online DDL operations supported by the current version
Category	Operation	Required Time	Remarks
Column operations	The supported operations are as follows: Add columns, that is, set default values of columns to an expression. Drop columns. Rename columns. Set and drop default values of columns. Change column lengths. Change column precisions. Change column types. Change definitions of ENUM/SET columns.	When data needs to be reorganized, the time required depends on the data volume. When only metadata is changed, the time required is irrelevant to the data volume.	To change column types or precisions, reduce lengths, or increase CHAR type lengths, you need to use the ONLINE keyword.
Table operations	The supported operations are as follows: Move tablespaces online at the table level. Rename tables. Specify character sets. Delete tables.	When data needs to be moved, the time required depends on the data volume. When only metadata is changed, the time required is irrelevant to the data volume.	To move tablespaces online at the table level, you need to use the ONLINE keyword.
Tablespace operations	The supported operations are as follows: Rename tablespaces. Change owners of tablespaces. Delete tablespaces.	When only metadata is changed, the time required is irrelevant to the data volume.	-
Constraint operations	The supported operations are as follows: Add NOT NULL, CHECK, UNIQUE, and PRIMARY KEY constraints. Set a column constraint to NULL.	When data needs to be queried, the time required depends on the data volume. When only metadata is changed, the time required is irrelevant to the data volume.	To add NOT NULL, CHECK, UNIQUE, and PRIMARY KEY constraints, you need to use the ONLINE keyword.
Partition operations	Add, delete, and truncate partitions, enable or disable row movement, and invalidate partitioned indexes.	When only metadata is changed, the time required is irrelevant to the data volume.	-
Recycling storage space	VACUUM FULL	When data needs to be reorganized, the time required depends on the data volume.	To recycle storage space, you need to use the ONLINE keyword.
Index operations	Build and rebuild the CONCURRENTLY index.	When data needs to be queried, the time required depends on the data volume.	For details about index operations, see CREATE INDEX and REINDEX.