Import data in bypass mode by using the INSERT INTO SELECT statement

The INSERT INTO SELECT statement uses the append keyword and the enable_parallel_dml parameter as hints to enable 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 SELECT statement cannot be used in triggers.
• The INSERT INTO SELECT statement supports loading LOBs but the loading performance is poor. Therefore, LOBs are still loaded to the corresponding paths by using transactions.
• The INSERT INTO SELECT statement cannot be executed in a multi-row transaction.

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).

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 of the return result of the EXPLAIN EXTENDED statement, check whether the data is written by using bypass import.

obclient [test]> EXPLAIN EXTENDED INSERT /*+ append enable_parallel_dml parallel(16) */INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;

More information

• Migrate data between tables
• View an execution plan