Oracle子查询优化

前言

项目经理扔给我一个需求，说用户反馈用户查询页面转圈圈很久很久，于是我就通过调试去找到请求的路径，找到相关代码，拿到SQL进行字段逐步排查，寻找到卡顿的原因，经我检查发现造成SQL查询慢的原因，如下：

注释掉卡顿字段SQL后，查询时间如下：

显而易见，卡的就是我红框圈出来的那块，查询20条数据都需要30秒，如果关联表数据再多点估计就是分钟级别了。

改造

将子查询抽出来，变成左连接查询，查询时间如下：

查询时间显而易见了，希望此篇文章对你有所帮助。

最后贴出SQL：

优化前
select * from ( select table_A., rownum rownum_A from (select T.,
DECODE( nvl(RA.STATUS_DISPLAY,0), 0, ‘未分配’, ‘已分配’ ) ROLE_STATUS_DISPLAY
from (SELECT USER_ACCOUNT.USER_ACCOUNT_ID, USER_ACCOUNT.ACCOUNT_NAME, USER_ACCOUNT.EMPNO, USER_ACCOUNT.CHANNEL_CODE, USER_ACCOUNT.SURNAME, USER_ACCOUNT.NAME, USER_ACCOUNT.ACCOUNT_TYPE, USER_ACCOUNT.STATUS, USER_ACCOUNT.PWD_STATUS, USER_ACCOUNT.UPDATE_DATE, ORG.ORG_NAME, ( SELECT POSITION.CODENAME FROM T_JOB_LEVEL POSITION WHERE POSITION.CODE = USER_ACCOUNT.JOB_LEVEL ) JOB_LEVEL_DISPLAY, ( SELECT DEV.DEV_CODE FROM PLAT_DEVICE DEV WHERE DEV.USER_ID = USER_ACCOUNT.USER_ACCOUNT_ID ) DEV_CODE FROM PLAT_USER_ACCOUNT USER_ACCOUNT, PLAT_ORG ORG WHERE ORG.ORG_CODE = USER_ACCOUNT.ORG_CODE AND NVL(USER_ACCOUNT.STATUS,1) != 2 AND (USER_ACCOUNT.IS_TEMPORARY=0 OR USER_ACCOUNT.IS_TEMPORARY is null) AND USER_ACCOUNT.CHANNEL_CODE=‘10’ AND USER_ACCOUNT.ORG_CODE IN ( SELECT T.ORG_CODE FROM PLAT_ORG T WHERE 1=1 START WITH ORG_CODE = ‘86’ CONNECT BY PRIOR ORG_CODE = PARENT_ORG_CODE) ) T left join ( SELECT COUNT(AR.ROLE_ID) STATUS_DISPLAY,AR.USER_ACCOUNT_ID USER_ACCOUNT_ID FROM PLAT_ACCOUNT_ROLE AR,PLAT_ROLE R WHERE AR.ROLE_ID = R.ROLE_ID AND NVL(R.STATUS,1) = 1 GROUP BY AR.USER_ACCOUNT_ID ) RA on ra.USER_ACCOUNT_ID = T.USER_ACCOUNT_ID ORDER BY T.UPDATE_DATE, T.NAME DESC) table_A
where rownum <= 20 ) where rownum_A > 0

优化后：
select * from ( select table_A., rownum rownum_A from (select T.,
DECODE( nvl(RA.STATUS_DISPLAY,0), 0, ‘未分配’, ‘已分配’ ) ROLE_STATUS_DISPLAY
from (SELECT USER_ACCOUNT.USER_ACCOUNT_ID, USER_ACCOUNT.ACCOUNT_NAME, USER_ACCOUNT.EMPNO, USER_ACCOUNT.CHANNEL_CODE, USER_ACCOUNT.SURNAME, USER_ACCOUNT.NAME, USER_ACCOUNT.ACCOUNT_TYPE, USER_ACCOUNT.STATUS, USER_ACCOUNT.PWD_STATUS, USER_ACCOUNT.UPDATE_DATE, ORG.ORG_NAME, ( SELECT POSITION.CODENAME FROM T_JOB_LEVEL POSITION WHERE POSITION.CODE = USER_ACCOUNT.JOB_LEVEL ) JOB_LEVEL_DISPLAY, ( SELECT DEV.DEV_CODE FROM PLAT_DEVICE DEV WHERE DEV.USER_ID = USER_ACCOUNT.USER_ACCOUNT_ID ) DEV_CODE FROM PLAT_USER_ACCOUNT USER_ACCOUNT, PLAT_ORG ORG WHERE ORG.ORG_CODE = USER_ACCOUNT.ORG_CODE AND NVL(USER_ACCOUNT.STATUS,1) != 2 AND (USER_ACCOUNT.IS_TEMPORARY=0 OR USER_ACCOUNT.IS_TEMPORARY is null) AND USER_ACCOUNT.CHANNEL_CODE=‘10’ AND USER_ACCOUNT.ORG_CODE IN ( SELECT T.ORG_CODE FROM PLAT_ORG T WHERE 1=1 START WITH ORG_CODE = ‘86’ CONNECT BY PRIOR ORG_CODE = PARENT_ORG_CODE) ) T left join ( SELECT COUNT(AR.ROLE_ID) STATUS_DISPLAY,AR.USER_ACCOUNT_ID USER_ACCOUNT_ID FROM PLAT_ACCOUNT_ROLE AR,PLAT_ROLE R WHERE AR.ROLE_ID = R.ROLE_ID AND NVL(R.STATUS,1) = 1 GROUP BY AR.USER_ACCOUNT_ID ) RA on ra.USER_ACCOUNT_ID = T.USER_ACCOUNT_ID ORDER BY T.UPDATE_DATE, T.NAME DESC) table_A
where rownum <= 20 ) where rownum_A > 0

总结

在编写SQL当中，尽量使用左/右连接，不要使用字段子查询的方式，这样会让你的SQL变得非常之慢(查询速度会随你的用户量倍增)。