更新时间:2024-11-05 GMT+08:00

案例:增加JOIN列非空条件

若Join列上的NULL值较多,可以加上is not null过滤条件,以实现数据的提前过滤,提高Join效率。

优化前

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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) 
 GROUP BY STTIME ) ) ;

执行计划如图1所示。

图1 增加JOIN列非空条件(一)

优化后

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

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

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    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列非空条件(二)