--- title: "使用 INSERT INTO SELECT 语句旁路导入数据 - OceanBase 数据库 V4.1.0 | OceanBase 文档中心" description: 使用 INSERT INTO SELECT 语句旁路导入数据 INSERT INTO SELECT 语句通过 Hint 使用 append 加上 enable_parallel_dml 来走旁路导入。 使用限制 只支持 PDML（Parallel Data Manipulation Language，并行数据操纵语言… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.1.0 # 使用 INSERT INTO SELECT 语句旁路导入数据 更新时间：2024-09-10 11:46:15 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.1.0/zh-CN/500.data-migration/1100.bypass-import/300.use-insert-into-select-statement-to-bypass-import-data.md) `INSERT INTO SELECT` 语句通过 Hint 使用 `append` 加上 `enable_parallel_dml` 来走旁路导入。 ## 使用限制 - 只支持 PDML（Parallel Data Manipulation Language，并行数据操纵语言），非 PDML 不能用旁路导入。 - 不能两个语句同时写一个表，因为导入的过程中会先加表锁。 - 不支持在触发器（Trigger）使用。 - 支持 `lob` 类型，但是性能比较差，`lob` 会走原来事务写入数据的路径。 - 不能在多行事务（包含多个操作的事务）中运行。 ## 语法 ```sql INSERT /*+ append enable_parallel_dml parallel(N) */ INTO table_name select_sentence ``` 更多 `INSERT INTO` 语法的信息，请参见 [INSERT（MySQL 模式）](https://www.oceanbase.com/docs/common-oceanbase-database-cn-0000000001967395) 和 [INSERT（Oracle 模式）](https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001701441)。 **参数解释：** | 参数 | 描述 | | --- | --- | | append | 表示走旁路导入。 | | enable_parallel_dml parallel(N) | 加载数据的并行度。 > **说明** > 一般情况下，`enable_parallel_dml` Hint 和 `parallel` Hint 必须配合使用才能开启并行 DML。不过，当目标表的 Schema 上指定了表级别的并行度时，仅需指定 `enable_parallel_dml` Hint。 | ## 示例 使用旁路导入将表 `tbl2` 中的部分数据导入到 `tbl1` 中。 ```shell 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 ``` 在 `EXPLAIN EXTENDED` 语句的返回结果的 `Note` 中，查看是否通过旁路导入写入的数据。 ```shell 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 ``` ## 相关文档 - [表与表之间的数据迁移](https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001687929) - [查看执行计划](https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001700611) 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线