当通过 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 走旁路导入
## 准备测试数据
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 也走了旁路导入
## 这边看到 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 及更高版本。
