The INSERT INTO SELECT statement uses the append keyword and the enable_parallel_dml parameter as hints to enable bypass import.
Note
- Columnstore tables support bypass import.
LOBcolumns support bypass import.
Limitations
Only parallel data manipulation language (PDML) data can be imported in bypass mode.
You cannot execute two statements to write the same table at a time because a lock is added to the table during import.
The
INSERT INTO SELECTstatement cannot be used in triggers.The
INSERT INTO SELECTstatement cannot be executed in a multi-row transaction that contains multiple operations.
Syntax
INSERT /*+ append 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 | Specifies to use bypass import. |
| enable_parallel_dml parallel(N) | The DOP for loading data. The default value of N is 4.
NoteGenerally, the |
Examples
Import part of the data in the tbl2 table to the tbl1 table in bypass mode.
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 /*+ append 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 bypass import.
obclient [test]> EXPLAIN EXTENDED INSERT /*+ append 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