好久不写博客了，一来是个人比较懒，二来是帮别人优化sql之后不喜欢收集案例，三则是有些经典优化案例客户不方便发。最近遇到一个有点坑爹的sql优化案例，我觉得非常
有必要分享给大家。废话不多说，SQL如下：
select *from (select z_results.*, rownum autorownofrom (select /*+first_rows(20)*/A.dd_id,A.ysje,A.ssje,A.xjbz,to_char(A.lrsj, 'yyyy-mm-dd') as lrsj,a.kh_id,a.jbr_id,to_char(A.zxkprq, 'yyyy-mm-dd') as zxkprq,a.khlx,A.ddlx,A.kpzt,A.skzt,A.fzgs_dm,casewhen a.JSDKJZT = '1' thencasewhen (select sum(h.fpje)from (select f.fpje, g.kppj_idfrom pjkgl_fpxx f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '2'and f.zfbz = '0'union allselect f.fpje, g.kppj_idfrom PJKGL_FPXX_DISABLED f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '2'and f.zfbz = '0') hwhere h.kppj_id = a.dd_id) is null then0else(select sum(fpje)from (select f.fpje, g.kppj_idfrom pjkgl_fpxx f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '2'and f.zfbz = '0'union allselect f.fpje, g.kppj_idfrom PJKGL_FPXX_DISABLED f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '2'and f.zfbz = '0') hwhere h.kppj_id = a.dd_id)endelsecasewhen (select sum(fpje)from (select f.fpje, g.kppj_idfrom pjkgl_fpxx f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '1'and f.zfbz = '0'union allselect f.fpje, g.kppj_idfrom PJKGL_FPXX_DISABLED f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '1'and f.zfbz = '0') hwhere h.kppj_id = a.dd_id) is null then0else(select sum(fpje)from (select f.fpje, g.kppj_idfrom pjkgl_fpxx f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '1'and f.zfbz = '0'union allselect f.fpje, g.kppj_idfrom PJKGL_FPXX_DISABLED f, pjkgl_pjglxx gwhere f.fp_id = g.fp_idand g.zxfs = '1'and f.pjlx = '1'and f.zfbz = '0') hwhere h.kppj_id = a.dd_id)endend as kpje,a.JSDKJZT,a.xsfsfrom swgl_ddjbxx aWHERE a.zfbz = '0'and (a.ysje > 0 or a.ysje < 0 or(a.ysje = 0 and a.kpzt = '3'))and (a.ZXKPRQ >=to_date('2014-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS') ora.KJJSDSJ >=to_date('2014-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS'))and (a.ZXKPRQ <=to_date('2014-11-05 23:59:59','YYYY-MM-DD HH24:MI:SS') ora.KJJSDSJ <=to_date('2014-11-05 23:59:59','YYYY-MM-DD HH24:MI:SS'))and (a.XJBZ = '9999')and (((a.ddlx in ('0', '2') and a.ddzt = '12') or(a.ddlx = '1' and a.ddzt in ('04', '99'))))and (a.XSJGFZGS_DM = '001081')and (exists(select 1from swgl_ddhjxx hjwhere hj.dd_id = a.dd_idand (hj.hjlx = '03' or hj.hjlx = '06' orhj.hjlx = '07' or hj.hjlx = '08' orhj.hjlx = '09')and hj.JBR_ID = '9F923C36CF0BA0DCA76A5F30D642A75C'))order by zxkprq desc, dd_id desc) z_resultswhere rownum <= 20)where autorowno >= 1;Execution Plan
----------------------------------------------------------
Plan hash value: 786935727--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
| 2 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 40 | | |
| 17 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 18 | UNION-ALL | | | | | |
| 19 | NESTED LOOPS | | | | | |
| 20 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | | | | |
| 26 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 40 | | |
| 32 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 33 | UNION-ALL | | | | | |
| 34 | NESTED LOOPS | | | | | |
| 35 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | | | | |
| 41 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 46 | SORT AGGREGATE | | 1 | 40 | | |
| 47 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 48 | UNION-ALL | | | | | |
| 49 | NESTED LOOPS | | | | | |
| 50 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | | | | |
| 56 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
|* 61 | VIEW | | 1 | 173 | 25 (4)| 00:00:01 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 1 | 160 | 25 (4)| 00:00:01 |
|* 64 | SORT ORDER BY STOPKEY | | 1 | 177 | 25 (4)| 00:00:01 |
| 65 | NESTED LOOPS SEMI | | 1 | 177 | 24 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 127 | 19 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_XJBZ | 39 | | 3 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID | SWGL_DDHJXX | 1077 | 53850 | 5 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | IDX_SWGL_DDHJXX_DDID | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------6 - filter("G"."ZXFS"='1')7 - access("G"."KPPJ_ID"=:B1)8 - access("F"."FP_ID"="G"."FP_ID")9 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')12 - filter("G"."ZXFS"='1')13 - access("G"."KPPJ_ID"=:B1)14 - access("F"."FP_ID"="G"."FP_ID")15 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')21 - filter("G"."ZXFS"='1')22 - access("G"."KPPJ_ID"=:B1)23 - access("F"."FP_ID"="G"."FP_ID")24 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')27 - filter("G"."ZXFS"='1')28 - access("G"."KPPJ_ID"=:B1)29 - access("F"."FP_ID"="G"."FP_ID")30 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')36 - filter("G"."ZXFS"='1')37 - access("G"."KPPJ_ID"=:B1)38 - access("F"."FP_ID"="G"."FP_ID")39 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')42 - filter("G"."ZXFS"='1')43 - access("G"."KPPJ_ID"=:B1)44 - access("F"."FP_ID"="G"."FP_ID")45 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')51 - filter("G"."ZXFS"='1')52 - access("G"."KPPJ_ID"=:B1)53 - access("F"."FP_ID"="G"."FP_ID")54 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')57 - filter("G"."ZXFS"='1')58 - access("G"."KPPJ_ID"=:B1)59 - access("F"."FP_ID"="G"."FP_ID")60 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')61 - filter("AUTOROWNO">=21)62 - filter(ROWNUM<=40)64 - filter(ROWNUM<=40)66 - filter(("A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR"A"."KJJSDSJ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND ("A"."ZXKPRQ"<=TO_DATE(' 2014-11-0523:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "A"."KJJSDSJ"<=TO_DATE(' 2014-11-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))AND "A"."XSJGFZGS_DM"='001097' AND "A"."DDZT"='12' AND "A"."ZFBZ"='0' AND ("A"."DDLX"='0' OR "A"."DDLX"='2') AND("A"."YSJE"<>0 OR "A"."YSJE"=0 AND "A"."KPZT"='3'))67 - access("A"."XJBZ"='9999')68 - filter("HJ"."JBR_ID"='649013B54CF555D3E4A7A1980F7854B3' AND ("HJ"."HJLX"='03' OR "HJ"."HJLX"='06' OR"HJ"."HJLX"='07' OR "HJ"."HJLX"='08' OR "HJ"."HJLX"='09'))69 - access("HJ"."DD_ID"="A"."DD_ID")Statistics
----------------------------------------------------------1 recursive calls0 db block gets2115589 consistent gets38 physical reads364 redo size1405 bytes sent via SQL*Net to client509 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)0 rows processed
这个sql是一个分页语句，取第一页，逻辑读 2115589 ，200w的逻辑读，肯定不能接受。这个sql是一个税务局的sql,国企的大爷们，sql跑得慢点其实无所谓，顶多老子出去抽
根烟回来再看。可是我们dba心里过意不去啊，200w逻辑读，要跑10多秒，反正看不惯它。如果并发多了，跑得更久了。这种sql就是祸害。
分页语句， 不管那一页 返回数据都很少，这里又是第一页，很明显，应该让这个sql 走嵌套循环(返回数据少不走这个走啥？) 走嵌套循环你得选定驱动表，驱动表一般就是
order by 的表，如果 order by 2个表，那去死吧，无法优化(当然了，你可以搞物化视图，再在mv上面创建 order by 的索引) 这个sql还好 order by 是一个表的条件
order by zxkprq desc, dd_id desc 那么，很明显，就在 这2个例建立组合索引，然后 走index full scan desc 扫描20条数据就停止。于是创建了一个索引
create index idx_sb on swgl_ddjbxx(zxkprq,dd_id) nologging online parallel 4 ;
然后加了一个hint index_desc(a idx_sb) ，索引是 走了 而且确实是走的 index full scan desc ，但是 他妈的， 居然里面有 sort order by。索引本来就是有序的啊， 都走了
index full scan desc 他妈的 还去 sort order by 那 跑得快个毛 ，建立索引反而跑更慢了。 这不科学啊，你大爷的 ，哥优化这么多sql 还没遇到这种怪事，我本来很不喜欢
10053 trace， 也不喜欢 10046 trace，但是 有时候 不用还真解决不了问题。 于是让兄弟搞个测试表，然后写了一个sql模拟上面情况，再做一个10053 trace ，看一下为啥 还有
sort order by 。关键的 10053 trace 代码如下：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."DD_ID" "DD_ID","from$_subquery$_001"."YSJE" "YSJE","from$_subquery$_001"."SSJE" "SSJE","from$_subquery$_001"."XJBZ" "XJBZ","from$_subquery$_001"."LRSJ" "LRSJ","from$_subquery$_001"."KH_ID" "KH_ID",
"from$_subquery$_001"."JBR_ID" "JBR_ID","from$_subquery$_001"."ZXKPRQ" "ZXKPRQ","from$_subquery$_001"."KHLX" "KHLX","from$_subquery$_001"."DDLX" "DDLX","from$_subquery$_001"."KPZT" "KPZT","from$_subquery$_001"."SKZT" "SKZT","from$_subquery$_001"."FZGS_DM" "FZGS_DM"
FROM (SELECT /*+ INDEX_DESC ("A" "IDX_TEST_1") */ "A"."DD_ID" "DD_ID","A"."YSJE" "YSJE","A"."SSJE" "SSJE","A"."XJBZ" "XJBZ",TO_CHAR("A"."LRSJ",'yyyy-mm-dd') "LRSJ","A"."KH_ID" "KH_ID","A"."JBR_ID" "JBR_ID",TO_CHAR("A"."ZXKPRQ",'yyyy-mm-dd') "ZXKPRQ","A"."KHLX" "KHLX",
"A"."DDLX" "DDLX","A"."KPZT" "KPZT","A"."SKZT" "SKZT","A"."FZGS_DM" "FZGS_DM" FROM "NBGL2"."TEST1" "A" WHERE "A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') ORDER BY TO_CHAR("A"."ZXKPRQ",'yyyy-mm-dd') DESC) "from$_subquery$_001" WHERE ROWNUM<=20
看最后面， sql最终被翻译为 ................. order by to_char(a.zxkprq,'yyyy-mm-dd') desc ， 但是 我原始的sql是 直接的 order by a.zxkprq 。卧槽 优化器 SB了。 这个问题我不
想管了， 要么表 里面 数据格式不对，要么是 某个参数 被改了， 但是 这个问题 我不想继续搞了，难得去搞。于是创建了下面的索引：
create index idx_bigsb on swgl_ddjbxx(to_char(zxkprq,'yyyy-mm-dd'),dd_id) nologging online parallel 4 ;
最终确实是走了索引， 而且没有sort order by 了， 但是 逻辑读更大了，之前200w ，现在妈的 1000w多了。这是为啥呢？ 哦草，原来忘记了， 这个sql返回0条记录。我草，这
个sql返回0条记录，那不就是相当于取分页语句里面最后一页？ 意思就是说 要 index full scan desc 把整个索引叶子块全扫描了，而且还得回表！ 卧槽，这是最垃圾的执行计
划。于是变更思路，这个sql不是有个 exists吗？ 我们来看 exists 返回多少记录，我跑了一下，返回1w左右。 from 之后的主表 有 上千万条数据。 好的 ，那思路来了，就让
exists 去驱动主表吧。于是sql 改写为下面：
SQL> set autot trace
SQL> select *2 from (select z_results.*, rownum autorowno3 from (select /*+ first_rows LEADING("HJ"@"SB") no_expand */4 A.dd_id,5 A.ysje,6 A.ssje,7 A.xjbz,8 to_char(A.lrsj, 'yyyy-mm-dd') as lrsj,9 a.kh_id,10 a.jbr_id,11 to_char(A.zxkprq, 'yyyy-mm-dd') as zxkprq,12 a.khlx,13 A.ddlx,14 A.kpzt,15 A.skzt,16 A.fzgs_dm,17 case18 when a.JSDKJZT = '1' then19 case20 when (select sum(h.fpje)21 from (select f.fpje, g.kppj_id22 from pjkgl_fpxx f, pjkgl_pjglxx g23 where f.fp_id = g.fp_id24 and g.zxfs = '1'25 and f.pjlx = '2'26 and f.zfbz = '0'27 union all28 select f.fpje, g.kppj_id29 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g30 where f.fp_id = g.fp_id31 and g.zxfs = '1'32 and f.pjlx = '2'33 and f.zfbz = '0') h34 where h.kppj_id = a.dd_id) is null then35 036 else37 (select sum(fpje)38 from (select f.fpje, g.kppj_id39 from pjkgl_fpxx f, pjkgl_pjglxx g40 where f.fp_id = g.fp_id41 and g.zxfs = '1'42 and f.pjlx = '2'43 and f.zfbz = '0'44 union all45 select f.fpje, g.kppj_id46 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g47 where f.fp_id = g.fp_id48 and g.zxfs = '1'49 and f.pjlx = '2'50 and f.zfbz = '0') h51 where h.kppj_id = a.dd_id)52 end53 else54 case55 when (select sum(fpje)56 from (select f.fpje, g.kppj_id57 from pjkgl_fpxx f, pjkgl_pjglxx g58 where f.fp_id = g.fp_id59 and g.zxfs = '1'60 and f.pjlx = '1'61 and f.zfbz = '0'62 union all63 select f.fpje, g.kppj_id64 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g65 where f.fp_id = g.fp_id66 and g.zxfs = '1'67 and f.pjlx = '1'68 and f.zfbz = '0') h69 where h.kppj_id = a.dd_id) is null then70 071 else72 (select sum(fpje)73 from (select f.fpje, g.kppj_id74 from pjkgl_fpxx f, pjkgl_pjglxx g75 where f.fp_id = g.fp_id76 and g.zxfs = '1'77 and f.pjlx = '1'78 and f.zfbz = '0'79 union all80 select f.fpje, g.kppj_id81 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g82 where f.fp_id = g.fp_id83 and g.zxfs = '1'84 and f.pjlx = '1'85 and f.zfbz = '0') h86 where h.kppj_id = a.dd_id)87 end88 end as kpje,89 a.JSDKJZT,90 a.xsfs91 from swgl_ddjbxx a92 WHERE a.zfbz = '0'93 and (a.ysje > 0 or a.ysje < 0 or94 (a.ysje = 0 and a.kpzt = '3'))95 and (a.ZXKPRQ >=96 to_date('2014-10-05 00:00:00',97 'YYYY-MM-DD HH24:MI:SS') or98 a.KJJSDSJ >=99 to_date('2014-10-05 00:00:00',
100 'YYYY-MM-DD HH24:MI:SS'))
101 and (a.ZXKPRQ <=
102 to_date('2014-11-05 23:59:59',
103 'YYYY-MM-DD HH24:MI:SS') or
104 a.KJJSDSJ <=
105 to_date('2014-11-05 23:59:59',
106 'YYYY-MM-DD HH24:MI:SS'))
107 and (a.XJBZ = '9999')
108 and (((a.ddlx in ('0', '2') and a.ddzt = '12') or
109 (a.ddlx = '1' and a.ddzt in ('04', '99'))))
110 and (a.XSJGFZGS_DM = '001081')
111 and (a.dd_id in (select /*+ qb_name(sb) */ dd_id
112 from swgl_ddhjxx hj
113 where (hj.hjlx = '03' or hj.hjlx = '06' or hj.hjlx = '07' or
114 hj.hjlx = '08' or hj.hjlx = '09')
115 and hj.JBR_ID = '9F923C36CF0BA0DCA76A5F30D642A75C'))
116 order by zxkprq desc, dd_id desc) z_results
117 where rownum <= 20)
118 where autorowno >= 1;no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 4195696224--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 3323 (1)| 00:00:40 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
| 2 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 40 | | |
| 17 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 18 | UNION-ALL | | | | | |
| 19 | NESTED LOOPS | | | | | |
| 20 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | | | | |
| 26 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 40 | | |
| 32 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 33 | UNION-ALL | | | | | |
| 34 | NESTED LOOPS | | | | | |
| 35 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | | | | |
| 41 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 46 | SORT AGGREGATE | | 1 | 40 | | |
| 47 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 48 | UNION-ALL | | | | | |
| 49 | NESTED LOOPS | | | | | |
| 50 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | | | | |
| 56 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
|* 61 | VIEW | | 1 | 173 | 3323 (1)| 00:00:40 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 1 | 160 | 3323 (1)| 00:00:40 |
|* 64 | SORT ORDER BY STOPKEY | | 1 | 184 | 3323 (1)| 00:00:40 |
| 65 | NESTED LOOPS | | | | | |
| 66 | NESTED LOOPS | | 1 | 184 | 3322 (1)| 00:00:40 |
| 67 | SORT UNIQUE | | 1078 | 53900 | 1637 (1)| 00:00:20 |
|* 68 | TABLE ACCESS BY INDEX ROWID | SWGL_DDHJXX | 1078 | 53900 | 1637 (1)| 00:00:20 |
|* 69 | INDEX RANGE SCAN | IDX_SWGL_DDHJXX_JBRID | 5632 | | 50 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | PK_SWGL_DDJBXX | 1 | | 2 (0)| 00:00:01 |
|* 71 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 134 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------6 - filter("G"."ZXFS"='1')7 - access("G"."KPPJ_ID"=:B1)8 - access("F"."FP_ID"="G"."FP_ID")9 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')12 - filter("G"."ZXFS"='1')13 - access("G"."KPPJ_ID"=:B1)14 - access("F"."FP_ID"="G"."FP_ID")15 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')21 - filter("G"."ZXFS"='1')22 - access("G"."KPPJ_ID"=:B1)23 - access("F"."FP_ID"="G"."FP_ID")24 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')27 - filter("G"."ZXFS"='1')28 - access("G"."KPPJ_ID"=:B1)29 - access("F"."FP_ID"="G"."FP_ID")30 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')36 - filter("G"."ZXFS"='1')37 - access("G"."KPPJ_ID"=:B1)38 - access("F"."FP_ID"="G"."FP_ID")39 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')42 - filter("G"."ZXFS"='1')43 - access("G"."KPPJ_ID"=:B1)44 - access("F"."FP_ID"="G"."FP_ID")45 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')51 - filter("G"."ZXFS"='1')52 - access("G"."KPPJ_ID"=:B1)53 - access("F"."FP_ID"="G"."FP_ID")54 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')57 - filter("G"."ZXFS"='1')58 - access("G"."KPPJ_ID"=:B1)59 - access("F"."FP_ID"="G"."FP_ID")60 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')61 - filter("AUTOROWNO">=1)62 - filter(ROWNUM<=20)64 - filter(ROWNUM<=20)68 - filter("HJ"."HJLX"='03' OR "HJ"."HJLX"='06' OR "HJ"."HJLX"='07' OR "HJ"."HJLX"='08' OR "HJ"."HJLX"='09')69 - access("HJ"."JBR_ID"='9F923C36CF0BA0DCA76A5F30D642A75C')70 - access("A"."DD_ID"="DD_ID")71 - filter("A"."XJBZ"='9999' AND ("A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR"A"."KJJSDSJ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND ("A"."ZXKPRQ"<=TO_DATE(' 2014-11-0523:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "A"."KJJSDSJ"<=TO_DATE(' 2014-11-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))AND "A"."XSJGFZGS_DM"='001081' AND "A"."ZFBZ"='0' AND ("A"."DDZT"='12' AND ("A"."DDLX"='0' OR "A"."DDLX"='2') OR"A"."DDLX"='1' AND ("A"."DDZT"='04' OR "A"."DDZT"='99')) AND ("A"."YSJE"<>0 OR "A"."YSJE"=0 AND "A"."KPZT"='3'))Statistics
----------------------------------------------------------1 recursive calls0 db block gets12314 consistent gets0 physical reads0 redo size1405 bytes sent via SQL*Net to client509 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client2 sorts (memory)0 sorts (disk)0 rows processed然后搞了几个hint 最终 逻辑读 12314 还过得去，好吧，就这样吧，不继续优化了。 真他奶奶的蛋疼。
更新一下， SQL 不走 sort order by 是 select 后面 to_char(A.zxkprq, 'yyyy-mm-dd') as zxkprq, 然后又来 order by zxkprq 卧槽， 这些鸟开发 怎么去别名 跟原始列同样的 草！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
??
??