Full direct load|V4.3.5| docs|Distributed Database

Full direct load

Last Updated：2025-07-15 08:58:43 Updated

This topic describes how to perform a full direct load by using the LOAD DATA, INSERT INTO SELECT, and CREATE TABLE AS SELECT statements.

Considerations

When performing a full direct load, note the following:

OceanBase Database supports direct load for LOB data types starting from V4.3.0.
During a direct load, a table lock is applied, making the table read-only and preventing other data from being written to it.
Full direct load is suitable for scenarios such as the initial import of large tables, data migration at the scale of 10 GB to TB, and environments with limited CPU and memory resources. This is because the direct load execution path is shorter, saving CPU overhead.
The LOAD DATA statement supports execution within multi-row transactions and will proactively commit any previous transactions during execution.
When using the INSERT INTO SELECT statement for direct load, only Parallel Data Manipulation Language (PDML) is supported. Non-PDML cannot be used for direct load.
When performing partition-specific direct load using the LOAD DATA or INSERT INTO SELECT statements, note that the target table cannot be a replicated table, nor can it contain auto-increment columns, identity columns, or global indexes.
For V4.3.5, starting from V4.3.5 BP1, the following changes apply to full direct load:
- If the target table's last-level partition is a hash/key partition, partition-level direct load is supported.
- When the session-level variable foreign_key_checks is set to False, the direct load operation will not perform foreign key constraint checks.
For LOAD DATA statements, full direct load on tables with unique indexes does not currently support the REPLACE or IGNORE keywords when duplicate unique index keys are encountered, nor does it support setting error-tolerant rows.

Use the `LOAD DATA` statement for direct load

The LOAD DATA statement performs direct load by using the append/direct() hint. If no hint is specified, the behavior of data import is determined based on the parameter default_load_mode.

Notice

OceanBase Database optimizes the data import speed of the LOAD DATA statement through parallel processing. This operation divides the data into multiple subtasks that are executed in parallel, with each subtask treated as an independent transaction. The execution order is not fixed. As a result, for tables without primary keys, the data write order may differ from the order in the original file.

Limitations

During the import process, it is not possible to execute two write operations simultaneously because the import process applies a table lock, and only read operations are allowed throughout the entire import process.
Triggers are not supported during the import process.
Tables containing generated columns are not supported (some indexes may create hidden generated columns, such as KEY idx_c2 (c2(16)) GLOBAL).
Importing data with a single row exceeding 2 MB is not supported.
Liboblog and flashback queries are not supported.

Syntax

LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,'full')] parallel(N) */ [REMOTE_OSS | LOCAL] INFILE 'file_name' INTO TABLE table_name [PARTITION(PARTITION_OPTION)] [COMPRESSION]...

For more information about the LOAD DATA syntax, see LOAD DATA.

Parameter description:

Parameter	Description
APPEND \| DIRECT()	Enables the direct load feature using hints. The `APPEND` hint is equivalent to using `DIRECT(true, 0)` by default and can also support online collection of statistical information (via the `GATHER_OPTIMIZER_STATISTICS` hint). The parameters of `direct()` are explained as follows: `need_sort`: Indicates whether the data being written needs to be sorted. The value is of `bool` type: `true`: Indicates that sorting is required. `false`: Indicates that sorting is not required. Note In scenarios involving the import of ordered files or backup data (already sorted by primary key), you can set the value of `need_sort` to `false` to skip the sorting process. If the data is not sorted by primary key and sorting is not explicitly enabled (i.e., the value of `need_sort` is `true`), an error will be triggered. `max_error`: Specifies the maximum number of tolerable error rows. The value is of `INT` type. If the number of errors exceeds this value, the import task will fail. `full`: Indicates full direct load. This is an optional parameter, and the value must be enclosed in single quotation marks in English.
parallel(N)	Required. The degree of parallelism for loading data. The default value is 4.
REMOTE_OSS	LOCAL	Optional. `REMOTE_OSS` specifies whether to read data from an object storage service. Supported services include Alibaba Cloud OSS, Amazon S3, and S3-compatible storage such as OBS, GCS, and COS. `LOCAL` specifies whether to read data from the client's local file system. If neither `REMOTE_OSS` nor `LOCAL` is specified, data is read from the server's file system (OBServer node).
file_name	Specifies the path and file name of the input file. The format of this parameter corresponds to the type of `REMOTE_OSS \| LOCAL`: When `REMOTE_OSS` is specified, `file_name` indicates that the import file is located in object storage. The commonly supported object storage formats are as follows: Alibaba Cloud OSS:`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY' INTO TABLE table_name ...;` AWS S3:`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 's3://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY&s3_region=S3_REGION' INTO TABLE table_name ...;` S3-compatible object storage:`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 's3://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY' INTO TABLE table_name ...;` When `LOCAL` is specified, the import file is located on the client. The syntax format is:`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / LOCAL INFILE '/$PATH/$FILENAME' INTO TABLE table_name ...;` If neither `REMOTE_OSS` nor `LOCAL` is specified, the import file is located on the OBServer node. In this case, the syntax format is the same as when only `LOCAL` is specified. The explanations for the parameters above are as follows: `$PATH`: Specifies the file path in the storage bucket, indicating the directory where the file is located. `$FILENAME`: Specifies the name of the file, indicating the specific file to be accessed. `$HOST`: Specifies the hostname of the OSS service or the domain name for CDN acceleration, which is the address of the OSS service to be accessed. `$ACCESS_ID`: Specifies the Access Key ID required to access the OSS service for authentication. `$ACCESS_KEY`: Specifies the Access Key Secret required to access the OSS service for authentication. `S3_REGION`: Specifies the region where the Amazon S3 bucket is located. This parameter is mandatory when the object storage is AWS S3. Note When importing files from OSS, ensure the following: Ensure that you have permissions to access the OSS bucket and files. You need sufficient permissions to read the specified bucket and files. This usually requires configuring access permissions through the OSS console or OSS API and setting the access keys (Access Key ID and Access Key Secret) as credentials with appropriate permissions. Ensure that the database server can connect to the specified `$HOST` address over the network to access the OSS service. If using the CDN acceleration domain name of the OSS service, ensure that the CDN configuration is correct and the network connection is stable.
table_name	The name of the table to which data is imported. You can specify any number of columns in the table.
PARTITION_OPTION	Specifies the partition names for direct load: `partition_option_list`: A list of primary partitions. When inserting into multiple partitions simultaneously, separate them with commas (,). `subpartition_option_list`: A list of subpartitions. When inserting into multiple subpartitions simultaneously, separate them with commas (,).
COMPRESSION	Specifies the compression format of the file. Valid values are: `AUTO`: automatically detects the compression algorithm based on the file extension. When you use the `AUTO` parameter, different file extensions correspond to different compression formats. `.gz`: a GZIP-compressed file. `.deflate`: a DEFLATE-compressed file. `.zst/.zstd`: a ZSTD-compressed file. `NONE`: indicates that the file is not compressed. `GZIP`: a GZIP-compressed file. `DEFLATE`: a GZIP-compressed file without metadata. `ZSTD`: a ZSTD-compressed file. You can specify the compression format of a file or let the system detect it based on the file extension.

Example

Note

The following example shows how to import data from a server file. The LOAD DATA statement of OceanBase Database also supports importing data by loading local files (LOCAL INFILE). For more information about LOAD DATA LOCAL INFILE, see Import data by using the LOAD DATA statement.

MySQL mode

Oracle mode

Log in to the machine where the OBServer node is located and create test data in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database only supports loading input files that are stored on the local storage of an OBServer node. Therefore, you need to copy the file to an OBServer node before importing it.
1. Log in to the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data file tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the path of the imported file.

Set the system variable secure_file_priv to configure the path that can be accessed when you import or export files.

Notice

For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.
1. Log in to the server where the OBServer node to connect to resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the mysql001 tenant using a local Unix socket.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After the database is reconnected, use the LOAD /*+ DIRECT */ DATA statement to import data.

Create a table named tbl2.

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),                           
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query table tbl2 for data. The table appears to be empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use the direct load method to import the data from the tbl1.csv file to the tbl2 table.

Import data to all columns of the tbl2 table.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify the columns of the tbl2 table to import data. For example, you can specify col1 and col2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Import data to the subpartition tbl2 of the partitioned table.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

Import data to subpartitions of table tbl2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the default_load_mode parameter to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints in the LOAD DATA statement.

obclient [test]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Verify whether data has been imported to the tbl2 table.

obclient [test]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Log in to the server where the OBServer node to be connected is located and create the test data tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database only supports loading input files that are stored on the local disk of an OBServer node. Therefore, you need to copy the file to an OBServer node before importing it.
1. Log in to the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the path of the imported file.

Set the system variable secure_file_priv to specify the path that can be accessed when you import or export files.

Notice

For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.
1. Log in to the server where the OBServer node to connect to resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the oracle001 tenant using the local Unix socket connection method.
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After the database is reconnected, use the LOAD /*+ DIRECT */ DATA statement to import data.

Create a table named tbl2.

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query table tbl2 for data. The table is empty.

obclient [SYS]> SELECT * FROM tbl2;
Empty set

Use the direct load method to import the data from the tbl1.csv file to the tbl2 table.

Import data to all columns of table tbl2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify the columns of the tbl2 table to import data. For example, you can specify col1 and col2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify to import data to the subpartitions of the tbl2 table.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

(Optional) Specify to import data to subpartitions of the tbl2 table.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the default_load_mode parameter to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints in the LOAD DATA statement.

obclient [SYS]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Check whether data has been imported to the tbl2 table.

obclient [SYS]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Use the INSERT INTO SELECT statement for direct load

The INSERT INTO SELECT statement can be used to perform direct load by using the append/direct() hint and the enable_parallel_dml variable. If no hint is specified, the behavior of importing data is determined by the default_load_mode parameter.

Limitations

Only PDML is supported. Non-PDML statements cannot use direct load. For more details about parallel DML, see Parallel DML.
During the import process, executing two write operations at the same time, such as writing to the same table, is not allowed. This is because a table lock is applied at the start of the process, and only read operations are permitted throughout the entire import.
Direct load is classified as a DDL statement and cannot be executed within a multi-statement transaction, which is a transaction containing multiple operations.
- It cannot be executed within a BEGIN block.
- Autocommit must be set to 1.
Not supported for use in triggers.
Tables with generated columns are not supported. Some indexes may create hidden generated columns, such as KEY idx_c2 (c2(16)) GLOBAL.
Liboblog and flashback queries are not supported.

Syntax

INSERT /*+ [APPEND |DIRECT(need_sort,max_error,'full')] enable_parallel_dml parallel(N) */ INTO  table_name [PARTITION(PARTITION_OPTION)] select_sentence

For more information about the INSERT INTO syntax, see INSERT (MySQL mode) and INSERT (Oracle mode).

Parameter description:

Parameter	Description
APPEND \| DIRECT()	Uses hints to enable direct load. The `APPEND` hint is equivalent to `DIRECT(true, 0)` by default and also enables the functionality of collecting statistics online (`GATHER_OPTIMIZER_STATISTICS` Hint). The `DIRECT()` parameters are explained as follows: `need_sort`: Indicates whether the data being written needs to be sorted. The value is of `bool` type: `true`: Sorting is required. `false`: Sorting is not required. `max_error`: Indicates the maximum allowable number of error rows. The value is of `INT` type; exceeding this number will cause the import task to fail. `full`: Indicates full direct load. This is optional and the value must be enclosed in single quotes.
enable_parallel_dml	The degree of parallelism for loading data. Note Generally, the `enable_parallel_dml` hint must be used in conjunction with the `parallel` hint to enable parallel DML. However, if the schema of the target table specifies table-level parallelism, only the `enable_parallel_dml` hint needs to be specified.
parallel(N)	The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1.
table_name	The name of the table to which the data is imported. You can specify any number of columns in the table.
PARTITION_OPTION	Specifies the partition names for direct load: `partition_option_list`: A list of primary partitions. When inserting into multiple partitions simultaneously, separate them with commas (,). `subpartition_option_list`: A list of subpartitions. When inserting into multiple subpartitions simultaneously, separate them with commas (,).

Example

MySQL mode

Oracle mode

Example 1: Use the INSERT INTO SELECT statement to perform full direct load.

Use direct load to import part of the data from tbl2 to tbl1.

Query table tbl1 for data. The table is empty.

obclient [test]> SELECT * FROM tbl1;
Empty set

Query whether the tbl2 table has data.

obclient [test]> SELECT * FROM tbl2;

Data is queried from the tbl2 table.

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a1   |   11 |
|    2 | a2   |   22 |
|    3 | a3   |   33 |
+------+------+------+
3 rows in set

Use the direct load method to import the data from table tbl2 to table tbl1.

Specify a hint for the INSERT INTO SELECT statement.

The data is imported without specifying partitions.

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

(Optional) Specify partitions to import.

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 partition(p0, p1) SELECT t2.col1,1 FROM tbl2 partition(p0, p1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 partition(p0, p1) SELECT t2.col1,1 FROM tbl2 partition(p0sp0_1, p1sp1_1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Do not specify hints for the INSERT INTO SELECT statement.

Set the value of the default_load_mode parameter to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> INSERT INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl1 t2;

Check whether data has been imported to the tbl1 table.

obclient [test]> SELECT * FROM tbl1;

The query result is as follows:

+------+------+
| col1 | col2 |
+------+------+
|    1 |   11 |
|    2 |   22 |
|    3 |   33 |
+------+------+
3 rows in set

The result shows that data has been imported into table tbl1.

(Optional) Check the Note section in the results returned by the EXPLAIN EXTENDED statement to see if the data was written using direct load.

obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;

The return result is as follows:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================================                                                                               |
| |ID|OPERATOR                           |NAME           |EST.ROWS|EST.TIME(us)|                                                                               |
| ------------------------------------------------------------------------------                                                                               |
| |0 |PX COORDINATOR                     |               |3       |27          |                                                                               |
| |1 |└─EXCHANGE OUT DISTR               |:EX10001       |3       |27          |                                                                               |
| |2 |  └─INSERT                         |               |3       |26          |                                                                               |
| |3 |    └─EXCHANGE IN DISTR            |               |3       |1           |                                                                               |
| |4 |      └─EXCHANGE OUT DISTR (RANDOM)|:EX10000       |3       |1           |                                                                               |
| |5 |        └─SUBPLAN SCAN             |ANONYMOUS_VIEW1|3       |1           |                                                                               |
| |6 |          └─PX BLOCK ITERATOR      |               |3       |1           |                                                                               |
| |7 |            └─TABLE FULL SCAN      |t2             |3       |1           |                                                                               |
| ==============================================================================                                                                               |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output(nil), filter(nil), rowset=16                                                                                                                    |
|   1 - output(nil), filter(nil), rowset=16                                                                                                                    |
|       dop=16                                                                                                                                                 |
|   2 - output(nil), filter(nil)                                                                                                                               |
|       columns([{tbl1: ({tbl1: (tbl1.__pk_increment(0x7efa63627790), tbl1.col1(0x7efa63611980), tbl1.col3(0x7efa63611dc0))})}]), partitions(p0),              |
|       column_values([T_HIDDEN_PK(0x7efa63627bd0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7efa63626f10))(0x7efa63627ff0)], [column_conv(INT, |
|       PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7efa63627350))(0x7efa6362ff20)])                                                                                 |
|   3 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16          |
|   4 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16          |
|       dop=16                                                                                                                                                 |
|   5 - output([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16                                         |
|       access([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)])                                                                 |
|   6 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16                                                                   |
|   7 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16                                                                   |
|       access([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), partitions(p0)                                                                           |
|       is_index_back=false, is_global_index=false,                                                                                                            |
|       range_key([t2.__pk_increment(0x7efa63656410)]), range(MIN ; MAX)always true                                                                            |
| Used Hint:                                                                                                                                                   |
| -------------------------------------                                                                                                                        |
|   /*+                                                                                                                                                        |
|                                                                                                                                                              |
|       USE_PLAN_CACHE( NONE )                                                                                                                                 |
|       PARALLEL(16)                                                                                                                                           |
|       ENABLE_PARALLEL_DML                                                                                                                                    |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                |
|   */                                                                                                                                                         |
| Qb name trace:                                                                                                                                               |
| -------------------------------------                                                                                                                        |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                             |
|   stmt_id:1, INS$1                                                                                                                                           |
|   stmt_id:2, SEL$1                                                                                                                                           |
| Outline Data:                                                                                                                                                |
| -------------------------------------                                                                                                                        |
|   /*+                                                                                                                                                        |
|       BEGIN_OUTLINE_DATA                                                                                                                                     |
|       PARALLEL(@"SEL$1" "t2"@"SEL$1" 16)                                                                                                                     |
|       FULL(@"SEL$1" "t2"@"SEL$1")                                                                                                                            |
|       USE_PLAN_CACHE( NONE )                                                                                                                                 |
|       PARALLEL(16)                                                                                                                                           |
|       ENABLE_PARALLEL_DML                                                                                                                                    |
|       OPTIMIZER_FEATURES_ENABLE('4.3.3.0')                                                                                                                   |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                |
|       END_OUTLINE_DATA                                                                                                                                       |
|   */                                                                                                                                                         |
| Optimization Info:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   t2:                                                                                                                                                        |
|       table_rows:3                                                                                                                                           |
|       physical_range_rows:3                                                                                                                                  |
|       logical_range_rows:3                                                                                                                                   |
|       index_back_rows:0                                                                                                                                      |
|       output_rows:3                                                                                                                                          |
|       table_dop:16                                                                                                                                           |
|       dop_method:Global DOP                                                                                                                                  |
|       avaiable_index_name:[tbl2]                                                                                                                             |
|       stats info:[version=0, is_locked=0, is_expired=0]                                                                                                      |
|       dynamic sampling level:0                                                                                                                               |
|       estimation method:[DEFAULT, STORAGE]                                                                                                                   |
|   Plan Type:                                                                                                                                                 |
|       DISTRIBUTED                                                                                                                                            |
|   Note:                                                                                                                                                      |
|       Degree of Parallelism is 16 because of hint                                                                                                            |
|       Direct-mode is enabled in insert into select                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
77 rows in set (0.009 sec)

Example 2: For a Range-Hash subpartitioned table, specify a primary partition for direct load.

The target table is a subpartitioned table, with the partition being a Range partition and the subpartition being a Hash partition. Specify the partition for full direct load.

Create a table named tbl1 with two partitions. The partition is by RANGE, and the subpartition is by HASH.

obclient [test]> CREATE TABLE tbl1(col1 INT,col2 INT)
                  PARTITION BY RANGE COLUMNS(col1)
                     SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 (
                        PARTITION p0 VALUES LESS THAN(10),                                               
                        PARTITION p1 VALUES LESS THAN(20));

Use direct load to import the data from table tbl2 to partitions p0 and p1 of table tbl1.

obclient [test]> insert /*+ direct(true, 0, 'full') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;

Example 1: A complete example of using INSERT INTO SELECT for full direct load.

Use direct load to import part of the data from table tbl4 to tbl3.

Check whether table tbl3 contains any data. At this point, the table is empty.
```
obclient [test]> SELECT * FROM tbl3;
Empty set
```

Check whether table tbl4 contains any data.

obclient [test]> SELECT * FROM tbl4;

The query shows that table tbl4 contains data.

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 | a1   |   11 |
|    2 | a2   |   22 |
|    3 | a3   |   33 |
+------+------+------+
3 rows in set (0.000 sec)

Use direct load to import the data from table tbl4 to table tbl3.

Specify the hint for the INSERT INTO SELECT statement.

Do not specify partitions for import.

obclient [test]> INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

(Optional) Specify partitions to import.

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 partition(p0, p1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 partition(p0sp0_1, p1sp1_1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Do not specify hints for the INSERT INTO SELECT statement.

Set the value of the default_load_mode parameter to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> INSERT INTO tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;

Verify whether data has been imported to the tbl3 table.

obclient [test]> SELECT * FROM tbl3;

The query result is as follows:

+------+------+
| col1 | col3 |
+------+------+
|    1 |   11 |
|    2 |   22 |
|    3 |   33 |
+------+------+
3 rows in set

The result shows that data has been imported into table tbl3.

(Optional) Check the Note section in the results returned by the EXPLAIN EXTENDED statement to verify whether the data was written using direct load.

obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 SELECT t2.col1,t2.col3 FROM tbl4 t2;

The return result is as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================================                                                                                                                                        |
| |ID|OPERATOR                                |NAME           |EST.ROWS|EST.TIME(us)|                                                                                                                                        |
| -----------------------------------------------------------------------------------                                                                                                                                        |
| |0 |OPTIMIZER STATS MERGE                   |               |3       |29          |                                                                                                                                        |
| |1 |└─PX COORDINATOR                        |               |3       |29          |                                                                                                                                        |
| |2 |  └─EXCHANGE OUT DISTR                  |:EX10001       |3       |27          |                                                                                                                                        |
| |3 |    └─INSERT                            |               |3       |27          |                                                                                                                                        |
| |4 |      └─OPTIMIZER STATS GATHER          |               |3       |1           |                                                                                                                                        |
| |5 |        └─EXCHANGE IN DISTR             |               |3       |1           |                                                                                                                                        |
| |6 |          └─EXCHANGE OUT DISTR (RANDOM) |:EX10000       |3       |1           |                                                                                                                                        |
| |7 |            └─SUBPLAN SCAN              |ANONYMOUS_VIEW1|3       |1           |                                                                                                                                        |
| |8 |              └─PX BLOCK ITERATOR       |               |3       |1           |                                                                                                                                        |
| |9 |                └─COLUMN TABLE FULL SCAN|T2             |3       |1           |                                                                                                                                        |
| ===================================================================================                                                                                                                                        |
| Outputs & filters:                                                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                                                      |
|   0 - output(nil), filter(nil), rowset=16                                                                                                                                                                                  |
|   1 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 |
|   2 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 |
|       dop=16                                                                                                                                                                                                               |
|   3 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil)            |
|       columns([{TBL3: ({TBL3: (TBL3.__pk_increment(0x7ef8f6027fa0), TBL3.COL1(0x7ef8f6011d50), TBL3.COL3(0x7ef8f6012190))})}]), partitions(p0),                                                                            |
|       column_values([T_HIDDEN_PK(0x7ef8f60283e0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,                                                         |
|       PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)])                                                                                                                                               |
|   4 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)],                         |
|        [T_HIDDEN_PK(0x7ef8f60283e0)]), filter(nil), rowset=16                                                                                                                                                              |
|   5 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                        |
|   6 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                        |
|       dop=16                                                                                                                                                                                                               |
|   7 - output([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                                                       |
|       access([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)])                                                                                                                               |
|   8 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16                                                                                                                                 |
|   9 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16                                                                                                                                 |
|       access([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), partitions(p0)                                                                                                                                         |
|       is_index_back=false, is_global_index=false,                                                                                                                                                                          |
|       range_key([T2.__pk_increment(0x7ef8f60584a0)]), range(MIN ; MAX)always true                                                                                                                                          |
| Used Hint:                                                                                                                                                                                                                 |
| -------------------------------------                                                                                                                                                                                      |
|   /*+                                                                                                                                                                                                                      |
|                                                                                                                                                                                                                            |
|       USE_PLAN_CACHE( NONE )                                                                                                                                                                                               |
|       PARALLEL(16)                                                                                                                                                                                                         |
|       ENABLE_PARALLEL_DML                                                                                                                                                                                                  |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                                                                              |
|   */                                                                                                                                                                                                                       |
| Qb name trace:                                                                                                                                                                                                             |
| -------------------------------------                                                                                                                                                                                      |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                                                                           |
|   stmt_id:1, INS$1                                                                                                                                                                                                         |
|   stmt_id:2, SEL$1                                                                                                                                                                                                         |
| Outline Data:                                                                                                                                                                                                              |
| -------------------------------------                                                                                                                                                                                      |
|   /*+                                                                                                                                                                                                                      |
|       BEGIN_OUTLINE_DATA                                                                                                                                                                                                   |
|       PARALLEL(@"SEL$1" "T2"@"SEL$1" 16)                                                                                                                                                                                   |
|       FULL(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                                          |
|       USE_COLUMN_TABLE(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                              |
|       USE_PLAN_CACHE( NONE )                                                                                                                                                                                               |
|       PARALLEL(16)                                                                                                                                                                                                         |
|       ENABLE_PARALLEL_DML                                                                                                                                                                                                  |
|       OPTIMIZER_FEATURES_ENABLE('4.3.3.0')                                                                                                                                                                                 |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                                                                              |
|       END_OUTLINE_DATA                                                                                                                                                                                                     |
|   */                                                                                                                                                                                                                       |
| Optimization Info:                                                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                                                      |
|   T2:                                                                                                                                                                                                                      |
|       table_rows:3                                                                                                                                                                                                         |
|       physical_range_rows:3                                                                                                                                                                                                |
|       logical_range_rows:3                                                                                                                                                                                                 |
|       index_back_rows:0                                                                                                                                                                                                    |
|       output_rows:3                                                                                                                                                                                                        |
|       table_dop:16                                                                                                                                                                                                         |
|       dop_method:Global DOP                                                                                                                                                                                                |
|       avaiable_index_name:[TBL4]                                                                                                                                                                                           |
|       stats info:[version=0, is_locked=0, is_expired=0]                                                                                                                                                                    |
|       dynamic sampling level:0                                                                                                                                                                                             |
|       estimation method:[DEFAULT, STORAGE]                                                                                                                                                                                 |
|   Plan Type:                                                                                                                                                                                                               |
|       DISTRIBUTED                                                                                                                                                                                                          |
|   Note:                                                                                                                                                                                                                    |
|       Degree of Parallelism is 16 because of hint                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
82 rows in set (0.006 sec)

Example 2: For a range-hash subpartitioned table, specify to directly load data into the partition.

The target table is a subpartitioned table, with the partion being a range partition and the subpartition being a hash partition. Specify the primary partition for full direct load.

Create a table named tbl1 with two partitions. The partition is by range, and the subpartition is by hash.

obclient [test]> CREATE TABLE tbl1_1 (
                     col1 INT,
                     col2 INT
                  )
                  PARTITION BY RANGE (col1)   
                  SUBPARTITION BY HASH (col2)
                  SUBPARTITION TEMPLATE (     
                     SUBPARTITION sp1,
                     SUBPARTITION sp2,
                     SUBPARTITION sp3
                  )
                  (
                     PARTITION p0 VALUES LESS THAN (10),
                     PARTITION p1 VALUES LESS THAN (20)  
                  );

Use direct load to import the data from table tbl2 to partitions p0 and p1 of table tbl1.

obclient [test]> insert /*+ direct(true, 0, 'full') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;

Use the CREATE TABLE AS SELECT statement for direct load

You can use the CREATE TABLE AS SELECT statement to specify the direct load mode by using the DIRECT() hint. If the hint is not specified, the direct load mode is determined based on the default_load_mode parameter.

Syntax

CREATE /*+ [APPEND | DIRECT(need_sort,max_error,load_type)] parallel(N) */ TABLE table_name [AS] select_sentence

For more information about the CREATE TABLE syntax, see CREATE TABLE (MySQL mode) and CREATE TABLE (Oracle mode).

Parameter description:

Parameter Description

APPEND | DIRECT()

Parameter	Description
APPEND \| DIRECT()	Use hints to enable direct load. The `APPEND` hint is equivalent to using `DIRECT(true, 0)` by default and also supports collecting statistics online (`GATHER_OPTIMIZER_STATISTICS` hint). The parameters for `DIRECT()` are explained as follows: `need_sort`: Indicates whether the data being written needs to be sorted. The value is of `bool` type: `true`: Sorting is required. `false`: Sorting is not required. `max_error`: Indicates the maximum allowable number of error rows. The value is of `INT` type; if this limit is exceeded, the import task will fail. `full`: Indicates full direct load. This is optional, and the value must be enclosed in single quotes.
parallel(N)	The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1.

Use hints to enable direct load.

The APPEND hint is equivalent to using DIRECT(true, 0) by default and also supports collecting statistics online (GATHER_OPTIMIZER_STATISTICS hint).
The parameters for DIRECT() are explained as follows:
- need_sort: Indicates whether the data being written needs to be sorted. The value is of bool type:
  - true: Sorting is required.
  - false: Sorting is not required.
- max_error: Indicates the maximum allowable number of error rows. The value is of INT type; if this limit is exceeded, the import task will fail.
- full: Indicates full direct load. This is optional, and the value must be enclosed in single quotes.

parallel(N) The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1.

Example

MySQL mode

Oracle mode

Use direct load to import data from table tbl1 to other tables.

Create a table named tbl1.

obclient [test]> CREATE TABLE tbl1(c1 int);

Insert data into the tbl1 table.

obclient [test]> INSERT INTO tbl1 VALUES (1),(2),(3);

Query the data in the tbl1 table.

obclient [test]> SELECT * FROM tbl1;

The query result is as follows:

+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.027 sec)

Use direct load to import data from table tbl1 to table tbl2.

Specifies a hint for the CREATE TABLE AS SELECT statement.

Use the APPEND hint for direct load.

obclient [test]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

Use the DIRECT hint to bypass data import.

obclient [test]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

Do not specify hints in the CREATE TABLE AS SELECT statement.

Set the value of the default_load_mode parameter to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;

Verify whether data has been imported to the tbl2 table.
```
obclient [test]> SELECT * FROM tbl2;
```
The query result is as follows:
```
+------+
| c1   |
+------+
|    1 |
|    3 |
|    2 |
+------+
3 rows in set (0.050 sec)
```
The result shows that data has been imported into table tbl2.

Use direct load to import data from table tbl1 to other tables.

Create a table named tbl1.

obclient [SYS]> CREATE TABLE tbl1(c1 int);

Insert data into the tbl1 table.

obclient [SYS]> INSERT INTO tbl1 VALUES (1),(2),(3);

Query the data in the tbl1 table.

obclient [SYS]> SELECT * FROM tbl1;

The query result is as follows:

+------+
| C1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.045 sec)

Use the direct load method to import data from table tbl1 to table tbl2.

Specifies a hint for the CREATE TABLE AS SELECT statement.

Use the APPEND hint for direct load.

obclient [SYS]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

Use the DIRECT hint to bypass data import.

obclient [SYS]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

Do not specify hints in the CREATE TABLE AS SELECT statement.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [SYS]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;

Check whether data has been imported to the tbl2 table.
```
obclient [SYS]> SELECT * FROM tbl2;
```
The query result is as follows:
```
+------+
| C1   |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.013 sec)
```
The result shows that data has been imported into table tbl2.

Full direct load

Considerations

Use the LOAD DATA statement for direct load

Notice

Limitations

Syntax

Note

Note

Example

Note

Note

Notice

Note

Notice

Use the INSERT INTO SELECT statement for direct load

Limitations

Syntax

Note

Example

Use the CREATE TABLE AS SELECT statement for direct load

Syntax

Example

References

Use the `LOAD DATA` statement for direct load