更新时间: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中,由于表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所示。
父主题: SQL调优案例