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 scenarios such as initial loading or full data overwrite, while incremental direct load is suitable for batch append 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.
- During the import process, two write statements cannot be executed simultaneously (that is, a table cannot be written to simultaneously), 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 inside 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 (MySQL-compatible mode) and INSERT (Oracle-compatible mode).
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 during direct load for a specified partition:
|
Examples
MySQL-compatible mode
Oracle mode
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 data. The result shows the table is empty.obclient [test]> SELECT * FROM tbl1; Empty setCheck if table
tbl2contains data.obclient [test]> SELECT * FROM tbl2;The query confirms 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 partition 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 the 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 return 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 partition for direct load.
The target table is a subpartitioned table with RANGE partitions as primary partitions and HASH partitions as subpartitions. Specify a primary partition for full direct load.
Create 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;
Example 1: A complete example of using INSERT INTO SELECT for full direct load.
Use direct load to import some data from table tbl4 into table tbl3.
Query whether table
tbl3contains data. The result shows the table is empty.obclient [test]> SELECT * FROM tbl3; Empty setQuery whether table
tbl4contains data.obclient [test]> SELECT * FROM tbl4;The query shows table
tbl4contains data.+------+------+------+ | COL1 | COL2 | COL3 | +------+------+------+ | 1 | a1 | 11 | | 2 | a2 | 22 | | 3 | a3 | 33 | +------+------+------+ 3 rows in set (0.000 sec)Use direct load to import data from table
tbl4into tabletbl3.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 tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000; 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 tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 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 tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 partition(p0sp0_1, p1sp1_1) t2; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0
Do not specify hints 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 tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;
Verify whether data has been imported into table
tbl3.obclient [test]> SELECT * FROM tbl3;The query result is as follows:
+------+------+ | col1 | col3 | +------+------+ | 1 | 11 | | 2 | 22 | | 3 | 33 | +------+------+ 3 rows in setThe result shows data has been imported into table
tbl3.(Optional) In the
Notesection of the results returned by 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 tbl3 SELECT t2.col1,t2.col3 FROM tbl4 t2;The return result is as follows:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------- | | |0 |OPTIMIZER STATS MERGE | |3 |29 | | | |1 |└─PX COORDINATOR | |3 |29 | | | |2 | └─EXCHANGE OUT DISTR |:EX10001 |3 |27 | | | |3 | └─INSERT | |3 |27 | | | |4 | └─OPTIMIZER STATS GATHER | |3 |1 | | | |5 | └─EXCHANGE IN DISTR | |3 |1 | | | |6 | └─EXCHANGE OUT DISTR (RANDOM) |:EX10000 |3 |1 | | | |7 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|3 |1 | | | |8 | └─PX BLOCK ITERATOR | |3 |1 | | | |9 | └─COLUMN TABLE FULL SCAN|T2 |3 |1 | | | =================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil), rowset=16 | | 1 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 | | 2 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 | | dop=16 | | 3 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil) | | columns([{TBL3: ({TBL3: (TBL3.__pk_increment(0x7ef8f6027fa0), TBL3.COL1(0x7ef8f6011d50), TBL3.COL3(0x7ef8f6012190))})}]), partitions(p0), | | column_values([T_HIDDEN_PK(0x7ef8f60283e0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER, | | PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]) | | 4 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)], | | [T_HIDDEN_PK(0x7ef8f60283e0)]), filter(nil), rowset=16 | | 5 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16 | | 6 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16 | | dop=16 | | 7 - output([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16 | | access([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]) | | 8 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16 | | 9 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16 | | access([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([T2.__pk_increment(0x7ef8f60584a0)]), 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_COLUMN_TABLE(@"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:[TBL4] | | 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 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 82 rows in set (0.006 sec)
Example 2: For a RANGE-HASH subpartitioned table, specify the direct load of data for a partition.
The target table is subpartitioned, with RANGE partitions as the primary partitioning and HASH partitions as the secondary partitioning. Specify to directly load all data in the primary partition.
Create a table
tbl1containing two partitions, with RANGE partitions as the primary partitioning and HASH partitions as the secondary partitioning.obclient [test]> CREATE TABLE tbl1_1 ( col1 INT, col2 INT ) PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col2) SUBPARTITION TEMPLATE ( SUBPARTITION sp1, SUBPARTITION sp2, SUBPARTITION sp3 ) ( 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.
- Two write statements cannot be executed simultaneously during the import process (that is, a table cannot be written to simultaneously), because a table lock is acquired at the beginning of the import and only read operations are allowed throughout the import.
- Trigger usage is not supported.
- 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 (MySQL-compatible mode) and INSERT (Oracle-compatible mode).
Parameter description:
Parameter |
Description |
|
|---|---|---|
| DIRECT() | Use the hint to enable direct load. The parameters of DIRECT() are described as follows:
|
inc_replace: Indicates the two modes of incremental direct load. The value must be enclosed in English single quotes.
|
| 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 during direct load for a specified partition:
|
Usage example
The procedure for incremental direct load using the INSERT INTO SELECT statement is the same as that for full direct load, except that the value of the full field is replaced with inc or inc_replace.
MySQL-compatible mode
Oracle mode
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;The query indicates table
tbl2contains data.+------+------+------+ | 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 the 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 if 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 results returned by theEXPLAIN EXTENDEDstatement, check whether the written data was imported via 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 return 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 secondary partitioned table, specify a primary partition for direct load.
The target table is a secondary partitioned table, with RANGE partitions as primary partitions and HASH partitions as secondary partitions. Specify a primary partition for incremental direct load.
Create table
tbl1with two partitions: one RANGE partition and one HASH partition.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;
Example 1: A complete example of incremental direct load using INSERT INTO SELECT.
Use direct load to import partial data from table tbl4 into tbl3.
Query whether table
tbl3contains data. It shows the table is empty.obclient [test]> SELECT * FROM tbl3; Empty setQuery whether table
tbl4contains data.obclient [test]> SELECT * FROM tbl4;The query finds that table
tbl4contains data.+------+------+------+ | COL1 | COL2 | COL3 | +------+------+------+ | 1 | a1 | 11 | | 2 | a2 | 22 | | 3 | a3 | 33 | +------+------+------+ 3 rows in set (0.000 sec)Use direct load to import data from table
tbl4into tabletbl3.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 tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0(Optional) Specify the partition to import.
obclient [test]> INSERT /*+ DIRECT(true, 0, 'inc_replace') enable_parallel_dml parallel(16) */ INTO tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 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 tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 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 tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;
Verify whether data has been imported into table
tbl3.obclient [test]> SELECT * FROM tbl3;The query result is as follows:
+------+------+ | col1 | col3 | +------+------+ | 1 | 11 | | 2 | 22 | | 3 | 33 | +------+------+ 3 rows in setThe result shows that data has been imported into table
tbl3.(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 tbl3 SELECT t2.col1,t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000; The return result is as follows: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------------- | | |0 |PX COORDINATOR | |3 |34 | | | |1 |└─EXCHANGE OUT DISTR |:EX10002 |3 |33 | | | |2 | └─INSERT | |3 |32 | | | |3 | └─EXCHANGE IN DISTR | |3 |7 | | | |4 | └─EXCHANGE OUT DISTR (RANDOM) |:EX10001 |3 |7 | | | |5 | └─MATERIAL | |3 |3 | | | |6 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|3 |3 | | | |7 | └─LIMIT | |3 |3 | | | |8 | └─EXCHANGE IN DISTR | |3 |3 | | | |9 | └─EXCHANGE OUT DISTR |:EX10000 |3 |1 | | | |10| └─LIMIT | |3 |1 | | | |11| └─PX BLOCK ITERATOR | |3 |1 | | | |12| └─COLUMN 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([{TBL3: ({TBL3: (TBL3.__pk_increment(0x7efaad22b0e0), TBL3.COL1(0x7efaad2123f0), TBL3.COL3(0x7efaad212830))})}]), partitions(p0), | | column_values([T_HIDDEN_PK(0x7efaad22b520)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7efaad22a860))(0x7efaad22b930)], [column_conv(NUMBER, | | PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7efaad22aca0))(0x7efaad233850)]) | | 3 - output([T_HIDDEN_PK(0x7efaad22b520)], [ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16 | | 4 - output([T_HIDDEN_PK(0x7efaad22b520)], [ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16 | | is_single, dop=1 | | 5 - output([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16 | | 6 - output([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]), filter(nil), rowset=16 | | access([ANONYMOUS_VIEW1.COL1(0x7efaad22a860)], [ANONYMOUS_VIEW1.COL3(0x7efaad22aca0)]) | | 7 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil) | | 8 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | 9 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | dop=16 | | 10 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil) | | 11 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | 12 - output([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), filter(nil), rowset=16 | | access([T2.COL1(0x7efaad229470)], [T2.COL3(0x7efaad229f40)]), partitions(p0) | | limit(cast(FLOOR(cast(10000, NUMBER(-1, -85))(0x7efaad227ef0))(0x7efaad25ac30), BIGINT(-1, 0))(0x7efaad25b6d0)), offset(nil), is_index_back=false, | | is_global_index=false, | | range_key([T2.__pk_increment(0x7efaad25a720)]), 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 | | stmt_id:3, parent:SEL$1 > SEL$658037CB > SEL$DCAFFB86 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PARALLEL(@"SEL$DCAFFB86" "T2"@"SEL$1" 16) | | FULL(@"SEL$DCAFFB86" "T2"@"SEL$1") | | USE_COLUMN_TABLE(@"SEL$DCAFFB86" "T2"@"SEL$1") | | MERGE(@"SEL$658037CB" < "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:[TBL4] | | 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 | | Expr Constraints: | | cast(FLOOR(cast(10000, NUMBER(-1, -85))), BIGINT(-1, 0)) >= 1 result is TRUE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 96 rows in set (0.007 sec)
Example 2: For a RANGE-HASH subpartitioned table, specify the partition for direct load.
The target table is subpartitioned, with RANGE partitions as the primary and HASH partitions as the secondary. Specify an incremental direct load for a partition.
Create a table
tbl1with two partitions: one RANGE partition and one HASH partition.obclient [test]> CREATE TABLE tbl1_1 ( col1 INT, col2 INT ) PARTITION BY RANGE (col1) SUBPARTITION BY HASH (col2) SUBPARTITION TEMPLATE ( SUBPARTITION sp1, SUBPARTITION sp2, SUBPARTITION sp3 ) ( 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;