GBase南大通用8a执行 sql 时由于表连接顺序导致物化大量数据

描述:

GBase南大通用8aT03 在执行时，表连接顺序不是最优，导致物化大量数据。 select count(*)
from (
select cm.mp_no mp_no,
ecp.cat_prc_name cat_prc_name,
acmpq.prc_ts_code prc_ts_code,
aecp.t_amt t_amt,
aecp.calc_id calc_id,
aep.pf_std_code pf_std_code,
aep.actual_pf actual_pf,
aecp.level_num level_num,
acmpq.settle_apq,
ecpd.kwh_prc,
ecpd.cat_kwh_prc,
aecp.t_pl_amt
from c_mp cm,
arc_e_mp_para_snap am,
arc_e_consprc_snap aep,
arc_e_cons_prc_amt aecp,
arc_e_mp_pq acmpq,
e_cat_prc ecp,
e_cat_prc_det ecpd
where cm.mp_id = am.mp_id
and cm.org_no = am.org_no
and am.calc_id = aep.calc_id
and am.prc_id = aep.prc_id
and am.org_no = aep.org_no
and aecp.prc_snap_id = aep.prc_snap_id
and aecp.org_no = aep.org_no
and ecp.prc_code = aecp.prc_code
and ecp.para_vn = aecp.para_vn
and acmpq.mp_para_snap_id = am.mp_para_snap_id
and acmpq.org_no = am.org_no
and ecp.cat_prc_id = ecpd.cat_prc_id

and ecp.org_no = ecpd.org_no
and acmpq.prc_ts_code = ecpd.prc_ti_code and am.org_no = ‘2140400’
and am.ym = ‘201301’);

解决办法:

GBase南大通用8a通过改写，指定连接顺序，可大大减少物化成本。
select count(*)
from (select ecp.cat_prc_id,
ecpd.prc_ti_code,
t2.mp_para_snap_id,
t2.org_no
from (select aecp.prc_code,
aecp.para_vn,
t1.mp_para_snap_id,
t1.org_no
from (select aep.prc_snap_id,
am.mp_para_snap_id,
am.org_no
from arc_e_mp_para_snap am, – 5 亿的表，但可有效过滤 arc_e_consprc_snap aep, – 5 亿的表
c_mp cm – 200 万
where am.calc_id = aep.calc_id
and am.prc_id = aep.prc_id
and am.org_no = aep.org_no
and am.org_no = ‘2140400’
and am.ym = ‘201301’
and cm.mp_id = am.mp_id
and cm.org_no = am.org_no) t1, --首先三表关联生成 T1 arc_e_cons_prc_amt aecp
where aecp.prc_snap_id = t1.prc_snap_id
and aecp.org_no = t1.org_no) t2, – T1 是小表，和大表关联生成 T2 e_cat_prc ecp,
e_cat_prc_det ecpd
where ecp.prc_code = t2.prc_code
and ecp.para_vn = t2.para_vn
and ecp.cat_prc_id = ecpd.cat_prc_id) t3, – t2 和两个小表关联，生成 T3 arc_e_mp_pq acmpq – 使 T3 和 5 亿的表关联
where acmpq.prc_ts_code = t3.prc_ti_code
and acmpq.mp_para_snap_id = t3.mp_para_snap_id
and acmpq.org_no = t3.org_no。