SQL优化案例-避免笛卡尔积

多表join加入distinct避免重复值产生笛卡尔积优化大表关联

原始SQL：

SELECT distinct n.HPHM
 from (SELECT PLATE_NUMBER as HPHM
         from through_vehicle_info_view
        where 1 = 1
          and point_id = '450124000000501636'
          and THROUGH_TIME BETWEEN '2014-05-02 09:00:00' AND
              '2014-05-20 10:00:00') n,
      (SELECT a.HPHM
         from (select HPHM as HPHM
                 from t_wbzy_bz_clwzryxx
                where 1 = 1
                  and wfsj >
                      to_date('2013-04-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
                  and wfsj <
                      to_date('2014-05-04 10:00:00', 'yyyy-mm-dd hh24:mi:ss')) a,
              (select HPHM as HPHM
                 from t_wbzy_bz_clwzryxx
                where 1 = 1
                  and wfsj >
                      to_date('2014-06-05 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
                  and wfsj <
                      to_date('2014-11-06 10:00:00', 'yyyy-mm-dd hh24:mi:ss')) b
        WHERE a.HPHM = b.HPHM) m
WHERE m.HPHM = n.HPHM;

其中的子查询：
select PLATE_NUMBER as HPHM
from through_vehicle_info_view
where 1 = 1
       and point_id = '441800600000214000'
       and THROUGH_TIME >to_date('2014-05-01 09:00:00','yyyy-mm-dd hh24:mi:ss')
       and THROUGH_TIME <to_date('2014-05-02 10:00:00','yyyy-mm-dd hh24:mi:ss')
原SQL中没有对结果集进行去重，因此进行子查询结果集join时因中间结果集内重复数据会形成笛卡尔积现象，从而形成较大join结果集，导致查询时间较长。

优化方式：
对子查询添加distinct去重函数：
set gcluster_hash_redistribute_join_optimize=0;
SELECT distinct n.HPHM
 from (SELECT distinct PLATE_NUMBER as HPHM
         from through_vehicle_info_view
        where 1 = 1
          and point_id = '450124000000501636'
          and THROUGH_TIME BETWEEN '2014-05-02 09:00:00' AND
              '2014-05-20 10:00:00') n,
      (SELECT a.HPHM
         from (select distinct HPHM as HPHM
                 from t_wbzy_bz_clwzryxx
                where 1 = 1
                  and wfsj >
                      to_date('2013-04-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
                  and wfsj <
                      to_date('2014-05-04 10:00:00', 'yyyy-mm-dd hh24:mi:ss')) a,
              (select distinct HPHM as HPHM
                 from t_wbzy_bz_clwzryxx
                where 1 = 1
                  and wfsj >
                      to_date('2014-06-05 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
                  and wfsj <
                      to_date('2014-11-06 10:00:00', 'yyyy-mm-dd hh24:mi:ss')) b
        WHERE a.HPHM = b.HPHM) m
WHERE m.HPHM = n.HPHM;
set gcluster_hash_redistribute_join_optimize=1;
通过去重后执行时间从原SQL的18分钟降至1.4s。