Incremental direct load|V4.6.0|OceanBase Database| docs|Distributed Database

Incremental direct load

Last Updated：2026-05-07 11:26:25 Updated

If a table already contains data and you want to import incremental data, you can use the incremental direct load feature. Although you can import incremental data using a full direct load, this process will overwrite all existing data, resulting in poor import performance. In contrast, incremental direct load only processes the incremental data, ensuring better performance.

This topic describes how to use the LOAD DATA, INSERT INTO SELECT, and CREATE TABLE AS SELECT statements to perform an incremental direct load.

Incremental direct load strategy

OceanBase Database V4.6.0 optimizes incremental direct load. The new incremental direct load strategy divides data into two parts. The part with primary key conflicts is written to a Mini SSTable (incremental SSTable), and the part without primary key conflicts is written to a Major SSTable (baseline SSTable). This way, for cases with few conflicts, the new incremental direct load strategy allows multiple imports without compromising query performance.

Considerations

When you use direct load, note the following considerations:

Direct load is not recommended for small data volumes that can be imported within a few minutes, because incremental direct load triggers a major compaction.
Direct load supports non-unique local indexes on the target table.
The LOAD DATA statement can be executed in a multi-row transaction. When you execute the LOAD DATA statement, it automatically commits the previous transaction.
When you use the INSERT INTO SELECT statement for direct load, only Parallel Data Manipulation Language (PDML) is supported. Direct load is not supported for non-PDML statements.
When you use the LOAD DATA or INSERT INTO SELECT statement for direct load to a specified partition, the target table cannot be a replicated table, and the table cannot contain auto-increment columns, identity columns, or global indexes.
For V4.3.5, incremental direct load has the following changes starting from V4.3.5 BP1:
- If the last-level partition of the target table is a hash or key partition, you can perform direct load at the partition level.
- Only heap tables with one unique local index are supported. If the target table contains multiple unique local indexes or global indexes, the import operation fails.
- In the LOAD DATA or INSERT INTO SELECT statement, if multiple direct load tasks have overlapping partitions, parallel direct load is not supported. If no partitions overlap, parallel direct load is supported.
- When the foreign_key_checks session-level variable is set to False, the direct load operation does not check foreign key constraints.

Use the LOAD DATA statement to perform a direct load

The LOAD DATA statement can perform a direct load by using the DIRECT() hint. If you do not specify the hint, the system determines the import behavior based on the default_load_mode configuration item.

Limitations

During import, you cannot execute two write operations at the same time (i.e., you cannot write to one table while another write operation is in progress). This is because a table lock is placed on the table during import, and only read operations are allowed throughout the import process.
Import is not supported in triggers.
Import is not supported for tables that contain generated columns (some indexes create hidden generated columns, for example, KEY idx_c2 (c2(16)) GLOBAL).
Import is not supported for single rows exceeding 2 MB.
Import is not supported for Liboblog and flashback queries.
Incremental direct load is not supported for tables with foreign keys.
Incremental direct load is not supported for tables with GLOBAL indexes.
Incremental direct load is not supported for tables with constraints.
Incremental direct load is not supported for tables with more than one LOCAL unique index and no primary key.
Incremental direct load is not supported for tables with a unique index and a primary key.

Syntax

LOAD DATA /*+ [DIRECT(need_sort,max_error,{'inc'|'inc_replace'})] 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
DIRECT()	Enables direct load by using the hint. The following table describes the parameters in `DIRECT()`. `need_sort`: specifies whether the data to be written needs to be sorted. Valid values: `bool`. `true`: the data needs to be sorted. `false`: the data does not need to be sorted. Note When you import data from an ordered file or a backup file (which is 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 the `need_sort` parameter is set to `true`, an error will be triggered. `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 will fail. `inc\|inc_replace`: specifies the two modes of direct load. The value must be enclosed in single quotes. `inc`: incremental direct load. It checks for duplicate primary keys and supports the `INSERT` and `IGNORE` semantics. `inc_replace`: incremental direct load. It does not check for duplicate primary keys, which is equivalent to the `REPLACE` semantics. Notice When the `load_mode` parameter is set to `inc_replace`, the `REPLACE` or `IGNORE` keyword cannot be specified in the `LOAD DATA` statement.
parallel(N)	Required. The parallelism for data loading. Default value: 4.
REMOTE_OSS \| LOCAL	Optional. `REMOTE_OSS` specifies whether to read data from an object storage service. Currently, you can read data from Alibaba Cloud OSS, AWS S3, and object storage services that are compatible with S3, such as OBS, GCS, and COS. `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 server (OBServer node).
file_name	The path and name of the input file. This parameter corresponds to the type of `REMOTE_OSS \| LOCAL`. When `REMOTE_OSS` is specified, `file_name` indicates that the import file is stored in the object storage. The following are some common object storage formats supported: Aliyun 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 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 `LOCAL` is specified, the import file is stored on the client. In this case, the syntax is `LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE '/$PATH/$FILENAME' INTO TABLE table_name ...;`. If neither `REMOTE_OSS` nor `LOCAL` is specified, the import file is stored on the OBServer node. In this case, the syntax is the same as when only `LOCAL` is specified. The following table describes the parameters. `$PATH`: the path of the file in the bucket. This parameter specifies the directory where the file is stored. `$FILENAME`: the name of the file. This parameter specifies the file to be accessed. `$HOST`: the hostname or CDN-accelerated domain name of the OSS service. This parameter specifies the address of the OSS service to be accessed. `$ACCESS_ID`: the Access Key ID required to access the OSS service. This parameter is used for authentication. `$ACCESSKEY`: the Access Key Secret required to access the OSS service. This parameter is used for authentication. `S3_REGION`: the region where the Amazon S3 bucket is located. This parameter is required when the object storage is AWS S3. Note When you import files from the object storage, make sure that the following conditions are met: You have the required permissions to access the object storage bucket and files. You must have sufficient permissions to read the specified bucket and files. This usually requires setting up access permissions in the OSS console or through the OSS API and configuring the Access Key ID and Access Key Secret as appropriate credentials. The database server can connect to the specified `$HOST` address to access the OSS service. If you are using the CDN-accelerated domain name of the OSS service, make sure that the CDN configuration is correct and 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	The name of the partition when you perform direct load for a partition. partition_option_list: the list of partitions. Separate multiple partitions with commas (,). subpartition_option_list: the list of subpartitions. Separate multiple subpartitions with commas (,).
COMPRESSION	The compression format of the file. Valid values: `AUTO`: the compression algorithm is automatically detected based on the file name suffix. When you use the `AUTO` parameter, different file name 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 program detect the compression format based on the file name suffix.

Note

The LOAD DATA statement supports wildcard-based multi-file import, just like full direct load. However, the wildcard in the LOAD DATA statement only takes effect on the OBServer node (the wildcard does not take effect when you log in to the OBServer node from another machine and use the LOAD DATA statement).

Examples

The operation steps for incremental direct load in the LOAD DATA statement are the same as those for full direct load. You only need to change the value of full to inc or inc_replace in the statement.

Note

The following example describes the procedure for importing data from a server-side file. In addition, OceanBase Database also supports the LOCAL INFILE method for loading local files. For more information about the LOAD DATA LOCAL INFILE statement, see Use the LOAD DATA statement to load data

MySQL mode

Oracle mode

Log in to the server where the OBServer node is located, and create a test table named tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database supports loading only local input files on the OBServer node. Therefore, you need to copy the file to an OBServer node before you import the file.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the file path for import.

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

Notice

To improve security, you can set only the secure_file_priv variable by connecting to the database by using a local Unix socket. 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. Connect to the mysql001 tenant by 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
```

Connect to the database again, and execute the LOAD /*+ DIRECT */ DATA statement to import data to a table.

Create a table named tbl1.

obclient [test]> CREATE TABLE tbl1 (
                    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 data exists in the tbl1 table. At this time, the query result shows that the table is empty.
```
obclient [test]> SELECT * FROM tbl1;
Empty set
```

Import data from the tbl1.csv file to the tbl1 table.

Specify that all columns of the tbl1 table be imported.

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

Specify that any column of the tbl1 table be imported. For example, specify the col1 and col2 columns.

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

(Optional) Specify that the partitions of the tbl1 table be imported.

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

(Optional) Specify that the subpartitions of the tbl1 table be imported.

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

Use the default_load_mode parameter for data import.

Set the default_load_mode parameter to INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.
```
obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_DIRECT_WRITE';
```

Do not specify the Hint for the LOAD DATA statement.

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

Query whether data exists in 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

This result shows that data exists in the tbl2 table.

Log in to the server where the OBServer node is located, and create a test table named tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database supports loading only local input files on the OBServer node. Therefore, you need to copy the file to an OBServer node before you import the file.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the file path for import.

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, you can only modify the global variable secure_file_priv by using a local Unix socket connection to the database. 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 by using a local Unix socket connection.
```
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 you reconnect to the database, 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 sp0_1 VALUES LESS THAN (50),
                             SUBPARTITION sp0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION sp1_1 VALUES LESS THAN (150),
                             SUBPARTITION sp1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

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

Import data from the tbl1.csv file to the tbl2 table by using direct load.

Import data to all columns of the tbl2 table.

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') 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 to specified columns of the tbl2 table. For example, import data to the col1 and col2 columns.

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') 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 to partitions of the tbl2 table.

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

(Optional) Import data to subpartitions of the tbl2 table.

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') 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 INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.
```
obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_DIRECT_WRITE';
```

Do not specify the 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 has been imported to the tbl2 table.

Use the INSERT INTO SELECT statement to bypass data import

The INSERT INTO SELECT statement can bypass data import by using the direct() hint with the enable_parallel_dml hint. If no hint is specified, the behavior of data import is determined by the default_load_mode configuration item.

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 execute two write operations simultaneously (i.e., you cannot write to one table at the same time). This is because a table lock is placed on the table during the import, and only read operations are allowed during the entire import.
Direct load is not supported in triggers.
Tables with generated columns are not supported. Some indexes generate hidden generated columns. For example, the index KEY idx_c2 (c2(16)) GLOBAL generates a hidden generated column.
Direct load is not supported for Liboblog and flashback queries.
Tables with indexes (excluding primary keys) are not supported for incremental direct load.
Tables with foreign keys are not supported for incremental direct load.
Tables with GLOBAL indexes are not supported for incremental direct load.
Tables with constraints are not supported for incremental direct load.
Tables without a primary key and with more than one LOCAL unique index are not supported for incremental direct load.
Tables with a primary key and a unique index are not supported for incremental direct load.

Syntax

INSERT /*+ [DIRECT(need_sort,max_error,{'inc'|'inc_replace'})] 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).

Parameters:

Parameter	Description
DIRECT()	Enables direct load by using the hint. `DIRECT()` specifies the following 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 the number of error rows exceeds this value, the import task fails. `inc\|inc_replace`: specifies the two modes of incremental direct load. The value must be enclosed in English single quotes. `inc`: incremental import. Checks for duplicate primary keys and supports the `INSERT` and `IGNORE` semantics. `inc_replace`: incremental import. Does not check for duplicate primary keys. This is equivalent to incremental import with the `REPLACE` semantics.
enable_parallel_dml	The parallelism of data loading. Note Generally, you must use both the `enable_parallel_dml` hint and the `parallel` hint to enable parallel DML. However, if the target table has a table-level parallelism specified in its schema, you only need to specify the `enable_parallel_dml` hint.
parallel(N)	The parallelism of 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 imported. You can specify any number of columns in the table.
PARTITION_OPTION	The name of the partition to which the data is imported when you use direct load for partitioning. 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 (,).

Example

The steps for incremental direct load in an INSERT INTO SELECT statement are the same as those for full direct load. The only difference is that you need to replace the value of the full field with inc or inc_replace.

MySQL mode

Oracle mode

Example 1: Full example of incremental direct load using the INSERT INTO SELECT statement.

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

Query whether table tbl1 contains data. The result indicates 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 indicates 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, 'inc_replace') 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, 'inc_replace') 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, 'inc_replace') 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 INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.
```
obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_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 indicates that data has been imported to table tbl1.

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

obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'inc_replace') 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(0x7efa518277d0), tbl1.col1(0x7efa518119c0), tbl1.col3(0x7efa51811e00))})}]), partitions(p0),              |
|       column_values([T_HIDDEN_PK(0x7efa51827c10)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7efa51826f50))(0x7efa51828030)], [column_conv(INT, |
|       PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7efa51827390))(0x7efa5182ff60)])                                                                                 |
|   3 - output([T_HIDDEN_PK(0x7efa51827c10)], [ANONYMOUS_VIEW1.col1(0x7efa51826f50)], [ANONYMOUS_VIEW1.col3(0x7efa51827390)]), filter(nil), rowset=16          |
|   4 - output([T_HIDDEN_PK(0x7efa51827c10)], [ANONYMOUS_VIEW1.col1(0x7efa51826f50)], [ANONYMOUS_VIEW1.col3(0x7efa51827390)]), filter(nil), rowset=16          |
|       dop=16                                                                                                                                                 |
|   5 - output([ANONYMOUS_VIEW1.col1(0x7efa51826f50)], [ANONYMOUS_VIEW1.col3(0x7efa51827390)]), filter(nil), rowset=16                                         |
|       access([ANONYMOUS_VIEW1.col1(0x7efa51826f50)], [ANONYMOUS_VIEW1.col3(0x7efa51827390)])                                                                 |
|   6 - output([t2.col1(0x7efa51825f10)], [t2.col3(0x7efa518267c0)]), filter(nil), rowset=16                                                                   |
|   7 - output([t2.col1(0x7efa51825f10)], [t2.col3(0x7efa518267c0)]), filter(nil), rowset=16                                                                   |
|       access([t2.col1(0x7efa51825f10)], [t2.col3(0x7efa518267c0)]), partitions(p0)                                                                           |
|       is_index_back=false, is_global_index=false,                                                                                                            |
|       range_key([t2.__pk_increment(0x7efa51856410)]), range(MIN ; MAX)always true                                                                            |
| Used Hint:                                                                                                                                                   |
| -------------------------------------                                                                                                                        |
|   /*+                                                                                                                                                        |
|                                                                                                                                                              |
|       USE_PLAN_CACHE( NONE )                                                                                                                                 |
|       PARALLEL(16)                                                                                                                                           |
|       ENABLE_PARALLEL_DML                                                                                                                                    |
|       DIRECT(TRUE, 0, 'INC_REPLACE')                                                                                                                         |
|   */                                                                                                                                                         |
| 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, 'INC_REPLACE')                                                                                                                         |
|       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 primary partition for incremental direct load for a RANGE-HASH partitioned table.

The target table is a secondary partitioned table. The primary partition is a RANGE partition, and the secondary partition is a HASH partition. Specify the primary partition for incremental direct load.

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

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, 'inc_replace') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;

Example 1: Full example of incremental direct load using the INSERT INTO SELECT statement.

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

Query whether table tbl3 contains data. The result indicates 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 indicates 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, 'inc_replace') 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, 'inc_replace') 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, 'inc_replace') 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 INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_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 indicates that data has been imported to table tbl3.

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

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

The return result is as follows:

```shell
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================================================                                                                          |
| |ID|OPERATOR                                      |NAME           |EST.ROWS|EST.TIME(us)|                                                                          |
| -----------------------------------------------------------------------------------------                                                                          |
| |0 |PX COORDINATOR                                |               |3       |34          |                                                                          |
| |1 |└─EXCHANGE OUT DISTR                          |:EX10002       |3       |33          |                                                                          |
| |2 |  └─INSERT                                    |               |3       |32          |                                                                          |
| |3 |    └─EXCHANGE IN DISTR                       |               |3       |7           |                                                                          |
| |4 |      └─EXCHANGE OUT DISTR (RANDOM)           |:EX10001       |3       |7           |                                                                          |
| |5 |        └─MATERIAL                            |               |3       |3           |                                                                          |
| |6 |          └─SUBPLAN SCAN                      |ANONYMOUS_VIEW1|3       |3           |                                                                          |
| |7 |            └─LIMIT                           |               |3       |3           |                                                                          |
| |8 |              └─EXCHANGE IN DISTR             |               |3       |3           |                                                                          |
| |9 |                └─EXCHANGE OUT DISTR          |:EX10000       |3       |1           |                                                                          |
| |10|                  └─LIMIT                     |               |3       |1           |                                                                          |
| |11|                    └─PX BLOCK ITERATOR       |               |3       |1           |                                                                          |
| |12|                      └─COLUMN 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([{TBL3: ({TBL3: (TBL3.__pk_increment(0x7efaad22b0e0), TBL3.COL1(0x7efaad2123f0), TBL3.COL3(0x7efaad212830))})}]), partitions(p0),                    |
|       column_values([T_HIDDEN_PK(0x7efaad22b520)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7efaad22a860))(0x7efaad22b930)], [column_conv(NUMBER, |
|       PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7efaad22aca0))(0x7efaad233850)])                                                                                       |
|   3 - output([T_HIDDEN_PK(0x7efaad22b520)], [ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16                |
|   4 - output([T_HIDDEN_PK(0x7efaad22b520)], [ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16                |
|       is_single, dop=1                                                                                                                                             |
|   5 - output([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16                                               |
|   6 - output([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16                                               |
|       access([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)])                                                                       |
|   7 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|       limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil)                                 |
|   8 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|   9 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|       dop=16                                                                                                                                                       |
|  10 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|       limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil)                                 |
|  11 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|  12 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16                                                                         |
|       access([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), partitions(p0)                                                                                 |
|       limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil), is_index_back=false,           |
|        is_global_index=false,                                                                                                                                      |
|       range_key([T2.__pk_increment(0x7efaad25a720)]), range(MIN ; MAX)always true                                                                                  |
| Used Hint:                                                                                                                                                         |
| -------------------------------------                                                                                                                              |
|   /*+                                                                                                                                                              |
|                                                                                                                                                                    |
|       USE_PLAN_CACHE( NONE )                                                                                                                                       |
|       PARALLEL(16)                                                                                                                                                 |
|       ENABLE_PARALLEL_DML                                                                                                                                          |
|       DIRECT(TRUE, 0, 'INC_REPLACE')                                                                                                                               |
|   */                                                                                                                                                               |
| Qb name trace:                                                                                                                                                     |
| -------------------------------------                                                                                                                              |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                   |
|   stmt_id:1, INS$1                                                                                                                                                 |
|   stmt_id:2, SEL$1                                                                                                                                                 |
|   stmt_id:3, parent:SEL$1  > SEL$658037CB > SEL$DCAFFB86                                                                                                           |
| Outline Data:                                                                                                                                                      |
| -------------------------------------                                                                                                                              |
|   /*+                                                                                                                                                              |
|       BEGIN_OUTLINE_DATA                                                                                                                                           |
|       PARALLEL(@"SEL$DCAFFB86" "T2"@"SEL$1" 16)                                                                                                                    |
|       FULL(@"SEL$DCAFFB86" "T2"@"SEL$1")                                                                                                                           |
|       USE_COLUMN_TABLE(@"SEL$DCAFFB86" "T2"@"SEL$1")                                                                                                               |
|       MERGE(@"SEL$658037CB" < "SEL$1")                                                                                                                             |
|       USE_PLAN_CACHE( NONE )                                                                                                                                       |
|       PARALLEL(16)                                                                                                                                                 |
|       ENABLE_PARALLEL_DML                                                                                                                                          |
|       OPTIMIZER_FEATURES_ENABLE('4.3.3.0')                                                                                                                         |
|       DIRECT(TRUE, 0, 'INC_REPLACE')                                                                                                                               |
|       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                                                                                                                  |
|       Direct-mode is enabled in insert into select                                                                                                                 |
|   Expr Constraints:                                                                                                                                                |
|       cast(FLOOR(cast(10000, NUMBER(-1, -85))), BIGINT(-1, 0)) >= 1 result is TRUE                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
96 rows in set (0.007 sec)

Example 2: Import data to a partition level by specifying the major partitions.

The destination table is a minor partitioned table. The major partitions are Range partitioned, and the minor partitions are Hash partitioned. The minor partitions are imported incrementally by using direct load for the major partitions.

Create a table tbl1 with two major partitions. The major partitions are Range partitioned, and the minor partitions are Hash partitioned.

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 major partitions p0 and p1 of table tbl1.

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

Import data using the CREATE TABLE AS SELECT statement

The CREATE TABLE AS SELECT statement specifies the direct load method by using the DIRECT() and PARALLE() hints. If no hints are specified, the import behavior is determined based on the configuration item default_load_mode.

Syntax

CREATE /*+ [DIRECT(need_sort,max_error,{'inc'|'inc_replace'})] parallel(N) */ TABLE table_name [AS] select_sentence

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

Parameters:

Parameter Description

DIRECT()

Parameter	Description
DIRECT()	Enables direct load by using the hint. `DIRECT()` parameters are described as follows: `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 will fail. `inc\|inc_replace`: specifies the two modes of incremental direct load. The value must be enclosed in single quotes. `inc`: specifies incremental import. It checks whether the primary key is duplicated and supports the `INSERT` and `IGNORE` semantics. `inc_replace`: specifies incremental import without checking whether the primary key is duplicated. It is equivalent to incremental import with the `REPLACE` semantics. Notice When `load_mode` is set to `inc_replace`, the `REPLACE` or `IGNORE` keyword cannot be specified in the `LOAD DATA` statement.
parallel(N)	The parallelism of data loading. This parameter is required. The value is an integer greater than 1.

Enables direct load by using the hint. DIRECT() parameters are described as follows:

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 will fail.
inc|inc_replace: specifies the two modes of incremental direct load. The value must be enclosed in single quotes.
- inc: specifies incremental import. It checks whether the primary key is duplicated and supports the INSERT and IGNORE semantics.
- inc_replace: specifies incremental import without checking whether the primary key is duplicated. It is equivalent to incremental import with the REPLACE semantics.

Notice

When load_mode is set to inc_replace, the REPLACE or IGNORE keyword cannot be specified in the LOAD DATA statement.

parallel(N) The parallelism of data loading. This parameter is required. The value is an integer greater than 1.

Examples

MySQL mode

Oracle mode

Use direct load to import data from table tbl1 to another table. Perform the following steps in sequence.

Create table tbl1.

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

Insert data into table tbl1.

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

Query data from table tbl1.

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.
- Specify the hint for the CREATE TABLE AS SELECT statement.
  - Use the inc hint for direct load. The inc hint is specified as follows: DIRECT(need_sort = false, max_error = 100, inc).
```
obclient [test]> CREATE /*+ direct(true, 0, 'inc') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
```
  - Use the inc_replace hint for direct load. The inc_replace hint is specified as follows: DIRECT(need_sort = false, max_error = 100, inc_replace).
```
obclient [test]> CREATE /*+ direct(true, 0, 'inc_replace') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
```
- Do not specify the hint for the CREATE TABLE AS SELECT statement.
  
  Set the value of the default_load_mode parameter to INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.
```
obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_DIRECT_WRITE';
```
```
obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;
```

Verify whether data has been imported to table tbl2.

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 table tbl2.

Use direct load to import data from table tbl1 to another table. Perform the following steps in sequence.

Create table tbl1.

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

Insert data into table tbl1.

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

Query data from table tbl1.

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 table tbl1 to table tbl2.
- Specify the hint for the CREATE TABLE AS SELECT statement.
  - Use the inc hint for direct load. The inc hint is specified as follows: DIRECT(need_sort = false, max_error = 100, inc).
```
obclient [SYS]> CREATE /*+ direct(true, 0, 'inc') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
```
  - Use the inc_replace hint for direct load. The inc_replace hint is specified as follows: DIRECT(need_sort = false, max_error = 100, inc_replace).
```
obclient [SYS]> CREATE /*+ direct(true, 0, 'inc_replace') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
```
- Do not specify the hint for the CREATE TABLE AS SELECT statement.
  
  Set the value of the default_load_mode parameter to INC_DIRECT_WRITE or INC_REPLACE_DIRECT_WRITE.
```
obclient [SYS]> ALTER SYSTEM SET default_load_mode ='INC_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

Incremental direct load

Incremental direct load strategy

Considerations

Use the LOAD DATA statement to perform a direct load

Limitations

Syntax

Note

Notice

Note

Note

Examples

Note

Note

Notice

Note

Notice

Use the INSERT INTO SELECT statement to bypass data import

Limitations

Syntax

Note

Example

Import data using the CREATE TABLE AS SELECT statement

Syntax

Notice

Examples

References