When prerequisites such as parallel DML are met, you can enable direct load for INSERT INTO SELECT using hints like APPEND, DIRECT(...), and enable_parallel_dml. If hints are not explicitly specified, you can also control the import behavior by configuring the default_load_mode parameter.
Based on the write target and processing method, direct load of INSERT INTO SELECT can be divided into full direct load and incremental direct load. Full direct load is suitable for initial loading or full overwriting scenarios, while incremental direct load is suitable for batch appending scenarios based on existing data. The following sections describe the limitations, syntax, and usage examples for each method.
Full direct load
Limitations
- Only Parallel Data Manipulation Language (PDML) is supported. Non-PDML operations cannot use direct load. For more information about parallel DML, see Parallel DML.
- Two write statements cannot be executed simultaneously during the import process (that is, one table cannot be written to at a time), because a table lock is acquired at the beginning of the import and only read operations are allowed throughout the import.
- Direct load is a DDL statement and cannot be executed within a multi-row transaction (a transaction containing multiple operations).
- It cannot be executed within a BEGIN block.
- Autocommit must be set to 1.
- It is not supported for use with triggers.
- Tables with generated columns are not supported (some indexes create hidden generated columns, for example, KEY
idx_c2(c2(16)) GLOBAL). - Liboblog and flashback queries are not supported.
Syntax
INSERT /*+ [APPEND |DIRECT(need_sort,max_error,'full')] enable_parallel_dml parallel(N) */ INTO table_name [PARTITION(PARTITION_OPTION)] select_sentence
For more information about the INSERT INTO syntax, see INSERT.
Parameter description:
Parameter |
Description |
|---|---|
| APPEND | DIRECT() | Use hints to enable the direct load feature.
|
| enable_parallel_dml | The degree of parallelism for loading data.
NoteIn general, the |
| parallel(N) | The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1. |
| table_name | The name of the table into which data is imported. You can specify any number of columns. |
| PARTITION_OPTION | The partition name for partition-level direct load:
|
Examples
Example 1: A complete example of using INSERT INTO SELECT for full direct load.
Use direct load to import some data from table tbl2 into table tbl1.
Check if table
tbl1contains any data. The result shows the table is empty.obclient [test]> SELECT * FROM tbl1; Empty setCheck if table
tbl2contains any data.obclient [test]> SELECT * FROM tbl2;The query finds that table
tbl2contains data.+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | a1 | 11 | | 2 | a2 | 22 | | 3 | a3 | 33 | +------+------+------+ 3 rows in setUse direct load to import the data from table
tbl2into tabletbl1.Specify hints for the
INSERT INTO SELECTstatement.Do not specify a partition for import.
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(Optional) Specify a partition for import.
obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') 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: 0obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') 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 a hint for the
INSERT INTO SELECTstatement.Set the value of the
default_load_modeparameter toFULL_DIRECT_WRITE.obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';obclient [test]> INSERT INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl1 t2;
Verify whether data has been imported into table
tbl1.obclient [test]> SELECT * FROM tbl1;The query result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 11 | | 2 | 22 | | 3 | 33 | +------+------+ 3 rows in setThe result shows that data has been imported into table
tbl1.(Optional) In the
Notesection of the return result from theEXPLAIN EXTENDEDstatement, check whether the data was 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;The 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(0x7efa63627790), tbl1.col1(0x7efa63611980), tbl1.col3(0x7efa63611dc0))})}]), partitions(p0), | | column_values([T_HIDDEN_PK(0x7efa63627bd0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7efa63626f10))(0x7efa63627ff0)], [column_conv(INT, | | PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7efa63627350))(0x7efa6362ff20)]) | | 3 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16 | | 4 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16 | | dop=16 | | 5 - output([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16 | | access([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]) | | 6 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16 | | 7 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16 | | access([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.__pk_increment(0x7efa63656410)]), range(MIN ; MAX)always true | | Used Hint: | | ------------------------------------- | | /*+ | | | | USE_PLAN_CACHE( NONE ) | | PARALLEL(16) | | ENABLE_PARALLEL_DML | | DIRECT(TRUE, 0, 'FULL') | | */ | | 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, 'FULL') | | 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: For a RANGE-HASH subpartitioned table, specify a primary partition for full direct load.
The target table is a subpartitioned table with RANGE partitions as primary partitions and HASH partitions as subpartitions.
Create a table
tbl1with two partitions: one RANGE partition and one HASH subpartition.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
tbl2into partitionsp0andp1of tabletbl1.obclient [test]> insert /*+ direct(true, 0, 'full') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;
Incremental direct load
Limitations
- Only Parallel Data Manipulation Language (PDML) is supported; non-PDML operations cannot use direct load. For more information about parallel DML, see Parallel DML.
- During the import process, two write statements cannot be executed simultaneously (that is, one table cannot be written to at a time), because a table lock is acquired at the beginning of the import and only read operations are allowed throughout the import.
- Direct load is not supported for tables with triggers.
- Tables with generated columns are not supported (some indexes create hidden generated columns, for example, KEY
idx_c2(c2(16)) GLOBAL). - Liboblog and flashback queries are not supported.
- Tables with indexes (excluding primary keys) do not support incremental direct load.
- Tables with foreign keys do not support incremental direct load.
- Tables with
GLOBALindexes do not support incremental direct load. - Tables with constraints do not support incremental direct load.
- Tables without a primary key that have more than one unique
LOCALindex do not support incremental direct load. - Tables with a primary key and a unique index do not support 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.
Parameter description:
Parameter |
Description |
|---|---|
| DIRECT() | Use the hint to enable direct load. The parameters of DIRECT() are described as follows:
|
| enable_parallel_dml | The degree of parallelism for loading data.
NoteIn general, the |
| parallel(N) | The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1. |
| table_name | The name of the table into which data is imported. You can specify any number of columns. |
| PARTITION_OPTION | The partition name for partition-level direct load:
|
Examples
The procedure for incremental direct load using the INSERT INTO SELECT statement is the same as for full direct load, except that the value of the full field is replaced with inc or inc_replace.
Example 1: A complete example of incremental direct load using INSERT INTO SELECT.
Use direct load to import some data from table tbl2 into table tbl1.
Check if table
tbl1contains any data. The result shows the table is empty.obclient [test]> SELECT * FROM tbl1; Empty setCheck if table
tbl2contains any data.obclient [test]> SELECT * FROM tbl2;Data is found in table
tbl2.+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | a1 | 11 | | 2 | a2 | 22 | | 3 | a3 | 33 | +------+------+------+ 3 rows in setUse direct load to import data from table
tbl2into tabletbl1.Specify the hint for the
INSERT INTO SELECTstatement.Do not specify a partition for import.
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 a partition for import.
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: 0obclient [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 a hint for the
INSERT INTO SELECTstatement.Set the value of the
default_load_modeparameter toINC_DIRECT_WRITEorINC_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;
Verify whether data has been imported into table
tbl1.obclient [test]> SELECT * FROM tbl1;The query result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 11 | | 2 | 22 | | 3 | 33 | +------+------+ 3 rows in setThe result shows that data has been imported into table
tbl1.(Optional) In the
Notesection of the return result from theEXPLAIN EXTENDEDstatement, check whether the data was written through 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 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: For a RANGE-HASH subpartitioned table, specify a primary partition for incremental direct load.
The target table is a subpartitioned table with RANGE partitions as primary partitions and HASH partitions as subpartitions.
Create a table
tbl1with two partitions: one RANGE partition and one HASH subpartition.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
tbl2into partitionsp0andp1of tabletbl1.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;