Overview

OceanBase Database allows you to insert data into a database by using direct load. In other words, OceanBase Database allows you to directly write data to data files. Direct load allows you to bypass the SQL layer and directly allocate space and insert data into data files, thereby improving the data import efficiency.

Scenarios

You can use direct load in the following scenarios:

• Data migration and synchronization. In data migration and synchronization, you usually need to migrate a large amount of data of various formats from different data sources to OceanBase Database. The performance of the traditional SQL interface may not meet the timeliness requirements.

• Traditional ETL. After data is extracted and transformed in the source, a large amount of data needs to be loaded to the destination in a short time. Direct load can improve the data import performance. During the ETL process, direct load can also improve the efficiency.

• Load data from text files or other data sources to OceanBase Database. Direct load can improve the data loading efficiency.

Supported statements

OceanBase Database allows you to use the following statements to perform direct load:

• LOAD DATA

• INSERT INTO SELECT

Limitations

• The following limitations apply to the LOAD DATA statement:

  • Only one LOAD DATA statement can be used to operate on the same table at a time, because a table lock is obtained before the operation.
  • The LOAD DATA statement cannot be used in a trigger.
  • The LOAD DATA statement cannot be used in a transaction that contains multiple operations.

• The following limitations apply to the INSERT INTO SELECT statement:

  • Only parallel data manipulation language (PDML) data is supported for direct load. Data in other formats cannot be directly loaded.

  • You cannot write data to the same table by using two statements at a time, because a table lock is obtained before the operation.

  • The INSERT INTO SELECT statement cannot be used in a trigger.

  • The INSERT INTO SELECT statement cannot be used in a multi-operation transaction.

Considerations

Direct load writes all existing data. If the original table contains a large amount of data, but only a small amount of data needs to be imported, direct load may not be suitable.