--- title: 如何通过 default_load_mode 参数开启默认旁路导入-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于如何通过 default_load_mode 参数开启默认旁路导入相关的常见问题和使用技巧，帮助您快速解决如何通过 default_load_mode 参数开启默认旁路导入的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 如何通过 default_load_mode 参数开启默认旁路导入 更新时间：2026-05-21 09:16 适用版本： V4.3.x、V4.4.x 内容类型：TechNote 当通过 `CREATE TABLE AS SELECT` 语句导入数据，且没有指定旁路导入的相关 Hint 时，数据的默认导入方式由配置项 `default_load_mode` 控制。本文将详细说明 `default_load_mode` 参数配置以及使用方法。 ## 详细说明 下面将说明 `default_load_mode` 参数设置配置详情。 | **属性** | **描述** | | --- | --- | | 参数类型 | String | | 默认值 | DISABLED | | 取值范围 | • DISABLED：不使用旁路导入 • FULL_DIRECT_WRITE：使用全量旁路导入，以 SQL 的语义为准 • INC_DIRECT_WRITE：使用增量旁路导入的 INC 模式 • INC_REPLACE_DIRECT_WRITE：使用增量旁路导入的 INC_REPLACE 模式 | | 是否可修改 | 是，支持通过 `ALTER SYSTEM SET` 语句修改 | | 是否重启 OBServer 节点生效 | 否，设置后立即生效 | ### 测试用例 #### 示例一：通过指定 Hint 走旁路导入 ```sql ## 准备测试数据 obclient [TEST_ZQ]> select count(*) from TSK_1108; +----------+ | COUNT(*) | +----------+ | 11605632 | +----------+ 1 row in set (0.046 sec) obclient [TEST_ZQ]> select count(*) from TSK_1108 where d_dev >= '2022-01-13' AND d_dev <= '2023-01-30' AND d_base = '2023-01-30'; +----------+ | COUNT(*) | +----------+ | 1096000 | +----------+ 1 row in set (0.884 sec) ## 添加旁路导入 Hint obclient [TEST_ZQ]> create /*+ direct(false, 0, 'full') enable_parallel_dml parallel(3) */ table TSK_TMP_1108 as select dt.D_DEV,dt.I_CODE,dt.A_TYPE,dt.M_TYPE, COALESCE( dt.F_TPL , 0) AS F_TPL FROM TSK_1108 dt WHERE dt.d_dev >= '2022-01-13' AND dt.d_dev <= '2023-01-30' AND dt.d_base = '2023-01-30'; Query OK, 1096000 rows affected (1.691 sec) ## 查看 outline data 中表示该条语句已走旁路导入，关键字为 DIRECT(FALSE, 0, 'FULL') ## Note 中的 “Direct-mode full is enabled in insert into select” 也表示该条语句走了旁路导入 obclient [TEST_ZQ]> select * from table(dbms_xplan.display_cursor(format=>'all')); +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | COLUMN_VALUE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =============================================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| | | ------------------------------------------------------------------------------------------------------------------------------- | | |0 |PX COORDINATOR | |1096289 |11171734 |0 |857000 |724295 |728880 | | | |1 |└─EXCHANGE OUT DISTR |:EX10001 |1096289 |9686744 |0 |782065 |0 |26 | | | |2 | └─INSERT | |1096289 |8573423 |0 |782065 |0 |702256 | | | |3 | └─EXCHANGE IN DISTR | |1096289 |1630964 |1096000 |776789 |984 |14937 | | | |4 | └─EXCHANGE OUT DISTR (RANDOM)|:EX10000 |1096289 |1135968 |1096000 |730317 |566020 |624066 | | | |5 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1096289 |22647 |1096000 |730317 |0 |602 | | | |6 | └─PX BLOCK ITERATOR | |1096289 |21680 |1096000 |730317 |0 |506 | | | |7 | └─TABLE RANGE SCAN |DT(I1) |1096289 |21680 |1096000 |730317 |0 |51094 | | | =============================================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil), rowset=256 | | 1 - output(nil), filter(nil), rowset=256 | | dop=3 | | 2 - output(nil), filter(nil), rowset=256 | | columns([{__ctas_3221595511_1763090281433356: ({__ctas_3221595511_1763090281433356: (__ctas_3221595511_1763090281433356.__pk_increment, __ctas_3221595511_1763090281433356.D_DEV, | | __ctas_3221595511_1763090281433356.I_CODE, __ctas_3221595511_1763090281433356.A_TYPE, __ctas_3221595511_1763090281433356.M_TYPE, __ctas_3221595511_1763090281433356.F_TPL)})}]), | | partitions(p0), | | column_values([T_HIDDEN_PK], [column_conv(CHAR,utf8mb4_bin,length:10,NOT NULL,ANONYMOUS_VIEW1.D_DEV)], [column_conv(VARCHAR2,utf8mb4_bin,length:80, | | NOT NULL,ANONYMOUS_VIEW1.I_CODE)], [column_conv(VARCHAR2,utf8mb4_bin,length:30,NOT NULL,ANONYMOUS_VIEW1.A_TYPE)], [column_conv(VARCHAR2,utf8mb4_bin,length:30, | | NOT NULL,ANONYMOUS_VIEW1.M_TYPE)], [column_conv(NUMBER,PS:(-1,-85),NULL,ANONYMOUS_VIEW1.F_TPL)]) | | 3 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | 4 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | dop=3 | | 5 - output([ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | access([ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]) | | 6 - output([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [COALESCE(cast(DT.F_TPL, NUMBER(-1, -85)), 0)]), filter(nil), rowset=256 | | 7 - output([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [COALESCE(cast(DT.F_TPL, NUMBER(-1, -85)), 0)]), filter(nil), rowset=256 | | access([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [DT.F_TPL]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([DT.D_BASE], [DT.D_DEV], [DT.__pk_increment]), range(2023-01-30,2022-01-13,MIN ; 2023-01-30,2023-01-30,MAX), | | range_cond([DT.D_DEV >= '2022-01-13'], [DT.D_DEV <= '2023-01-30'], [DT.D_BASE = '2023-01-30']) | | Used Hint: | | ------------------------------------- | | /*+ | | | | PARALLEL(3) | | ENABLE_PARALLEL_DML | | GATHER_OPTIMIZER_STATISTICS | | DIRECT(FALSE, 0, 'FULL') | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, INS$1 | | stmt_id:1, SEL$1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PARALLEL(@"SEL$1" "DT"@"SEL$1" 3) | | INDEX(@"SEL$1" "DT"@"SEL$1" "I1") | | PARALLEL(3) | | DML_PARALLEL(3) | | ENABLE_PARALLEL_DML | | OPTIMIZER_FEATURES_ENABLE('4.3.5.4') | | GATHER_OPTIMIZER_STATISTICS | | DIRECT(FALSE, 0, 'FULL') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | DT: | | table_rows:14992798 | | physical_range_rows:1096289 | | logical_range_rows:1096289 | | output_rows:1096289 | | table_dop:3 | | dop_method:Global DOP | | avaiable_index_name:[IDX_MVR_1208_1, IDX_MVR_1208_2, I1, TSK_1108] | | pruned_index_name:[IDX_MVR_1208_1, IDX_MVR_1208_2, TSK_1108] | | stats info:[version=2025-11-06 18:44:58.733304, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | Plan Type: | | DISTRIBUTED | | Parameters: | | :0 => 0 | | :1 => '2022-01-13' | | :2 => '2023-01-30' | | :3 => '2023-01-30' | | Note: | | Degree of Parallelism is 3 because of hint | | Direct-mode full is enabled in insert into select | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 86 rows in set (0.004 sec) ``` #### 示例二：通过设置 default_load_mode 参数不指定 Hint 也走了旁路导入 ```sql ## 这边看到 default_load_mode 参数是 disable obclient [TEST_ZQ]> show parameters like '%default_load_mode%'; +-------+----------+---------------+----------+-------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | ZONE | SVR_TYPE | SVR_IP | SVR_PORT | NAME | DATA_TYPE | VALUE | INFO | SECTION | SCOPE | SOURCE | EDIT_LEVEL | DEFAULT_VALUE | ISDEFAULT | +-------+----------+---------------+----------+-------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone1 | observer | xx.xxx.211.54 | 2882 | default_load_mode | STRING | disabled | Specifies default load data path."DISABLED" represent load data not in direct load path (default value)."FULL_DIRECT_WRITE" represent load data in full direct load path with insert semantics."INC_DIRECT_WRITE" represent load data in inc direct load path with insert semantics."INC_REPLACE_DIRECT_WRITE" represent load data in inc direct load path with replace semantics. | TENANT | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | DISABLED | 1 | +-------+----------+---------------+----------+-------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ 1 row in set (0.007 sec) ## 登录 sys 租户设置 default_load_mode 参数为 FULL_DIRECT_WRITE obclient(root@sys)[(none)]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE' tenant = 'oracle_test'; Query OK, 0 rows affected (0.008 sec) ## 再回到测试租户发现 default_load_mode 参数已经修改为 FULL_DIRECT_WRITE obclient [TEST_ZQ]> show parameters like '%default_load_mode%'; +-------+----------+---------------+----------+-------------------+-----------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | ZONE | SVR_TYPE | SVR_IP | SVR_PORT | NAME | DATA_TYPE | VALUE | INFO | SECTION | SCOPE | SOURCE | EDIT_LEVEL | DEFAULT_VALUE | ISDEFAULT | +-------+----------+---------------+----------+-------------------+-----------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone1 | observer | xx.xxx.211.54 | 2882 | default_load_mode | STRING | FULL_DIRECT_WRITE | Specifies default load data path."DISABLED" represent load data not in direct load path (default value)."FULL_DIRECT_WRITE" represent load data in full direct load path with insert semantics."INC_DIRECT_WRITE" represent load data in inc direct load path with insert semantics."INC_REPLACE_DIRECT_WRITE" represent load data in inc direct load path with replace semantics. | TENANT | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | DISABLED | 0 | +-------+----------+---------------+----------+-------------------+-----------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ 1 row in set (0.008 sec) ## 同步配置 PDML obclient [TEST_ZQ]> alter session force parallel dml parallel 8; Query OK, 0 rows affected (0.001 sec) ## 可以发现这边的建表时间比上面添加 Hint 还要快；目前走了旁路导入并没有体现在 outline data 中，这个显示问题会在 OceanBase 数据库 V4.5.0 版本中修复 ## Note 中的 “Direct-mode full is enabled in insert into select” 也表示该条语句走了旁路导入 obclient [TEST_ZQ]> CREATE TABLE TSK_TMP_1108 AS select dt.D_DEV,dt.I_CODE,dt.A_TYPE,dt.M_TYPE, COALESCE( dt.F_TPL , 0) AS F_TPL FROM TSK_1108 dt WHERE dt.d_dev >= '2022-01-13' AND dt.d_dev <= '2023-01-30' AND dt.d_base = '2023-01-30'; Query OK, 1096000 rows affected (1.396 sec) obclient [TEST_ZQ]> select * from table(dbms_xplan.display_cursor(format=>'all')); +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | COLUMN_VALUE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =============================================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| | | ------------------------------------------------------------------------------------------------------------------------------- | | |0 |PX COORDINATOR | |1096289 |9456555 |0 |560804 |446595 |452498 | | | |1 |└─EXCHANGE OUT DISTR |:EX10001 |1096289 |7971566 |0 |485764 |0 |42 | | | |2 | └─INSERT | |1096289 |7554071 |0 |485764 |0 |406564 | | | |3 | └─EXCHANGE IN DISTR | |1096289 |611612 |1096000 |480450 |13426 |33176 | | | |4 | └─EXCHANGE OUT DISTR (RANDOM)|:EX10000 |1096289 |425988 |1096000 |427453 |327102 |340647 | | | |5 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1096289 |8493 |1096000 |426397 |0 |703 | | | |6 | └─PX BLOCK ITERATOR | |1096289 |8130 |1096000 |426397 |0 |707 | | | |7 | └─TABLE RANGE SCAN |DT(I1) |1096289 |8130 |1096000 |426397 |0 |60136 | | | =============================================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil), rowset=256 | | 1 - output(nil), filter(nil), rowset=256 | | dop=8 | | 2 - output(nil), filter(nil), rowset=256 | | columns([{__ctas_3221540917_1763107955636961: ({__ctas_3221540917_1763107955636961: (__ctas_3221540917_1763107955636961.__pk_increment, __ctas_3221540917_1763107955636961.D_DEV, | | __ctas_3221540917_1763107955636961.I_CODE, __ctas_3221540917_1763107955636961.A_TYPE, __ctas_3221540917_1763107955636961.M_TYPE, __ctas_3221540917_1763107955636961.F_TPL)})}]), | | partitions(p0), | | column_values([T_HIDDEN_PK], [column_conv(CHAR,utf8mb4_bin,length:10,NOT NULL,ANONYMOUS_VIEW1.D_DEV)], [column_conv(VARCHAR2,utf8mb4_bin,length:80, | | NOT NULL,ANONYMOUS_VIEW1.I_CODE)], [column_conv(VARCHAR2,utf8mb4_bin,length:30,NOT NULL,ANONYMOUS_VIEW1.A_TYPE)], [column_conv(VARCHAR2,utf8mb4_bin,length:30, | | NOT NULL,ANONYMOUS_VIEW1.M_TYPE)], [column_conv(NUMBER,PS:(-1,-85),NULL,ANONYMOUS_VIEW1.F_TPL)]) | | 3 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | 4 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | dop=8 | | 5 - output([ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]), filter(nil), rowset=256 | | access([ANONYMOUS_VIEW1.D_DEV], [ANONYMOUS_VIEW1.I_CODE], [ANONYMOUS_VIEW1.A_TYPE], [ANONYMOUS_VIEW1.M_TYPE], [ANONYMOUS_VIEW1.F_TPL]) | | 6 - output([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [COALESCE(cast(DT.F_TPL, NUMBER(-1, -85)), 0)]), filter(nil), rowset=256 | | 7 - output([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [COALESCE(cast(DT.F_TPL, NUMBER(-1, -85)), 0)]), filter(nil), rowset=256 | | access([DT.D_DEV], [DT.I_CODE], [DT.A_TYPE], [DT.M_TYPE], [DT.F_TPL]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([DT.D_BASE], [DT.D_DEV], [DT.__pk_increment]), range(2023-01-30,2022-01-13,MIN ; 2023-01-30,2023-01-30,MAX), | | range_cond([DT.D_DEV >= '2022-01-13'], [DT.D_DEV <= '2023-01-30'], [DT.D_BASE = '2023-01-30']) | | Used Hint: | | ------------------------------------- | | /*+ | | | | ENABLE_PARALLEL_DML | | GATHER_OPTIMIZER_STATISTICS | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, INS$1 | | stmt_id:1, SEL$1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | PARALLEL(@"SEL$1" "DT"@"SEL$1" 8) | | INDEX(@"SEL$1" "DT"@"SEL$1" "I1") | | PARALLEL(8) | | DML_PARALLEL(8) | | ENABLE_PARALLEL_DML | | OPTIMIZER_FEATURES_ENABLE('4.3.5.4') | | GATHER_OPTIMIZER_STATISTICS | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | DT: | | table_rows:14992798 | | physical_range_rows:1096289 | | logical_range_rows:1096289 | | output_rows:1096289 | | table_dop:8 | | dop_method:Global DOP | | avaiable_index_name:[IDX_MVR_1208_1, IDX_MVR_1208_2, I1, TSK_1108] | | pruned_index_name:[IDX_MVR_1208_1, IDX_MVR_1208_2, TSK_1108] | | stats info:[version=2025-11-06 18:44:58.733304, is_locked=0, is_expired=0] | | dynamic sampling level:0 | | estimation method:[OPTIMIZER STATISTICS, STORAGE] | | Plan Type: | | DISTRIBUTED | | Parameters: | | :0 => 0 | | :1 => '2022-01-13' | | :2 => '2023-01-30' | | :3 => '2023-01-30' | | Note: | | Degree of Parallelism is 8 because of session | | Direct-mode full is enabled in insert into select | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 83 rows in set (0.004 sec) ``` ## 适用版本 OceanBase 数据库 V4.3.4 及更高版本。 上一篇 [OceanBase Oracle 存储过程 DDL 执行编译结果落盘和缓存](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001999977) 下一篇 [并串行 DDL](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005476155) ![有帮助](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) 咨询热线