Parallel DDL

OceanBase Database supports parallel DDL operations to improve the execution efficiency of DDL statements.

Configure parallel DDL

In OceanBase Database, you can flexibly control the parallel DDL feature by configuring parameters:

• _enable_parallel_table_creation: controls parallel CREATE TABLE operations.

• _parallel_ddl_control: controls the following parallel operations:

  • TRUNCATE TABLE
  • DROP TABLE
  • CREATE INDEX
  • CREATE VIEW
  • COMMENT
  • ADD_COLUMN
  • DROP_COLUMN
  • ADD_PARTITION
  • ADD_SUB_PARTITION
  • DROP_PARTITION
  • DROP_SUB_PARTITION
  • TRUNCATE_PARTITION
  • TRUNCATE_SUB_PARTITION

_enable_parallel_table_creation

_enable_parallel_table_creation specifies whether to enable concurrent table creation (CREATE TABLE) at the tenant level.

Examples

• Execute the following statement in the sys tenant to disable concurrent table creation for the user tenant mysql001.

  ALTER SYSTEM SET _enable_parallel_table_creation = false tenant=mysql001;
  

• Execute the following statement in the user tenant to disable concurrent table creation.

  MySQL-compatible mode:

  ALTER SYSTEM SET _enable_parallel_table_creation = false;
  

  Oracle-compatible mode:

  Note

  In Oracle-compatible mode, parameters are enclosed in double quotes by default.

  ALTER SYSTEM SET "_enable_parallel_table_creation" = false;
  

_parallel_ddl_control

_parallel_ddl_control specifies whether to enable parallel DDL operations at the tenant level. This parameter takes effect only when the corresponding DDL operation is performed on the tenant.

DDL type information:

Examples

• Execute the following statement in the sys tenant to disable parallel TRUNCATE TABLE and enable parallel CREATE INDEX for the user tenant mysql001.

  ALTER SYSTEM SET _parallel_ddl_control = 'CREATE_INDEX:ON, TRUNCATE_TABLE:OFF' tenant = 'mysql001';
  

• Execute the following statement in the user tenant to disable parallel TRUNCATE TABLE and enable parallel CREATE INDEX.

  MySQL-compatible mode:

  ALTER SYSTEM SET _parallel_ddl_control = 'CREATE_INDEX:ON, TRUNCATE_TABLE:OFF';
  

  Oracle-compatible mode:

  Note

  In Oracle-compatible mode, double quotes are required around configuration parameters that are specified implicitly in the syntax.

  ALTER SYSTEM SET "_parallel_ddl_control" = 'CREATE_INDEX:ON, TRUNCATE_TABLE:OFF';
  

Default DDL parallelism

In OceanBase Database, you can use the session-level parameter _force_parallel_ddl_dop to specify the default parallelism for DDL operations executed in parallel.

When using the parallel DDL feature, you can pre-set the parallelism for DDL operations executed in parallel via _force_parallel_ddl_dop.

Example

Execute the following statement in the user tenant to set the default parallelism for DDL operations executed in parallel at the session level to 16.

MySQL-compatible mode:

SET _FORCE_PARALLEL_DDL_DOP = 16;

Oracle-compatible mode:

SET "_FORCE_PARALLEL_DDL_DOP" = 16;