You can use the INSERT INTO SELECT statement with the append and the enable_parallel_dml hints to import data in direct load mode.
Limitations
Only PDML data can be imported in direct load mode.
You cannot execute two statements to concurrently write data to the same table. This is because the table is locked during data import and only read operations are supported.
This feature is not supported for tables with triggers.
This feature is not supported for tables with generated columns. Some indexes generate hidden generated columns, for example, KEY
idx_c2(c2(16)) GLOBAL).This feature is not supported if the data in a single row exceeds 2 MB in size.
Liboblog and flashback queries are not supported.
This feature supports loading LOBs but the loading performance is poor. Therefore, LOBs are still loaded to the corresponding paths by using transactions.
Direct load is a DDL operation and cannot be executed in a multi-row transaction that contains multiple operations.
- This feature is not supported in a
BEGINblock. autocommitmust be set to1.
- This feature is not supported in a
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 following table describes the parameters in the syntax.
| Parameter | Description |
|---|---|
| append | Specifies to enable direct load. |
| enable_parallel_dml parallel(N) | The degree of parallelism (DOP) for loading data.
NoteGenerally, the |
Examples
Import partial data in the tbl2 table to the tbl1 table in direct load 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 direct load.
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