sql中用EXISTS替代IN、用NOT EXISTS替代NOT IN显著变化

最新推荐文章于 2024-05-20 22:45:39 发布

haveysun

最新推荐文章于 2024-05-20 22:45:39 发布

阅读量1.4k

点赞数

分类专栏：数据库文章标签： oracle sql优化

本文链接：https://blog.csdn.net/haveysun/article/details/9350937

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

sql如下所示：
SELECT SUM(T.F1), SUM(T.F2), SUM(T.F3), SUM(T.F4), SUM(T.F5), SUM(T.F6), SUM(T.F7), SUM(T.F8), SUM(T.F9), SUM(T.F10), SUM(T.F11), SUM(T.F12)
      INTO V_CONT_RECORD
      FROM (SELECT SUM(DECODE(BF.FLOOR_NUM, '-1', COUNT(*), 0)) F1, SUM(DECODE(BF.FLOOR_NUM, '1', COUNT(*), 0)) F2,
                     SUM(DECODE(BF.FLOOR_NUM, '2', COUNT(*), 0)) F3, SUM(DECODE(BF.FLOOR_NUM, '3', COUNT(*), 0)) F4,
                     SUM(DECODE(BF.FLOOR_NUM, '4', COUNT(*), 0)) F5, SUM(DECODE(BF.FLOOR_NUM, '5', COUNT(*), 0)) F6,
                     SUM(DECODE(BF.FLOOR_NUM, '-1', BS.SQUARE_REAL, 0)) F7, SUM(DECODE(BF.FLOOR_NUM, '1', BS.SQUARE_REAL, 0)) F8,
                     SUM(DECODE(BF.FLOOR_NUM, '2', BS.SQUARE_REAL, 0)) F9, SUM(DECODE(BF.FLOOR_NUM, '3', BS.SQUARE_REAL, 0)) F10,
                     SUM(DECODE(BF.FLOOR_NUM, '4', BS.SQUARE_REAL, 0)) F11, SUM(DECODE(BF.FLOOR_NUM, '5', BS.SQUARE_REAL, 0)) F12
               FROM BIS_CONT BC
               LEFT JOIN BIS_STORE_CONT_REL BSCR ON BC.BIS_CONT_ID = BSCR.BIS_CONT_ID
               LEFT JOIN BIS_STORE BS ON BSCR.BIS_STORE_ID = BS.BIS_STORE_ID
               LEFT JOIN BIS_FLOOR BF ON BS.BIS_FLOOR_ID = BF.BIS_FLOOR_ID
               WHERE BC.CONT_TYPE_CD = '1' --合同类型为租赁
               AND BC.STORE_TYPE = '1' --物业类型为购物中心
               AND BC.MANAGE_CD='2'    --商铺类型为小商铺
               AND BC.BIS_PROJECT_ID = P_BIS_PROJECT_ID
               AND BC.STATUS_CD ='3'
               AND (BF.FLOOR_NUM='-1' OR BF.FLOOR_NUM='1' OR BF.FLOOR_NUM='2' OR
               BF.FLOOR_NUM='3' OR BF.FLOOR_NUM='4' OR BF.FLOOR_NUM='5')
               AND SUBSTR(BC.PROPORTION_NAMES,0,
               DECODE(INSTR(BC.PROPORTION_NAMES, '-', 1, 1),0,LENGTH(BC.PROPORTION_NAMES),
               INSTR(BC.PROPORTION_NAMES, '-', 1, 1) - 1)) =V_OPREATE_TYPE
               AND P_YMD >= TO_CHAR(BC.RENT_DATE, 'YYYY-MM-DD')
               AND ((BC.CONT_TO_FAIL_DATE IS NOT NULL AND
               P_YMD <= TO_CHAR(BC.CONT_TO_FAIL_DATE, 'YYYY-MM-DD')) OR
               (BC.CONT_TO_FAIL_DATE IS NULL AND
               P_YMD <= TO_CHAR(BC.CONT_END_DATE, 'YYYY-MM-DD')))
               GROUP BY BF.FLOOR_NUM, BS.SQUARE_REAL
            UNION ALL
            SELECT SUM(DECODE(BFR.FLOOR_NUM, '-1', COUNT(*), 0)) F1, SUM(DECODE(BFR.FLOOR_NUM, '1', COUNT(*), 0)) F2,
                     SUM(DECODE(BFR.FLOOR_NUM, '2', COUNT(*), 0)) F3, SUM(DECODE(BFR.FLOOR_NUM, '3', COUNT(*), 0)) F4,
                     SUM(DECODE(BFR.FLOOR_NUM, '4', COUNT(*), 0)) F5, SUM(DECODE(BFR.FLOOR_NUM, '5', COUNT(*), 0)) F6,
                     SUM(DECODE(BFR.FLOOR_NUM, '-1', BST.SQUARE_REAL, 0)) F7, SUM(DECODE(BFR.FLOOR_NUM, '1', BST.SQUARE_REAL, 0)) F8,
                     SUM(DECODE(BFR.FLOOR_NUM, '2', BST.SQUARE_REAL, 0)) F9, SUM(DECODE(BFR.FLOOR_NUM, '3', BST.SQUARE_REAL, 0)) F10,
                     SUM(DECODE(BFR.FLOOR_NUM, '4', BST.SQUARE_REAL, 0)) F11, SUM(DECODE(BFR.FLOOR_NUM, '5', BST.SQUARE_REAL, 0)) F12
               FROM BIS_STORE BST
               LEFT JOIN BIS_FLOOR BFR  ON BST.BIS_FLOOR_ID = BFR.BIS_FLOOR_ID
               WHERE  BST.LAYOUT_CD = ITEM
               AND (BFR.FLOOR_NUM='-1' OR BFR.FLOOR_NUM='1' OR BFR.FLOOR_NUM='2' OR
               BFR.FLOOR_NUM='3' OR BFR.FLOOR_NUM='4' OR BFR.FLOOR_NUM='5')
               AND BFR.CHARGE_TYPE='1' --物业类型为购物中心
               AND BST.STORE_POSITION='2'--商铺类型为小商铺
               AND BST.BIS_PROJECT_ID = P_BIS_PROJECT_ID
               AND (BST.SPLIT_STATUS IS NULL OR BST.SPLIT_STATUS = '0')
               AND (BST.IF_SUBMIT = '1' OR BST.IF_SUBMIT = '2' OR
                     BST.IF_SUBMIT IS NULL)
                  --AND BST.STORE_NO NOT IN
               AND NOT EXISTS
                     (SELECT  1 FROM
                        BIS_CONT BT LEFT JOIN BIS_STORE_CONT_REL BSCR
                        ON BT.BIS_CONT_ID = BSCR.BIS_CONT_ID
                        LEFT JOIN  BIS_STORE BSA ON
                        BSCR.BIS_STORE_ID = BSA.BIS_STORE_ID
                        WHERE BT.BIS_PROJECT_ID = P_BIS_PROJECT_ID
                        AND BT.CONT_TYPE_CD = '1' --合同类型为租赁
                        AND BT.STORE_TYPE = '1' --物业类型为购物中心
                        AND BT.MANAGE_CD='2'    --商铺类型为小商铺
                        AND BT.STATUS_CD ='3'
                        AND P_YMD >= TO_CHAR(BT.RENT_DATE, 'YYYY-MM-DD')
                        AND ((BT.CONT_TO_FAIL_DATE IS NOT NULL AND
                              P_YMD <= TO_CHAR(BT.CONT_TO_FAIL_DATE, 'YYYY-MM-DD')) OR
                              (BT.CONT_TO_FAIL_DATE IS NULL AND
                              P_YMD <= TO_CHAR(BT.CONT_END_DATE, 'YYYY-MM-DD')))
                        AND BSA.STORE_NO = BST.STORE_NO
                              )
               GROUP BY BFR.FLOOR_NUM, BST.SQUARE_REAL) T;
红色部分是真正的瓶颈所在，这个sql是查询一下项目公司下面的，-1到5楼的商铺的总数，面积等等之类的业务，数据量应该在1000条之内的，这个是运行在procedure里面的，没修改红色之前，需要132s,修改之后在3s内就出来了，原理如下：用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历) . 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.