GaussDB案例：增加JOIN列非空条件

现象描述

SELECT
 * 
FROM
( ( SELECT
  STARTTIME STTIME,
  SUM(NVL(PAGE_DELAY_MSEL,0)) PAGE_DELAY_MSEL,
  SUM(NVL(PAGE_SUCCEED_TIMES,0)) PAGE_SUCCEED_TIMES,
  SUM(NVL(FST_PAGE_REQ_NUM,0)) FST_PAGE_REQ_NUM,
  SUM(NVL(PAGE_AVG_SIZE,0)) PAGE_AVG_SIZE,
  SUM(NVL(FST_PAGE_ACK_NUM,0)) FST_PAGE_ACK_NUM,
  SUM(NVL(DATATRANS_DW_DURATION,0)) DATATRANS_DW_DURATION,
  SUM(NVL(PAGE_SR_DELAY_MSEL,0)) PAGE_SR_DELAY_MSEL 
 FROM
  PS.SDR_WEB_BSCRNC_1DAY SDR
  INNER JOIN (SELECT
      BSCRNC_ID,
      BSCRNC_NAME,
      ACCESS_TYPE,
      ACCESS_TYPE_ID 
     FROM
      nethouse.DIM_LOC_BSCRNC 
     GROUP BY
      BSCRNC_ID,
      BSCRNC_NAME,
      ACCESS_TYPE,
      ACCESS_TYPE_ID) DIM 
  ON SDR.BSCRNC_ID = DIM.BSCRNC_ID 
  AND DIM.ACCESS_TYPE_ID IN (0,1,2) 
  INNER JOIN nethouse.DIM_RAT_MAPPING RAT 
  ON (RAT.RAT = SDR.RAT)
 WHERE
  ( (STARTTIME >= 1461340800 
  AND STARTTIME < 1461427200) ) 
  AND RAT.ACCESS_TYPE_ID IN (0,1,2) 
  --and SDR.BSCRNC_ID is not null
 GROUP BY
  STTIME ) ) ;

执行计划如图1所示。

图1 增加JOIN列非空条件（一）

优化分析

1. 现象描述

   SELECT * FROM ( ( SELECT STARTTIME STTIME, SUM(NVL(PAGE_DELAY_MSEL,0)) PAGE_DELAY_MSEL, SUM(NVL(PAGE_SUCCEED_TIMES,0)) PAGE_SUCCEED_TIMES, SUM(NVL(FST_PAGE_REQ_NUM,0)) FST_PAGE_REQ_NUM, SUM(NVL(PAGE_AVG_SIZE,0)) PAGE_AVG_SIZE, SUM(NVL(FST_PAGE_ACK_NUM,0)) FST_PAGE_ACK_NUM, SUM(NVL(DATATRANS_DW_DURATION,0)) DATATRANS_DW_DURATION, SUM(NVL(PAGE_SR_DELAY_MSEL,0)) PAGE_SR_DELAY_MSEL FROM PS.SDR_WEB_BSCRNC_1DAY SDR INNER JOIN (SELECT BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID FROM nethouse.DIM_LOC_BSCRNC GROUP BY BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID) DIM ON SDR.BSCRNC_ID = DIM.BSCRNC_ID AND DIM.ACCESS_TYPE_ID IN (0,1,2) INNER JOIN nethouse.DIM_RAT_MAPPING RAT ON (RAT.RAT = SDR.RAT) WHERE ( (STARTTIME >= 1461340800 AND STARTTIME < 1461427200) ) AND RAT.ACCESS_TYPE_ID IN (0,1,2) --and SDR.BSCRNC_ID is not null GROUP BY STTIME ) ) ;

   执行计划如图1所示。

   图1 增加JOIN列非空条件（一）
   

   优化分析

   1. 分析执行计划图1可知，在顺序扫描阶段耗时较多。
   2. 多表JOIN中，由于表PS.SDR_WEB_BSCRNC_1DAY的JOIN列“BSCRNC_ID”存在大量空值，JOIN性能差。

      建议在语句中手动添加JOIN列的非空判断，修改后的语句如下所示。

      SELECT * FROM ( ( SELECT STARTTIME STTIME, SUM(NVL(PAGE_DELAY_MSEL,0)) PAGE_DELAY_MSEL, SUM(NVL(PAGE_SUCCEED_TIMES,0)) PAGE_SUCCEED_TIMES, SUM(NVL(FST_PAGE_REQ_NUM,0)) FST_PAGE_REQ_NUM, SUM(NVL(PAGE_AVG_SIZE,0)) PAGE_AVG_SIZE, SUM(NVL(FST_PAGE_ACK_NUM,0)) FST_PAGE_ACK_NUM, SUM(NVL(DATATRANS_DW_DURATION,0)) DATATRANS_DW_DURATION, SUM(NVL(PAGE_SR_DELAY_MSEL,0)) PAGE_SR_DELAY_MSEL FROM PS.SDR_WEB_BSCRNC_1DAY SDR INNER JOIN (SELECT BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID FROM nethouse.DIM_LOC_BSCRNC GROUP BY BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID) DIM ON SDR.BSCRNC_ID = DIM.BSCRNC_ID AND DIM.ACCESS_TYPE_ID IN (0,1,2) INNER JOIN nethouse.DIM_RAT_MAPPING RAT ON (RAT.RAT = SDR.RAT) WHERE ( (STARTTIME >= 1461340800 AND STARTTIME < 1461427200) ) AND RAT.ACCESS_TYPE_ID IN (0,1,2) and SDR.BSCRNC_ID is not null GROUP BY STTIME ) ) A;

      执行计划如图2所示。

      图2 增加JOIN列非空条件（二）
      
   分析执行计划图1可知，在顺序扫描阶段耗时较多。
2. 多表JOIN中，由于表PS.SDR_WEB_BSCRNC_1DAY的JOIN列“BSCRNC_ID”存在大量空值，JOIN性能差。

   建议在语句中手动添加JOIN列的非空判断，修改后的语句如下所示。

   SELECT * FROM ( ( SELECT STARTTIME STTIME, SUM(NVL(PAGE_DELAY_MSEL,0)) PAGE_DELAY_MSEL, SUM(NVL(PAGE_SUCCEED_TIMES,0)) PAGE_SUCCEED_TIMES, SUM(NVL(FST_PAGE_REQ_NUM,0)) FST_PAGE_REQ_NUM, SUM(NVL(PAGE_AVG_SIZE,0)) PAGE_AVG_SIZE, SUM(NVL(FST_PAGE_ACK_NUM,0)) FST_PAGE_ACK_NUM, SUM(NVL(DATATRANS_DW_DURATION,0)) DATATRANS_DW_DURATION, SUM(NVL(PAGE_SR_DELAY_MSEL,0)) PAGE_SR_DELAY_MSEL FROM PS.SDR_WEB_BSCRNC_1DAY SDR INNER JOIN (SELECT BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID FROM nethouse.DIM_LOC_BSCRNC GROUP BY BSCRNC_ID, BSCRNC_NAME, ACCESS_TYPE, ACCESS_TYPE_ID) DIM ON SDR.BSCRNC_ID = DIM.BSCRNC_ID AND DIM.ACCESS_TYPE_ID IN (0,1,2) INNER JOIN nethouse.DIM_RAT_MAPPING RAT ON (RAT.RAT = SDR.RAT) WHERE ( (STARTTIME >= 1461340800 AND STARTTIME < 1461427200) ) AND RAT.ACCESS_TYPE_ID IN (0,1,2) and SDR.BSCRNC_ID is not null GROUP BY STTIME ) ) A;

   执行计划如图2所示。

   图2 增加JOIN列非空条件（二）