帮朋友优化一SQL---表重复全扫
来源：互联网 发布：阿里云幕布照片怎么ps 编辑：程序博客网 时间：2024/05/16 19:07
昨日，一朋友让我帮看个语句，原因是那个语句 写得过长，语句不容易理解，很多地方全表扫描了。。。。。
源SQL:
SELECT A.NAME, (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) AS EX, (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) AS RE, ROW_NUMBER() OVER(ORDER BY ROUND((CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) > (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) THEN CASE WHEN (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) = 0 THEN (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) ELSE (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) / (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) END ELSE CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) > 0 THEN 1 ELSE 0 END END),2) DESC, ROUND((CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) < (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) THEN CASE WHEN (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) = 0 THEN (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) ELSE (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) / (SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)) END ELSE CASE WHEN (SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) > 0 THEN 1 ELSE 0 END END),2) ASC) RN FROM TBL_DICTIONARY A, TBL_EDU_EXP B, TBL_RECRUITMENT_PROGRAM C WHERE A.TYPE_ID = 018 AND ((A.DICT_ID = B.MAJOR_S AND trunc(B.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(B.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND B.SCHOOL_ID IN (2012060631583)) OR (A.DICT_ID = C.PROFESSIONAL_REQUIREMENTS AND C.STATUS=1 AND ((trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(C.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND C.COMPANY_ID IN (2012022531438,2012031531465,2012020631316))) GROUP BY A.NAME,A.DICT_ID 源SQL的执行计划为：
其中表的数据量为：
select count(1) from tbl_edu_exp --1929 rows 全扫 9次
select count(1) from tbl_recruitment_program --228 rows 全扫8次
select count(1） from tbl_dictionary --2697 rows
分析：
-------------------------------------------------------------------------------------------------------
从执行计划中看的出有2张表（ tbl_edu_exp 、 tbl_recruitment_program ）多次重复全扫。
再看SQL语句，3张表连接，语句中重复出现的片断为：
SELECT NVL((SUM(R.RECRUITMENT_NUMBER)), 0) FROM TBL_RECRUITMENT_PROGRAM R WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)SELECT COUNT(DISTINCT E.EXP_ID) FROM TBL_EDU_EXP E WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583)于是准备把以上这2片断的结果集当做一个表用with ..as方式来处理，处理如下：
with sr as(SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316) and A.TYPE_ID = 018 group by a.dict_id ),ep as (SELECT a.dict_id,COUNT(DISTINCT E.EXP_ID) as expidcount FROM TBL_EDU_EXP E,TBL_DICTIONARY A WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583) and A.TYPE_ID = 018 group by a.dict_id )注意 ：SQL语句where条件中的逻辑结构是：
WHERE A.TYPE_ID = 018 AND ( (A.DICT_ID = B.MAJOR_S AND trunc(B.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(B.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND B.SCHOOL_ID IN (2012060631583)) OR (A.DICT_ID = C.PROFESSIONAL_REQUIREMENTS AND C.STATUS=1 AND ((trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(C.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(C.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(C.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND C.COMPANY_ID IN (2012022531438,2012031531465,2012020631316)) ) GROUP BY A.NAME,A.DICT_ID 中间有个or ，空行的下面
所以在where 部分要用union all来连接 with as的2个表。
最终调整：
-----------------------------------------------------------------------
with .. as改写语句：
with sr as(SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment,0 expidcount FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316) and A.TYPE_ID = 018 group by a.dict_id ),ep as (SELECT a.dict_id,0 as sum_recruitment,COUNT(DISTINCT E.EXP_ID) as expidcount FROM TBL_EDU_EXP E,TBL_DICTIONARY A WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583) and A.TYPE_ID = 018 group by a.dict_id ) SELECT A.NAME, (sum(b.expidcount)) AS ex, (sum(b.sum_recruitment)) AS re, ROW_NUMBER() OVER(ORDER BY ROUND((CASE WHEN (sum(b.expidcount)) > sum((b.sum_recruitment)) THEN CASE WHEN sum((b.sum_recruitment))=0 then sum(b.expidcount) else sum(b.expidcount)/sum((b.sum_recruitment)) end else CASE WHEN (sum(b.expidcount)) > 0 THEN 1 ELSE 0 END END),2) DESC, ROUND((CASE WHEN (sum(b.expidcount)) < (sum((b.sum_recruitment))) THEN CASE WHEN (sum(b.expidcount)) = 0 THEN (sum((b.sum_recruitment))) ELSE (sum((b.sum_recruitment))) / (sum(b.expidcount)) END ELSE CASE WHEN (sum((b.sum_recruitment))) > 0 THEN 1 ELSE 0 END END),2) ASC) RN FROM TBL_DICTIONARY A ,(select * from sr union all select * from ep) b where A.TYPE_ID = 018 and a.dict_id=b.dict_id GROUP BY A.NAME,A.DICT_ID 因朋友程序原因不能用with ..as来改写，最终调整改写如下：
SELECT A.NAME, (sum(b.expidcount)) AS ex, (sum(b.sum_recruitment)) AS re, ROW_NUMBER() OVER(ORDER BY ROUND((CASE WHEN (sum(b.expidcount)) > sum((b.sum_recruitment)) THEN CASE WHEN sum((b.sum_recruitment))=0 then sum(b.expidcount) else sum(b.expidcount)/sum((b.sum_recruitment)) end else CASE WHEN (sum(b.expidcount)) > 0 THEN 1 ELSE 0 END END),2) DESC, ROUND((CASE WHEN (sum(b.expidcount)) < (sum((b.sum_recruitment))) THEN CASE WHEN (sum(b.expidcount)) = 0 THEN (sum((b.sum_recruitment))) ELSE (sum((b.sum_recruitment))) / (sum(b.expidcount)) END ELSE CASE WHEN (sum((b.sum_recruitment))) > 0 THEN 1 ELSE 0 END END),2) ASC) RN FROM (SELECT a.dict_id,NVL((SUM(R.RECRUITMENT_NUMBER)), 0) as sum_recruitment,0 expidcount FROM TBL_RECRUITMENT_PROGRAM R ,TBL_DICTIONARY A WHERE R.PROFESSIONAL_REQUIREMENTS = A.DICT_ID AND R.STATUS=1 AND ((trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) >= TO_DATE('2012-12-31', 'YYYY-MM-DD')) OR (trunc(R.ENTERED_INTO_FORCE_DATE) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(R.MATURITY_DATE) <= TO_DATE('2012-12-31', 'YYYY-MM-DD'))) AND R.COMPANY_ID IN (2012022531438,2012031531465,2012020631316) and A.TYPE_ID = 018 group by a.dict_id union all SELECT a.dict_id,0 as sum_recruitment,COUNT(DISTINCT E.EXP_ID) as expidcount FROM TBL_EDU_EXP E,TBL_DICTIONARY A WHERE E.MAJOR_S = A.DICT_ID AND trunc(E.ENDTIME) >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND trunc(E.ENDTIME) <= TO_DATE('2012-12-31', 'YYYY-MM-DD') AND E.SCHOOL_ID IN (2012060631583) and A.TYPE_ID = 018 group by a.dict_id ) b , TBL_DICTIONARY A where A.TYPE_ID = 018 and a.dict_id=b.dict_id GROUP BY A.NAME,A.DICT_ID 以上改写SQL的执行计划为：
本次SQL优化，看下源SQL的执行计划然立马能定位到问题所在，再看下SQL的写法，很快就能确定解决方法了。
- 帮朋友优化一SQL---表重复全扫
- 牛刀小试——记一次帮朋友小幅优化SQL
- 【MySQL 帮助】帮朋友优化SQL的过程-->从4S到0.011秒
- 【MySQL 帮助】帮朋友优化SQL的过程-->从4S到0.011秒
- 帮盖尔优化SQL
- 一扫天下——ZXing使用全解析
- 一扫天下——ZXing使用全解析
- sql优化：避免全表扫描
- 避免全表扫描的sql优化
- 远程帮人优化SQL视频
- 帮朋友找实习生
- 帮朋友发的
- 新帮朋友做个网站，开始搜索引擎优化，分享优化过程并欢迎探讨
- 帮盖尔优化SQL-----子查询优化的经典案例
- Zbar扫码优化
- 二维码扫码优化
- 帮朋友写的一个累计SQL代码，有分季度，和总累计
- 帮朋友找网站漏洞
- iOS单元测试SenTest
- JSP 如何使用对象收集form表单的信息
- MyEclipse编码设置,中文乱码解决方法，UTF-8，GBK
- 杭电ACM 2027 统计元音
- 60条面向对象的设计原则
- 帮朋友优化一SQL---表重复全扫
- Flowers&&树状数组+离散化
- APUE学习——Ch2.Unix标准化与实现
- 解决strus2中文乱码
- 关于build/envsetup.sh
- 给图片添加阴影
- 二叉树操作
- ffmpeg解码实例
- 基于弹性计算平台——构建高可用、可扩展的应用
