Full direct load|V4.4.2|OceanBase Database| docs|Distributed Database

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 you use full direct load, note the following considerations:

OceanBase Database V4.3.0 and later support direct load for LOB data types.
During direct load, a table lock is placed on the table. The table cannot be written to, and it is read-only during the entire process.
Full direct load is suitable for large tables that are being imported for the first time, data migration of 10 GB to TB in size, and scenarios where CPU and memory resources are not particularly abundant. This is because the execution path of direct load is shorter, which reduces CPU overhead.
The LOAD DATA statement can be executed in multi-row transactions. During execution, it will automatically commit previous transactions.
When using the INSERT INTO SELECT statement for direct load, only PDML (Parallel Data Manipulation Language) is supported. Non-PDML operations cannot be directly loaded.
When using the LOAD DATA or INSERT INTO SELECT statement for direct load on a specified partition, the target table cannot be a replicated table, and it cannot contain auto-increment columns, identity columns, or global indexes.
Starting from V4.3.5 BP1, full direct load in V4.3.5 has the following changes:
- If the last-level partition of the target table is a hash or key partition, partition-level direct load is supported.
- If the session-level variable foreign_key_checks is set to False, foreign key constraints are not checked during direct load.
For full direct load with unique indexes, if duplicate unique index keys are encountered, the REPLACE or IGNORE keywords are not supported, and error rows cannot be set.

Use the LOAD DATA statement to import data directly

The LOAD DATA statement can bypass the import process by using the append/direct() hint. If the hint is not specified, the import behavior is determined by the parameter default_load_mode.

Notice

OceanBase Database optimizes the data import rate of the LOAD DATA statement by using parallel processing technology. This operation divides the data into multiple subtasks and executes them in parallel. Each subtask is treated as an independent transaction, and the execution order is not fixed. Therefore, for tables without a primary key, the data write order may differ from the original file order.

Limitations

During the import process, you cannot execute two write operations at the same time (i.e., you cannot write to a table while another write operation is in progress). This is because a table lock is placed at the beginning of the import process, and only read operations are allowed during the entire import.
Triggers are not supported.
Tables with generated columns are not supported. For example, certain indexes may create hidden generated columns, such as KEY idx_c2 (c2(16)) GLOBAL.
Importing data rows exceeding 2 MB is not supported.
Liboblog and Flashback Query 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 direct load by using a hint. `APPEND` is equivalent to `DIRECT(true, 0)` by default. It also enables online statistics collection by using the `GATHER_OPTIMIZER_STATISTICS` hint. `direct()` specifies the following parameters: `need_sort`: specifies whether to sort the data. The value is of the `bool` type. `true`: indicates that the data needs to be sorted. `false`: indicates that the data does not need to be sorted. Note When you import data from an ordered file or a backup file (sorted by the primary key), you can set `need_sort` to `false` to skip the sorting process. If the data is not sorted by the primary key and sorting is not explicitly enabled (that is, `need_sort` is set to `true`), an error is returned. `max_error`: specifies the maximum number of error rows that can be tolerated. The value is of the `INT` type. If the number of error rows exceeds this value, the import task fails. `full`: specifies full direct load. The value is enclosed in single quotation marks.
parallel(N)	Required. The parallelism of data loading. The default value is 4.
REMOTE_OSS \| LOCAL	Optional. `REMOTE_OSS` specifies whether to read data from an object storage service. Currently, you can use Alibaba Cloud OSS, AWS S3, and object storage services compatible with S3, such as OBS and GCS. `LOCAL` specifies whether to read data from the local file system of the client. If you do not specify `REMOTE_OSS` or `LOCAL`, data is read from the file system of the OBServer node.
file_name	The path and name of the input file. The format of this parameter corresponds to the type of `REMOTE_OSS \| LOCAL`: When you specify `REMOTE_OSS`, `file_name` specifies the path of the import file in the object storage service. The following examples show the common object storage service formats: 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 ...;` Object storage service compatible with 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' INTO TABLE table_name ...;` When you specify `LOCAL`, the import file is located on the client. In this case, the syntax is `LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / LOCAL INFILE '/$PATH/$FILENAME' INTO TABLE table_name ...;`. When you do not specify `REMOTE_OSS` or `LOCAL`, the import file is located on the OBServer node. In this case, the syntax is the same as when you specify only `LOCAL`. The following table describes the parameters in the preceding examples: `$PATH`: specifies the path of the file in the bucket. `$FILENAME`: specifies the name of the file to be accessed. `$HOST`: specifies the host name or the CDN domain name of the OSS service. This parameter specifies the address of the OSS service to be accessed. `$ACCESS_ID`: specifies the Access Key ID required to access the OSS service. This parameter is used for authentication. `$ACCESSKEY`: specifies the Access Key Secret required to access the OSS service. This parameter is used for authentication. `S3_REGION`: specifies the region where the Amazon S3 bucket is located. This parameter is required when the object storage service is AWS S3. Note When you import data from an object storage service, make sure that the following information is available: You have the required permissions to access the object storage service. You must have sufficient permissions to read the specified bucket and file. Usually, you need to set the access permissions in the OSS console or by using the OSS API, and configure the Access Key ID and Access Key Secret as credentials with appropriate permissions. The database server can connect to the specified `$HOST` address to access the OSS service. If you use the CDN-accelerated domain name of the OSS service, make sure that the CDN configuration is correct and that the network connection is normal.
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 name when you perform a direct load. partition_option_list: the list of partitions to be inserted, separated by commas (,). subpartition_option_list: the list of subpartitions to be inserted, separated by commas (,).
COMPRESSION	Specifies the compression format of the file. Valid values: `AUTO`: automatically detects the compression algorithm based on the file name suffix. When you use the `AUTO` parameter, different suffixes correspond to different compression formats. `.gz`: GZIP compression. `.deflate`: DEFLATE compression. `.zst/.zstd`: ZSTD compression. `NONE`: the file is not compressed. `GZIP`: GZIP compression. `DEFLATE`: DEFLATE compression without metadata. `ZSTD`: ZSTD compression. You can explicitly specify the compression format of the file or let the system detect the compression format based on the file name suffix.

Usage examples

Explanation

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

MySQL-compatible mode

Oracle-compatible mode

Log in to the machine where an OBServer node needs to be connected, and create test data in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database only supports loading input files from local OBServer nodes. Therefore, you must copy the file to an OBServer node before importing it.
1. Log in to the server on which the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create test data in a file named tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Specify the file to import.

Sets the secure_file_priv system variable to configure the path of the file for import or export. This option is available only to the administrator.

Note

For security reasons, you can connect to the database only via a local Unix Socket when setting the secure_file_priv system variable. The following table describes how to modify the value of the secure_file_priv variable. For more information, see secure_file_priv.
1. Log in to the server where you want to connect to the OBServer node.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Run the following command to connect to tenant mysql001 through 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
```

Reload the data with the LOAD /*+ DIRECT */ DATA statement.

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

Check if the tbl2 table has data. The table is currently empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Import the data from the tbl1.csv file to table tbl2 by using bypass import.

Import 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

Import data from any columns of the tbl2 table. For example, import data from the col1 and col2 columns.

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 into partition p2 of table tbl2.

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 for the table tbl2 to the subpartition.

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 option 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 a hint 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 indicates that data is imported into the tbl2 table.

Log in to the host where the OBServer node that you want to connect to is located. Create test data tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database supports loading only local input files on an OBServer node. Therefore, you must copy the file to an OBServer before the import.
1. Log in to the server on which the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create a test data file named tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Enter the path of the imported file.

Sets the system variable secure_file_priv to specify the path from which import or export files can be accessed.

Notice

Due to security reasons, when you set the system variable secure_file_priv, you can only connect to the database through a local Unix 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 is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Run the following command to connect to the oracle001 tenant through a local Unix socket.
```
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 reconnecting to the database, 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 whether the tbl2 table contains data. The result indicates that the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

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

Import data into all columns of the tbl2 table.

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

Import data into specified columns of the tbl2 table. For example, import data into the col1 and col2 columns.

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) Import data into partitions 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) Import data into 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 ',';

Import data by using the default_load_mode parameter.

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 the Hint in the LOAD DATA statement.

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

Verify whether data has been imported into 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 indicates that data has been imported into the tbl2 table.

Use the INSERT INTO SELECT statement to bypass data import

The INSERT INTO SELECT statement uses the append/direct() hint and the enable_parallel_dml parameter to perform a bypass import. If no hint is specified, the behavior of data import is determined by the default_load_mode parameter.

Limitations

Only PDML (Parallel Data Manipulation Language) is supported for direct load. Non-PDML statements cannot be used for direct load. For more information about parallel DML, see Parallel DML.
During a direct load, you cannot perform two write operations at the same time. This is because a table lock is placed on the table before the direct load starts, and only read operations are allowed during the direct load.
Direct load is a DDL statement and cannot be executed in a multi-row transaction that contains multiple operations.
- You cannot execute it in a Begin block.
- Autocommit must be set to 1.
Direct load cannot be used in triggers.
Direct load cannot be used for tables that contain generated columns. For example, some indexes generate hidden generated columns, such as KEY idx_c2 (c2(16)) GLOBAL.
Direct load cannot be used for Liboblog or flashback queries.

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 statement, see INSERT (MySQL-compatible mode) and INSERT (Oracle-compatible mode).

Parameters:

Parameter	Description
APPEND \| DIRECT()	Specifies the hint for enabling direct load. `APPEND` is equivalent to using `DIRECT(true, 0)` by default. It also enables online statistics collection (`GATHER_OPTIMIZER_STATISTICS`). `DIRECT()` specifies the following parameters: `need_sort`: specifies whether to sort the data to be written. The value is of the `bool` type: `true`: specifies to sort the data. `false`: specifies not to sort the data. `max_error`: specifies the maximum number of error rows that can be tolerated. The value is of the `INT` type. If the number of error rows exceeds this value, the direct load task fails. `full`: specifies a full direct load. This parameter is optional. The value must be enclosed in single quotation marks (').
enable_parallel_dml	Specifies the parallelism for data loading. Note Generally, you must use both the `enable_parallel_dml` and `parallel` hints to enable parallel DML. However, if the schema of the target table specifies the table-level parallelism, you only need to use the `enable_parallel_dml` hint.
parallel(N)	Specifies the parallelism for data loading. This parameter is required. The value is an integer greater than 1.
table_name	The name of the table to which the data is to be imported. You can specify any number of columns in the table.
PARTITION_OPTION	Specifies the partition name for a direct load to a partition: partition_option_list: the list of partitions to be inserted. Separate multiple partitions with commas (,). subpartition_option_list: the list of subpartitions to be inserted. Separate multiple subpartitions with commas (,).

Examples

MySQL-compatible mode

Oracle-compatible mode

Example 1: Full direct load using INSERT INTO SELECT.

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

Query whether table tbl1 contains data. The result shows that the table is empty.
```
obclient [test]> SELECT * FROM tbl1;
Empty set
```

Query whether table tbl2 contains data.

obclient [test]> SELECT * FROM tbl2;

The query result shows that table tbl2 contains data.

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

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

Specify the Hint for the INSERT INTO SELECT statement.

Do not specify partitioned direct load.

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 partitioned direct load.

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 the Hint 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;

Query whether data has been imported to table tbl1.

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 to table tbl1.

(Optional) Check whether the data is written by direct load in the Note field of the return result of the EXPLAIN EXTENDED statement.

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: Specify the partitioned direct load for the primary partitions of a RANGE-HASH partitioned table.

The target table is a RANGE-HASH partitioned table. The primary partitions are RANGE partitions, and the secondary partitions are HASH partitions. Specify the partitioned direct load for the primary partitions.

Create a table tbl1 with two partitions. The primary partitions are RANGE partitions, and the secondary partitions are HASH partitions.

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 data from table tbl2 to the p0 and p1 partitions 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: Full direct load using INSERT INTO SELECT.

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

Query whether table tbl3 contains data. The result shows that the table is empty.
```
obclient [test]> SELECT * FROM tbl3;
Empty set
```

Query whether table tbl4 contains data.

obclient [test]> SELECT * FROM tbl4;

The query result 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 data from table tbl4 to table tbl3.

Specify the Hint for the INSERT INTO SELECT statement.

Do not specify partitioned direct load.

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 partitioned direct load.

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 the Hint 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;

Query whether data has been imported to table tbl3.

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 to table tbl3.

(Optional) Check whether the data is written by direct load in the Note field of the return result of the EXPLAIN EXTENDED statement.

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: Specify the partitioned direct load for the primary partitions of a RANGE-HASH partitioned table.

Create a table tbl1 with two partitions. The primary partitions are RANGE partitions, and the secondary partitions are HASH partitions.

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 data from table tbl2 to the p0 and p1 partitions 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 to import data in direct mode

The CREATE TABLE AS SELECT statement specifies the direct import mode by setting the DIRECT() hint. If no hint is specified, the behavior of importing data is determined by 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-compatible mode) and CREATE TABLE (Oracle-compatible mode).

Parameter description:

Parameter	Description
APPEND \| DIRECT()	Specifies a hint to enable direct load. `APPEND` is equivalent to `DIRECT(true, 0)` by default. It also supports the `GATHER_OPTIMIZER_STATISTICS` hint for online statistics collection. `DIRECT()` specifies parameters. `need_sort`: specifies whether the data to be written needs to be sorted. The value is of the `bool` type. `true`: the data needs to be sorted. `false`: the data does not need to be sorted. `max_error`: specifies the maximum number of error rows that can be tolerated. The value is of the `INT` type. If this value is exceeded, the import task fails. `full`: specifies whether to perform a full direct load. This parameter is optional and must be enclosed in single quotation marks.
parallel(N)	The parallelism for data loading. This parameter is required and must be an integer greater than 1.

Parameter

Description

APPEND | DIRECT()

Specifies a hint to enable direct load.

APPEND is equivalent to DIRECT(true, 0) by default. It also supports the GATHER_OPTIMIZER_STATISTICS hint for online statistics collection.
DIRECT() specifies parameters.
- need_sort: specifies whether the data to be written needs to be sorted. The value is of the bool type.
  - true: the data needs to be sorted.
  - false: the data does not need to be sorted.
- max_error: specifies the maximum number of error rows that can be tolerated. The value is of the INT type. If this value is exceeded, the import task fails.
- full: specifies whether to perform a full direct load. This parameter is optional and must be enclosed in single quotation marks.

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

Examples

MySQL-compatible mode

Oracle-compatible mode

Use direct load to import data from the tbl1 table to another table.

Create the tbl1 table.

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 the tbl1 table to the tbl2 table.

Specify 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 for direct load.

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

Do not specify a hint for 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 indicates that data has been imported to the tbl2 table.

Use direct load to import data from the tbl1 table to another table.

Create the tbl1 table.

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 direct load to import data from the tbl1 table to the tbl2 table.

Specify 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 for direct load.

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

Do not specify a hint for 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;

Verify 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 indicates that data has been imported to the tbl2 table.

OceanBase

Customer Stories

Documentation

Full direct load

Considerations

Use the LOAD DATA statement to import data directly

Notice

Limitations

Syntax

Note

Note

Usage examples

Explanation

Note

Note

Note

Notice

Use the INSERT INTO SELECT statement to bypass data import

Limitations

Syntax

Note

Examples

Use the CREATE TABLE AS SELECT statement to import data in direct mode

Syntax

Examples

References