In the INSERT INTO SELECT statement, the APPEND or direct() hint is used in combination with the enable_parallel_dml hint to implement direct load.
Full direct load
Limitations
Direct load is supported only for parallel DML. For more information about parallel DML, see Parallel DML.
During the import, you cannot concurrently execute two write statements to write data into the same table, because the table is locked. You can perform only read operations during the import.
The import is not supported for tables with triggers.
The import is not supported for tables with generated columns. Some indexes will generate hidden generated columns, such as KEY
idx_c2(c2(16)) GLOBAL.Data obtained by Liboblog or flashback queries cannot be imported.
Direct load is a DDL statement and cannot be executed within a multi-statement transaction (a transaction containing multiple operations).
- Direct load cannot be executed within a
BEGINblock. - The
autocommitsetting must be set to 1.
- Direct load cannot be executed within a
Syntax
INSERT /*+ [APPEND |direct(need_sort,max_error,'full')] enable_parallel_dml parallel(N) */ INTO table_name select_sentence
For more information about the syntax of the INSERT INTO statement, see INSERT (MySQL mode) and INSERT (Oracle mode).
The parameters are described as follows:
| Parameter | Description |
|---|---|
| APPEND | direct() | Specifies to enable the direct load feature.
|
| enable_parallel_dml parallel(N) | The DOP for loading data.
NoteGenerally, the |
| parallel(N) | The DOP for loading data. This parameter is required and must be an integer greater than 1. |
Examples
Import part of the data in the tbl2 table to the tbl1 table through direct load.
obclient [test]> SELECT * FROM tbl1;
Empty set
obclient [test]> SELECT * FROM tbl2;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 1 | a1 | 11 |
| 2 | a2 | 22 |
| 3 | a3 | 33 |
+------+------+------+
3 rows in set
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
obclient [test]> SELECT * FROM tbl1;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+------+------+
3 rows in set
In the Note section of the return result of the EXPLAIN EXTENDED statement, check whether the data is written through direct load.
obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------------- |
| |0 |OPTIMIZER STATS MERGE | |3 |27 | |
| |1 | PX COORDINATOR | |3 |27 | |
| |2 | EXCHANGE OUT DISTR |:EX10001 |3 |27 | |
| |3 | INSERT | |3 |26 | |
| |4 | EXCHANGE IN DISTR | |3 |1 | |
| |5 | EXCHANGE OUT DISTR (RANDOM)|:EX10000 |3 |1 | |
| |6 | OPTIMIZER STATS GATHER | |3 |1 | |
| |7 | SUBPLAN SCAN |ANONYMOUS_VIEW1|3 |1 | |
| |8 | PX BLOCK ITERATOR | |3 |1 | |
| |9 | TABLE SCAN |t2 |3 |1 | |
| =========================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)]), |
| filter(nil), rowset=256 |
| 2 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)]), |
| filter(nil), rowset=256 |
| dop=16 |
| 3 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)]), |
| filter(nil) |
| columns([{tbl1: ({tbl1: (tbl1.__pk_increment(0x7f0ba6a51d80), tbl1.col1(0x7f0ba6a30a90), tbl1.col2(0x7f0ba6a30d50))})}]), partitions(p0), |
| column_values([T_HIDDEN_PK(0x7f0ba6a52040)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)]) |
| 4 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)], |
| [T_HIDDEN_PK(0x7f0ba6a52040)]), filter(nil), rowset=256 |
| 5 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)], |
| [T_HIDDEN_PK(0x7f0ba6a52040)]), filter(nil), rowset=256 |
| dop=16 |
| 6 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7f0ba6a51800))(0x7f0ba6a522c0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0))(0x7f0ba6a59630)]), |
| filter(nil), rowset=256 |
| 7 - output([ANONYMOUS_VIEW1.col1(0x7f0ba6a51800)], [ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0)]), filter(nil), rowset=256 |
| access([ANONYMOUS_VIEW1.col1(0x7f0ba6a51800)], [ANONYMOUS_VIEW1.col3(0x7f0ba6a51ac0)]) |
| 8 - output([t2.col1(0x7f0ba6a50d40)], [t2.col3(0x7f0ba6a512f0)]), filter(nil), rowset=256 |
| 9 - output([t2.col1(0x7f0ba6a50d40)], [t2.col3(0x7f0ba6a512f0)]), filter(nil), rowset=256 |
| access([t2.col1(0x7f0ba6a50d40)], [t2.col3(0x7f0ba6a512f0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.__pk_increment(0x7f0ba6a6ccf0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| USE_PLAN_CACHE( NONE ) |
| PARALLEL(16) |
| ENABLE_PARALLEL_DML |
| APPEND |
| APPEND |
| */ |
| 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 |
| FULL(@"SEL$1" "test"."t2"@"SEL$1") |
| USE_PLAN_CACHE( NONE ) |
| PARALLEL(16) |
| ENABLE_PARALLEL_DML |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| APPEND |
| APPEND |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t2: |
| table_rows:3 |
| physical_range_rows:3 |
| logical_range_rows:3 |
| index_back_rows:0 |
| output_rows:3 |
| est_method:local_storage |
| optimization_method:cost_based |
| avaiable_index_name:[tbl2] |
| table_id:500004:estimation info:(table_type:12, version:-1--1--1, logical_rc:3, physical_rc:3)] |
| stats version:0 |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 16 because of hint |
| Direct-mode is enabled in insert into select |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
86 rows in set
Incremental direct load
Notice
At present, the incremental direct load feature is still in the experimental stage. Therefore, we recommend that you do not use this feature in a production environment to avoid affecting system stability.
Limitations
- Tables with indexes (except a primary key) do not support incremental direct load.
- Tables with foreign keys do not support incremental direct load.
- If the total size of existing LOB data in the destination table and the LOB data to be imported exceeds the value specified by
OB_INROW_THRESHOLDfor the table, incremental direct load is not supported.
Considerations
A minor compaction will be triggered after incremental direct load. If the data size is small and the import can be done within minutes, we recommend that you do not use incremental direct load.
The number of partitions involved in incremental direct load cannot exceed 8,000.
Syntax
INSERT /*+ [APPEND | direct(need_sort,max_error,'inc_replace')] enable_parallel_dml parallel(N) */ INTO table_name select_sentence
For more information about the syntax of the INSERT INTO statement, see INSERT (MySQL mode) and INSERT (Oracle mode).
The parameters are described as follows:
| Parameter | Description |
|---|---|
| APPEND | direct() | Specifies to enable the direct load feature.
NoticeWhen the value of |
| enable_parallel_dml parallel(N) | The DOP for loading data.
NoteGenerally, the |
| parallel(N) | The DOP for loading data. This parameter is required and must be an integer greater than 1. |
Examples
The incremental direct load procedure is the same as the full direct load procedure. You only need to replace full with inc_replace in the INSERT INTO SELECT statement.
obclient [test]> INSERT /*+ direct(true, 0, 'inc_replace') enable_parallel_dml parallel(2) */ INTO tbl2 SELECT * FROM tbl1;
The return result is as follows:
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0