关联顺序调整成功把2个多小时的语句缩短至几分钟

今日遇到一条语句，运行超2个小时未完成。还收到了cache_gbase空间的告警。根据业务的特性，关联的条件，结果集会很小，理应很快能跑出来。语句主要部分如下：
from dwmid.tm_um_offer_cha_sum_day_20240930 a
join dwmid.tm_um_prod_cha_sum_day_20240930 b on a.value = b.value
join dwifc.p25027_um_offer_prod_rel_01_20240930 c on b.prod_ins_id = c.prod_ins_id
join dwifc.p25071_um_bundle_offer_rel_01_20240930 d on c.offer_ins_id = d.bundle_offer_ins_id
join dwd.dwd_cont_um_constr_sht_busi_cha_dm_20240930 e on d.offer_ins_id = e.value
join dwifc.p04015_um_constr_sht_busi_20240930 h on e.constr_sht_busi_ins_id = h.constr_sht_busi_ins_id
join dwifc.p04013_um_constr_sht_20240930 i on h.constr_sht_ins_id = i.constr_sht_ins_id
join public.tmp_03a_rpt_mkt_jt_5gjb_js_usr_2023_day_20240930 k on a.offer_ins_id = k.offer_ins_id
where a.cha_spec_id = '300286291'
and b.cha_spec_id = '200000105'
and e.cha_spec_id = '200002168'
and a.value not in (1, -1)
and b.value not like '%9999999999%'
and a.expire_date > '20240930000000'
and b.expire_date > '20240930000000'
and c.expire_date > '20240930000000'
and d.expire_date > '20240930000000'
and date_format(e.expire_date, '%Y%m%d%H%i%s')> '20240930000000'
and h.expire_date > '20240930000000'
and i.expire_date > '20240930000000'
and h.busi_type_code not like '%MOVE'
and h.busi_type_code not like '%REPLACE'
and substr(a.expire_date,1,8) >= k.end_date
and substr(c.expire_date,1,8) >= k.end_date

虽说a表45亿，23亿。但where条件已经添加了不少条件，且关联出来的结果集只有1962614行。再加入c表结果集也才1962605,加入d表1962608。全部结果集是：20901。再仔细看关联条件也没写错，应该不至于出现cache_gbase空间出现总共40多T。

看一下执行计划：
+----+----------------------------+---------------+-----------------+---------------------------------+-----------------------+
| ID | MOTION                     | OPERATION     | TABLE           | CONDITION                       | NO STAT Tab/Col       |
+----+----------------------------+---------------+-----------------+---------------------------------+-----------------------+
| 12 | [RESULT]                   |  Step         | <11>            |                                 |                       |
| 11 | [REDIST(offer_ins_id)]     |  INNER JOIN   |                 | (constr_sht_ins_id = constr_s.. |                       |
|    |                            |   Step        | <09>            |                                 |                       |
|    |                            |   Step        | <10>            |                                 |                       |
| 10 | [REDIST(constr_sht_ins..)] |  SCAN         | i[DIS]          | (expire_date{S} > '2024093000.. |                       |
| 09 | [REDIST(constr_sht_ins..)] |  INNER JOIN   |                 | (constr_sht_busi_ins_id = con.. |                       |
|    |                            |   Step        | <07>            |                                 |                       |
|    |                            |   Step        | <08>            |                                 |                       |
| 08 | [REDIST(constr_sht_bus..)] |  SCAN         | h[DIS]          | (expire_date{S} > '2024093000.. |                       |
|    |                            |               |                 | (busi_type_code NOT LIKE '%MO.. |                       |
|    |                            |               |                 | (busi_type_code NOT LIKE '%RE.. |                       |
| 07 | [REDIST(constr_sht_bus..)] |  INNER JOIN   |                 | (offer_ins_id = value)          |                       |
|    |                            |   Step        | <05>            |                                 |                       |
|    |                            |   Step        | <06>            |                                 |                       |
| 06 | [REDIST(value)]            |  SCAN         | e[DIS]          | (cha_spec_id{S} = '200002168')  |                       |
|    |                            |               |                 | (date_format(expire_date, '%Y.. |                       |
| 05 | [REDIST(offer_ins_id)]     |  INNER JOIN   |                 | (offer_ins_id = bundle_offer_.. |                       |
|    |                            |   Step        | <03>            |                                 |                       |
|    |                            |   Step        | <04>            |                                 |                       |
| 04 | [REDIST(bundle_offer_i..)] |  SCAN         | d[offer_ins_id] | (expire_date{S} > '2024093000.. |                       |
| 03 | [REDIST(offer_ins_id)]     |  INNER JOIN   |                 | (prod_ins_id = prod_ins_id) A.. |                       |
|    |                            |   INNER JOIN  |                 | (substr(expire_date, 1, 8) >=.. |                       |
|    |                            |    INNER JOIN |                 | (value = value)                 |                       |
|    |                            |     Step      | <00>            |                                 |                       |
|    |                            |     SCAN      | b[prod_ins_id]  | (cha_spec_id{S} = '200000105')  |                       |
|    |                            |               |                 | (value NOT LIKE '%9999999999%') |                       |
|    |                            |               |                 | (expire_date{S} > '2024093000.. |                       |
|    |                            |    Step       | <01>            |                                 |                       |
|    |                            |   Step        | <02>            |                                 |                       |
| 02 | [REDIST(prod_ins_id)]      |  SCAN         | c[offer_ins_id] | (expire_date{S} > '2024093000.. |                       |
| 01 | [BROADCAST]                |  Table        | k[DIS]          |                                 |                       |
| 00 | [BROADCAST]                |  SCAN         | a[offer_ins_id] | (cha_spec_id{S} = '300286291')  | tm_um_offer_cha_sum.. |
|    |                            |               |                 | (value NOT IN (1, -(1)))        |                       |
|    |                            |               |                 | (expire_date{S} > '2024093000.. |                       |
+----+----------------------------+---------------+-----------------+---------------------------------+-----------------------+
34 rows in set (Elapsed: 00:00:45.90)

发现关联顺序与预想中的不一样。非a与b表，与c表关联后，再与d关联。而是直接跳到了k表关联。简化SQL，a，b，c和k表关联，结果跑不出来，确定k表数据存在异常。发现有数据关联造成上1000亿的结果集。但业务和SQL逻辑上已经把a表的该数据过滤了呢？看来是语句关联顺序有问题了，只能通过调整语句，让a表数据先与其他表关联缩小结果集并去掉异常数据出现，避免与k表关联。语句调整后如下：
from (
select
a.expire_date expire_date_a,
c.expire_date expire_date_c,
a.offer_ins_id,
i.deal_tag,
i.done_date
from dwmid.tm_um_offer_cha_sum_day_20240930 a
join dwmid.tm_um_prod_cha_sum_day_20240930 b on a.value = b.value
join dwifc.p25027_um_offer_prod_rel_01_20240930 c on b.prod_ins_id = c.prod_ins_id
join dwifc.p25071_um_bundle_offer_rel_01_20240930 d on c.offer_ins_id = d.bundle_offer_ins_id
join dwd.dwd_cont_um_constr_sht_busi_cha_dm_20240930 e on d.offer_ins_id = e.value
join dwifc.p04015_um_constr_sht_busi_20240930 h on e.constr_sht_busi_ins_id = h.constr_sht_busi_ins_id
join dwifc.p04013_um_constr_sht_20240930 i on h.constr_sht_ins_id = i.constr_sht_ins_id
where a.cha_spec_id = '300286291'
and b.cha_spec_id = '200000105'
and e.cha_spec_id = '200002168'
and a.value not in (1, -1)
and b.value not like '%9999999999%'
and a.expire_date > '20240930000000'
and b.expire_date > '20240930000000'
and c.expire_date > '20240930000000'
and d.expire_date > '20240930000000'
and date_format(e.expire_date, '%Y%m%d%H%i%s')> '20240930000000'
and h.expire_date > '20240930000000'
and i.expire_date > '20240930000000'
and h.busi_type_code not like '%MOVE'
and h.busi_type_code not like '%REPLACE') i
join public.tmp_03a_rpt_mkt_jt_5gjb_js_usr_2023_day_20240930 k on i.offer_ins_id = k.offer_ins_id
where i.expire_date_a >= k.end_date
and i.expire_date_c >= k.end_date

最终运行时间缩短至3分钟不到。运行过程日志如下：
2024-10-24 14:43:00 [pool-1-thread-2] [INFO] Sql语句：create table public.tmp_03_rpt_mkt_jt_5gjb_js_usr_2023_day_20240930
……
and h.busi_type_code not like '%REPLACE') i
join public.tmp_03a_rpt_mkt_jt_5gjb_js_usr_2023_day_20240930 k on i.offer_ins_id = k.offer_ins_id
where i.expire_date_a >= k.end_date
and i.expire_date_c >= k.end_date
2024-10-24 14:45:42 [pool-1-thread-2] [INFO] Sql执行成功，影响行数：20901,数据源为:gbase_qjpublic