“小表”被拉复制表后再笛卡尔关联，造成效率很差

某项目巡检发现，有一条语句运行超过1小时，其中出现单个节点计算的倾斜现象，运算的节点临时空间已超400G。
--------- 10.xxx.xxx.204---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.205---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.206---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.207---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.208---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.209---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.210---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.211---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.212---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.213---------
Authorized users only. All activity may be monitored and reported. 
139445338       xxx  10.xxx.xxx.191:51860     gctmpdb Query   4455    init    SELECT /*::ffff:10.xxx.xxx.191_8628511_69_2024-12-27_14:55:18*/ /*+ TID('10368464000') */ `_tmp_32065
--------- 10.xxx.xxx.214---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.215---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.216---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.217---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.218---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.219---------
Authorized users only. All activity may be monitored and reported. 
--------- 10.xxx.xxx.220---------
语句主要部分为：
create table xxx  as
select ****
from (select ***
    from dwtmp.tmp_****_20241226 a
         cross join dwtmp.tmp_cycle_***_day_20241226 b
         inner join dwctr.tc_usr_2025_***_20241226 c
           on a.user_id = c.user_id
         left join (select cycle_id
                          ,chnl_id
                          ,user_id
                          ,sum(nvl(num_xkh,0) + nvl(num_kd,0) + nvl(num_tcsd,0) + nvl(num_jx_fttr_kd,0) + nvl(num_jx_wifi_kd,0) + nvl(num_wifibn,0) + nvl(num_jx_af,0) + nvl(num_afbn,0) + nvl(num_jkny,0) + nvl(num_jknn,0) + nvl(num_yx,0) + nvl(num_ds,0) + nvl(num_qy,0) + nvl(num_scy,0) + nvl(num_zq,0) + nvl(num_sp_wp,0) + nvl(num_sp_qly,0)) as chnl_par_09
                   from dwtmp.tmp_offer2_***_20241226
                   group by cycle_id
                           ,chnl_id
                           ,user_id) d
           on b.cycle_id = d.cycle_id and a.chnl_id = d.chnl_id and c.user_id_sj = d.user_id and d.chnl_par_09 > 0
    where a.create_date between b.min_date and b.max_date
    group by b.cycle_id
            ,a.chnl_id
            ,c.user_id_sj) t1
group by t1.cycle_id
       ,t1.chnl_id 
a  -- 2579万
b -- 5行
c -- 2亿
d -- 352万
执行计划

+----+--------------------+---------------+-------------------------------+--------------------------------+----------------------+
| ID | MOTION             | OPERATION     | TABLE                         | CONDITION                      | NO STAT Tab/Col      |
+----+--------------------+---------------+-------------------------------+--------------------------------+----------------------+
| 03 | [RESULT]           |  SubQuery1    | t1                            |                                |                      |
|    |                    |   Step        | <02>                          |                                |                      |
|    |                    |   GROUP       |                               | GROUP BY cycle_id, chnl_id, .. |                      |
|    |                    |  GROUP        |                               | GROUP BY cycle_id, chnl_id     |                      |
| 02 | [REDIST(cycle_id)] |  LEFT JOIN    |                               | (cycle_id = cycle_id) AND (c.. |                      |
|    |                    |   INNER JOIN  |                               | (create_date BETWEEN min_dat.. |                      |
|    |                    |    Step       | <00>                          |                                |                      |
|    |                    |    INNER JOIN |                               | (user_id = user_id)            |                      |
|    |                    |     Table     | c[user_id]                    |                                |                      |
|    |                    |     Table     | a[user_id]                    |                                |                      |
|    |                    |   Step        | <01>                          |                                |                      |
|    |                    |  GROUP        |                               | GROUP BY cycle_id, chnl_id, .. |                      |
| 01 | [BROADCAST]        |  SubQuery2    | d                             |                                | tmp_offer2_rpt_chl.. |
|    |                    |   Table       | tmp_offer2_rpt_chl..[user_id] |                                |                      |
|    |                    |   GROUP       |                               | GROUP BY cycle_id, chnl_id, .. |                      |
|    |                    |  WHERE        |                               | (chnl_par_09 > 0)              |                      |
| 00 | [BROADCAST]        |  Table        | b[DIS]                        |                                | tmp_cycle_rpt_chl_.. |
+----+--------------------+---------------+-------------------------------+--------------------------------+----------------------+
17 rows in set (Elapsed: 00:00:02.39)

通过执行计划发现，d和a先关联了，d被拉复制表。a和b、c关联的结果表运算，未能发挥所有节点并发运算。所有结果集积压在一个节点，在cross join关联，出现了笛卡尔成绩现象。
解决思路有2种：
1，拆两段，a，b，c先关联，再与d关联
2，避免d表被拉复制表，调整拉复制表阈值小于d表的值（352万），如：set gcluster_hash_redist_threshold_row=3000000;

最终方法一耗时不到2分钟。
2024-12-27 15:59:53 [pool-1-thread-2] [INFO] Sql语句：create table dwtmp.tmp_02_1_rpt_chl_2025_sjyx_campain_day_20241226 distributed by ('chnl_id') as
select b.cycle_id
     ,a.chnl_id
     ,c.user_id_sj as user_id
     ,max(case when a.busi_type = 1 then 1
               else 0 end) as flag_busi
     ,max(case when a.busi_type = 2 then 1
               else 0 end) as flag_card
from dwtmp.tmp_busi_rpt_chl_2025_sjyx_campain_day_20241226 a
    cross join dwtmp.tmp_cycle_***_day_20241226 b
    inner join dwctr.tc_usr_2025_***_20241226 c
      on a.user_id = c.user_id
where a.create_date between b.min_date and b.max_date
group by b.cycle_id
       ,a.chnl_id
       ,c.user_id_sj
2024-12-27 16:00:47 [pool-1-thread-2] [INFO] Sql执行成功，影响行数：62872434,数据源为:gbase_qj2ods
2024-12-27 16:00:48 [pool-1-thread-2] [INFO] Sql语句：create table dwtmp.tmp_02_rpt_chl_2025_sjyx_campain_day_20241226 distributed by ('chnl_id') as
select t1.cycle_id
     ,t1.chnl_id
……

    from dwtmp.tmp_****_20241226 a
         left join (select cycle_id
                          ,chnl_id
                          ,user_id
                          ,nvl(num_xkh,0) + nvl(num_kd,0) + nvl(num_tcsd,0) + nvl(num_jx_fttr_kd,0) + nvl(num_jx_wifi_kd,0) + nvl(num_wifibn,0) + nvl(num_jx_af,0) + nvl(num_afbn,0) + nvl(num_jkny,0) + nvl(num_jknn,0) +
                           nvl(num_yx,0) + nvl(num_ds,0) + nvl(num_qy,0) + nvl(num_scy,0) + nvl(num_zq,0) + nvl(num_sp_wp,0) + nvl(num_sp_qly,0) as chnl_par_09
                   from dwtmp.tmp_offer2_***_20241226) b
           on a.cycle_id = b.cycle_id and a.chnl_id = b.chnl_id and a.user_id = b.user_id and b.chnl_par_09 > 0) t1
group by t1.cycle_id
       ,t1.chnl_id
2024-12-27 16:01:18 [pool-1-thread-2] [INFO] Sql执行成功，影响行数：95018,数据源为:gbase_qj2ods

方法二：set gcluster_hash_redist_threshold_row=3000000;最终也是耗时1分13秒