需要优化的SQL
SELECT * FROM (SELECT A.*, ROWNUM R FROM (SELECT DISTINCT A.NDOCID AS NDOCID, TO_CHAR(NVL(A.DMODIFYDATE, A.DCREATEDATE), 'yyyy-MM-dd hh24:mi:ss') AS DMODIFYDATE, NVL(A.CURTITLE, ' ') AS CTITLE, A.DMODIFYDATE AS DM, A.DCREATEDATE, A.CURTITLE, A.NPROCID, A.NPROCSTATUS, A.NDOCSORTID, A.NMANAGERID, GW.CDOCPRIORITY, GW.CFWDW, GW.WENHAO, GW.CDOCUTYPE, COUNT(A.NDOCID) OVER() AS ROWNUMBER FROM WF_DOC_GW A, (SELECT NDOCID, NJJCD, CBT AS CTITLE, CFWDW, NVL(CDOCFROM, '本单位发文') AS CDOCFROM, NVL(CDOCPRIORITY, 100) AS CDOCPRIORITY, NVL(CFWZH, '无文号') AS WENHAO, CDOCUTYPE, CFWYDW FROM WF_DOC_GW_SHOUWEN SW UNION ALL SELECT NDOCID, NJJCD, CBT AS CTITLE, CFWDW, NVL(CDOCFROM, '本单位发文') AS CDOCFROM, NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY, NVL(CWENHAO, '无文号') AS WENHAO, CDOCUTYPE, CFWYDW FROM WF_DOC_GW_FAWEN FW) GW WHERE ((A.NPROCID = 20 AND A.NPROCSTATUS = 1) OR (A.NPROCID <> 20)) AND (A.NSTATE = 0 OR (A.NSTATE = 2 AND A.NDOCSORTID = 1)) AND A.NDOCSORTID IN (1, 2) AND ((INSTR(',' || A.CPROCUSERLIST || ',', ',' || '4301' || ',') > 0 AND A.NDOCID NOT IN (SELECT GI.SRC_NDOCID FROM WF_DOC_GW_INNER GI WHERE GI.SRC_USERID = 4301 AND GI.RETURNSTATUS IS NULL AND GI.SRC_NDOCID IS NOT NULL)) OR A.NPROCUID = 4301) AND (A.NMSGID = 0 OR (A.NMSGID IS NOT NULL AND A.NFWQBOPT = 10)) AND GW.NDOCID = A.NDOCID ORDER BY A.DMODIFYDATE DESC, GW.CDOCPRIORITY DESC) A WHERE ROWNUM <= 13) B WHERE R > 0?执行计划
?
-------------------------------------| Id | Operation | Name | E-Rows |-------------------------------------| 0 | SELECT STATEMENT | | ||* 1 | VIEW | | 13 ||* 2 | COUNT STOPKEY | | || 3 | VIEW | | 261 ||* 4 | SORT UNIQUE STOPKEY | | 261 || 5 | WINDOW BUFFER | | 261 ||* 6 | FILTER | | ||* 7 | HASH JOIN | | 90557 ||* 8 | TABLE ACCESS FULL | WF_DOC_GW | 84010 || 9 | VIEW | | 261K|| 10 | UNION-ALL | | || 11 | TABLE ACCESS FULL | WF_DOC_GW_SHOUWEN | 242K|| 12 | TABLE ACCESS FULL | WF_DOC_GW_FAWEN | 18876 ||* 13 | TABLE ACCESS BY INDEX ROWID| WF_DOC_GW_INNER | 1 ||* 14 | INDEX RANGE SCAN | IDX_INNER_SRCDOCID | 2 |-------------------------------------
?
?
Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("R">0) 2 - filter(ROWNUM<=13) 4 - filter(ROWNUM<=13) 6 - filter(((INSTR(','||"A"."CPROCUSERLIST"||',',',4301,')>0 AND IS NULL) OR "A"."NPROCUID"=4301)) 8 - filter((("A"."NSTATE"=0 OR ("A"."NSTATE"=2 AND "A"."NDOCSORTID"=1)) AND ("A"."NPROCID"<>20 OR ("A"."NPROCSTATUS"=1 AND "A"."NPROCID"=20)) AND ("A"."NMSGID"=0 OR ("A"."NFWQBOPT"=10 AND "A"."NMSGID" IS NOT NULL)) AND INTERNAL_FUNCTION("A"."NDOCSORTID"))) 12 - access("NDOCID"="A"."NDOCID") 14 - access("NDOCID"="A"."NDOCID") 15 - filter(("GI"."SRC_USERID"=4301 AND "GI"."RETURNSTATUS" IS NULL)) 16 - access("GI"."SRC_NDOCID"=:B1) filter("GI"."SRC_NDOCID" IS NOT NULL)?
该条SQL执行后的结果集只有2条数据，WF_DOC_GW_SHOUWEN数据量有24万，NDOCID是主键，从执行计划看，走的是全表扫描，和预期结果不符合，应该走的是唯一索引才对，优化的思路是先关联WF_DOC_GW和WF_DOC_GW_INNER，计算出结果集后，再关联WF_DOC_GW_SHOUWEN，这样就会走唯一索引，使用push_subq可以达到想要到想过，修改后的SQL如下
SELECT * FROM (SELECT A.*, ROWNUM R FROM (SELECT /*+ push_subq(@tmp)*/ DISTINCT A.NDOCID AS NDOCID, TO_CHAR(NVL(A.DMODIFYDATE, A.DCREATEDATE), 'yyyy-MM-dd hh24:mi:ss') AS DMODIFYDATE, NVL(A.CURTITLE, ' ') AS CTITLE, A.DMODIFYDATE AS DM, A.DCREATEDATE, A.CURTITLE, A.NPROCID, A.NPROCSTATUS, A.NDOCSORTID, A.NMANAGERID, GW.CDOCPRIORITY, GW.CFWDW, GW.WENHAO, GW.CDOCUTYPE, COUNT(A.NDOCID) OVER() AS ROWNUMBER FROM WF_DOC_GW A, (SELECT NDOCID, NJJCD, CBT AS CTITLE, CFWDW, NVL(CDOCFROM, '本单位发文') AS CDOCFROM, NVL(CDOCPRIORITY, 100) AS CDOCPRIORITY, NVL(CFWZH, '无文号') AS WENHAO, CDOCUTYPE, CFWYDW FROM WF_DOC_GW_SHOUWEN SW UNION ALL SELECT NDOCID, NJJCD, CBT AS CTITLE, CFWDW, NVL(CDOCFROM, '本单位发文') AS CDOCFROM, NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY, NVL(CWENHAO, '无文号') AS WENHAO, CDOCUTYPE, CFWYDW FROM WF_DOC_GW_FAWEN FW) GW WHERE ((A.NPROCID = 20 AND A.NPROCSTATUS = 1) OR (A.NPROCID <> 20)) AND (A.NSTATE = 0 OR (A.NSTATE = 2 AND A.NDOCSORTID = 1)) AND A.NDOCSORTID IN (1, 2) AND ((INSTR(',' || A.CPROCUSERLIST || ',', ',' || '4301' || ',') > 0 AND A.NDOCID NOT IN (SELECT /*+ qb_name(tmp)*/ GI.SRC_NDOCID FROM WF_DOC_GW_INNER GI WHERE GI.SRC_USERID = 4301 AND GI.RETURNSTATUS IS NULL AND GI.SRC_NDOCID IS NOT NULL)) OR A.NPROCUID = 4301) AND (A.NMSGID = 0 OR (A.NMSGID IS NOT NULL AND A.NFWQBOPT = 10)) AND GW.NDOCID = A.NDOCID ORDER BY A.DMODIFYDATE DESC, GW.CDOCPRIORITY DESC) A WHERE ROWNUM <= 13) B WHERE R > 0? ?
------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 13 | 9984 | 2518 (1)| 00:00:31 ||* 1 | VIEW | | 13 | 9984 | 2518 (1)| 00:00:31 ||* 2 | COUNT STOPKEY | | | | | || 3 | VIEW | | 244 | 179K| 2518 (1)| 00:00:31 ||* 4 | SORT UNIQUE STOPKEY | | 244 | 63928 | 2517 (1)| 00:00:31 || 5 | WINDOW BUFFER | | 244 | 63928 | 2518 (1)| 00:00:31 || 6 | NESTED LOOPS | | 244 | 63928 | 2516 (1)| 00:00:31 ||* 7 | TABLE ACCESS FULL | WF_DOC_GW | 242 | 27588 | 1547 (2)| 00:00:19 ||* 8 | TABLE ACCESS BY INDEX ROWID | WF_DOC_GW_INNER | 1 | 17 | 2 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_INNER_SRCDOCID | 2 | | 1 (0)| 00:00:01 || 10 | VIEW | | 1 | 148 | 4 (0)| 00:00:01 || 11 | UNION ALL PUSHED PREDICATE | | | | | || 12 | TABLE ACCESS BY INDEX ROWID| WF_DOC_GW_SHOUWEN | 1 | 49 | 2 (0)| 00:00:01 ||* 13 | INDEX UNIQUE SCAN | PK_WF_DOC_GW_SHOUWEN | 1 | | 1 (0)| 00:00:01 || 14 | TABLE ACCESS BY INDEX ROWID| WF_DOC_GW_FAWEN | 1 | 38 | 2 (0)| 00:00:01 ||* 15 | INDEX UNIQUE SCAN | PK_WF_DOC_GW_FAWEN | 1 | | 1 (0)| 00:00:01 |
?
优化前的逻辑读是12000，优化后的逻辑读降到了5000，还是蛮有效果的