You can specify the APPEND | direct() hint and the enable_parallel_dml parameter in the INSERT INTO SELECT statement to enable direct load. Extract-transform-load (ETL) is accelerated in direct load mode.
APPEND hint
You can add the APPEND hint to the INSERT INTO SELECT statement to enable direct load. For more information, see Import data through direct load by using the INSERT INTO SELECT statement.
The syntax of the hint is as follows:
/*+ APPEND */
Use the APPEND hint in the INSERT INTO SELECT statement to enable direct load
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 following table describes the parameters.
| Parameter | Description |
|---|---|
| APPEND | direct() | Specifies to enable direct load.
NoticeWhen the value of |
| enable_parallel_dml parallel(N) | The degree of parallelism (DOP) for loading data. The default value of N is 4.
NoteGenerally, the |
| parallel(N) | The DOP for loading data. The default value of N is 4. |
Enable full direct load
Limitations
- Only PDML data can be imported in direct load mode. For more information about PDML, see Parallel DML.
- Two write statements for writing data to one table cannot be executed at the same time. The reason is that the table is locked during data import and only read operations are supported.
- Full direct load is not supported for tables with triggers.
- Tables with generated columns are not supported. Some indexes generate hidden generated columns, for example, KEY
idx_c2(c2(16)) GLOBAL. - Liboblog and flashback queries are not supported.
Examples
Import part of the 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 /*+ 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
The incremental direct load feature is currently in the experimental stage. To ensure system stability, we recommend that you do not use it in a production environment.
Limitations
- Tables with indexes (excluding primary keys) are not supported.
- Tables with foreign keys are not supported.
- Tables or data that contain LOB data whose size exceeds the value of
OB_INROW_THRESHOLDare not supported.
Considerations
Data imported in incremental direct load mode triggers minor compactions. We recommend that you do not use incremental direct load for a small amount of data that can be imported within minutes.
Incremental direct load supports a maximum of 8,000 partitions.
Examples
The procedure for enabling incremental direct load by using the INSERT INTO SELECT statement is the same as that for enabling full direct load, except that the full field is replaced with the inc_replace field.
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