跨层子查询大表拉复制表执行计划分析

CREATE TABLE test.tb1(id int, name varchar(10)) DISTRIBUTED BY('id');
CREATE TABLE test.tb2(a int, b varchar(10)) DISTRIBUTED BY('a');
CREATE TABLE test.tb3(id int, info varchar(10)) DISTRIBUTED BY('id');

相邻子查询：
explain partitions select * from tb1 where exists (select 1 from tb2 where tb2.a=tb1.id);

跨层子查询：
explain partitions select * from tb1 where exists (select 1 from tb2 where tb2.a=tb1.id and tb2.a in (select tb3.id from tb3));


----------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS sys_area;
CREATE TABLE IF NOT EXISTS sys_area (areano varchar(10), region varchar(20));


一、 ODS表是随机分布表、DM表是hash分布表
表结构如下：
DROP TABLE IF EXISTS ods_td_contracts_pr_edp_tmp_day;
CREATE TABLE IF NOT EXISTS ods_td_contracts_pr_edp_tmp_day (
  fic_mis_date varchar(8) DEFAULT NULL,
  v_ibk_no varchar(5) DEFAULT NULL,
  v_account_number varchar(50) NOT NULL,
  n_cur_book_bal decimal(22,3) DEFAULT NULL
);

DROP TABLE IF EXISTS tmp_dm_invm_hisbalance_p;
CREATE TABLE IF NOT EXISTS tmp_dm_invm_hisbalance_p (
  v_account_number varchar(50) NOT NULL,
  v_ibk_no varchar(5) DEFAULT NULL,
  v_tran_date varchar(8) DEFAULT NULL
)distributed by ('v_account_number');

执行计划：
SQL中存在跨层子查询，执行计划是对关联DM表进行拉复制表操作，如果DM表数据量大，影响SQL处理效率
explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm where dm.v_ibk_no in (select areano from sys_area where region='40000')
and dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+-----------------------------------------------------------------------+
| PlanTree                                                              |
+-----------------------------------------------------------------------+
| 02: [RESULT]                                                          |
|         Table : vc_hw_dcmp.tdb.ods_td_contracts_pr_edp_tmp_day as ods |
|             Distributed                                               |
|         WHERE  NOT EXISTS ([SubQuery1])                               |
| --->01: [BROADCAST]                                                   |
|             Table : vc_hw_dcmp.tdb.tmp_dm_invm_hisbalance_p as dm     |
|                 Hash Key : v_account_number                           |
|             WHERE v_ibk_no IN ([SubQuery2])                           |
|     --->00: [BROADCAST]                                               |
|                 Table : vc_hw_dcmp.tdb.sys_area                       |
|                     Distributed                                       |
|                 WHERE (region{S} = '40000')                           |
+-----------------------------------------------------------------------+
12 rows in set (Elapsed: 00:00:00.03)

优化方案：
取消跨层子查询改为JOIN关联查询
explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm JOIN sys_area area ON dm.v_ibk_no = area.areano and area.region='40000'
where dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+---------------------------------------------------------------------------+
| PlanTree                                                                  |
+---------------------------------------------------------------------------+
| 03: [RESULT]                                                              |
|         Step : <00>                                                       |
|         WHERE  NOT EXISTS ([SubQuery1])                                   |
| --->02: [REDIST]                                                          |
|         Redist Key : (areano)                                             |
|             Table : vc_hw_dcmp.tdb.sys_area as area                       |
|                 Distributed                                               |
|             WHERE (region{S} = '40000')                                   |
| --->01: [REDIST]                                                          |
|         Redist Key : (v_ibk_no)                                           |
|             Table : vc_hw_dcmp.tdb.tmp_dm_invm_hisbalance_p as dm         |
|                 Hash Key : v_account_number                               |
| --->00: [REDIST]                                                          |
|         Redist Key : (v_ibk_no)                                           |
|             Table : vc_hw_dcmp.tdb.ods_td_contracts_pr_edp_tmp_day as ods |
|                 Distributed                                               |
+---------------------------------------------------------------------------+
16 rows in set (Elapsed: 00:00:00.16)



二、 ODS表是hash分布表、DM表是hash分布表
表结构如下：
DROP TABLE IF EXISTS ods_td_contracts_pr_edp_tmp_day;
CREATE TABLE IF NOT EXISTS ods_td_contracts_pr_edp_tmp_day (
  fic_mis_date varchar(8) DEFAULT NULL,
  v_ibk_no varchar(5) DEFAULT NULL,
  v_account_number varchar(50) NOT NULL,
  n_cur_book_bal decimal(22,3) DEFAULT NULL
) distributed by ('v_account_number');

DROP TABLE IF EXISTS tmp_dm_invm_hisbalance_p;
CREATE TABLE IF NOT EXISTS tmp_dm_invm_hisbalance_p (
  v_account_number varchar(50) NOT NULL,
  v_ibk_no varchar(5) DEFAULT NULL,
  v_tran_date varchar(8) DEFAULT NULL
)distributed by ('v_account_number');

执行计划：
SQL中存在跨层子查询，执行计划是对关联DM表进行拉复制表操作，如果DM表数据量大，影响SQL处理效率
explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm where dm.v_ibk_no in (select areano from sys_area where region='40000')
and dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+-----------------------------------------------------------------+
| 02: [RESULT]                                                    |
|         Table : vc1.test.ods_td_contracts_pr_edp_tmp_day as ods |
|             Hash Key : v_account_number                         |
|         WHERE  NOT EXISTS ([SubQuery1])                         |
| --->01: [BROADCAST]                                             |
|             Table : vc1.test.tmp_dm_invm_hisbalance_p as dm     |
|                 Hash Key : v_account_number                     |
|             WHERE v_ibk_no IN ([SubQuery2])                     |
|     --->00: [BROADCAST]                                         |
|                 Table : vc1.test.sys_area                       |
|                     Distributed                                 |
|                 WHERE (region{S} = '40000')                     |
+-----------------------------------------------------------------+

优化方案：
取消跨层子查询改为JOIN关联查询
explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm JOIN sys_area area ON dm.v_ibk_no = area.areano and area.region='40000'
where dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+-------------------------------------------------------------------------+
| 03: [RESULT]                                                            |
|         Table : vcname000001.tdb.ods_td_contracts_pr_edp_tmp_day as ods |
|             Hash Key : v_account_number                                 |
|         WHERE  NOT EXISTS ([SubQuery1])                                 |
| --->02: [REDIST]                                                        |
|         Redist Key : (v_account_number)                                 |
|             Subquery 1 : placeholder                                    |
|                 INNER JOIN                                              |
|                 ON (v_ibk_no = areano)                                  |
|                     Step : <00>                                         |
|                     Step : <01>                                         |
|     --->01: [REDIST]                                                    |
|             Redist Key : (areano)                                       |
|                 Table : vcname000001.tdb.sys_area as area               |
|                     Distributed                                         |
|                 WHERE (region{S} = '40000')                             |
|     --->00: [REDIST]                                                    |
|             Redist Key : (v_ibk_no)                                     |
|                 Table : vcname000001.tdb.tmp_dm_invm_hisbalance_p as dm |
|                     Hash Key : v_account_number                         |
+-------------------------------------------------------------------------+


----------------------------------------------------------------------------------------------------------------------------



9.5.3.27.21_patch.24.3/9.5.3.27.21_patch.32版本新增参数参数_t_gcluster_corr_subquery_optimize控制，默认1表示开启优化，0同原先表现一致。

GBase client 9.5.3.27.21_patch.24.35c1a144. Copyright (c) 2004-2025, GBase.  All Rights Reserved.

gbase> use tdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> show variables like '%corr_subquery%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| _t_gcluster_corr_subquery_optimize | 1     |
+------------------------------------+-------+
1 row in set (Elapsed: 00:00:00.00)

1）ODS表是随机分布表、DM表是hash分布表

explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm where dm.v_ibk_no in (select areano from sys_area where region='40000')
and dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+---------------------------------------------------------------------------+
| PlanTree                                                                  |
+---------------------------------------------------------------------------+
| 02: [RESULT]                                                              |
|         Step : <01>                                                       |
|         WHERE  NOT EXISTS ([SubQuery1])                                   |
| --->01: [REDIST]                                                          |
|         Redist Key : (v_account_number)                                   |
|             Table : vc_hw_dcmp.tdb.ods_td_contracts_pr_edp_tmp_day as ods |
|                 Distributed                                               |
| --->00: [BROADCAST]                                                       |
|             Table : vc_hw_dcmp.tdb.sys_area                               |
|                 Distributed                                               |
|             WHERE (region{S} = '40000')                                   |
|             AGG                                                           |
+---------------------------------------------------------------------------+
12 rows in set (Elapsed: 00:00:00.03)

2）ODS表是hash分布表、DM表是hash分布表
explain partitions
select ods.v_ibk_no,ods.v_account_number,ods.n_cur_book_bal
from ods_td_contracts_pr_edp_tmp_day ods
where not exists
(select 1 from tmp_dm_invm_hisbalance_p dm JOIN sys_area area ON dm.v_ibk_no = area.areano and area.region='40000'
where dm.v_ibk_no = ods.v_ibk_no
and dm.v_account_number = ods.v_account_number
and dm.v_tran_date = ods.fic_mis_date);
+-----------------------------------------------------------------------+
| PlanTree                                                              |
+-----------------------------------------------------------------------+
| 03: [RESULT]                                                          |
|         Table : vc_hw_dcmp.tdb.ods_td_contracts_pr_edp_tmp_day as ods |
|             Hash Key : v_account_number                               |
|         WHERE  NOT EXISTS ([SubQuery1])                               |
| --->02: [REDIST]                                                      |
|         Redist Key : (v_account_number)                               |
|             Subquery 1 : placeholder                                  |
|                 INNER JOIN                                            |
|                 ON (v_ibk_no = areano)                                |
|                     Step : <00>                                       |
|                     Step : <01>                                       |
|     --->01: [REDIST]                                                  |
|             Redist Key : (areano)                                     |
|                 Table : vc_hw_dcmp.tdb.sys_area as area               |
|                     Distributed                                       |
|                 WHERE (region{S} = '40000')                           |
|     --->00: [REDIST]                                                  |
|             Redist Key : (v_ibk_no)                                   |
|                 Table : vc_hw_dcmp.tdb.tmp_dm_invm_hisbalance_p as dm |
|                     Hash Key : v_account_number                       |
+-----------------------------------------------------------------------+
20 rows in set (Elapsed: 00:00:00.16)