hints的push_pred应用

前俩年在项目中优化了一条SQL，当时从40多秒减少到了2秒，感觉很有成就感，现在反过头来又看了一次，觉得仍然有优化的余地，SQL如下

 

SELECT DISTINCT A.CURTITLE AS CTITLE,
                A.DMODIFYDATE,
                A.NDOCID AS NDOCID,
                A.NPROCID AS NPROCID,
                B.CPROCNAME AS CPROCNAME,
                B.NDAYS AS TRUE_DAYS,
                (SYSDATE - A.DMODIFYDATE) AS DAYSLEFT,
                A.NDOCSORTID AS NDOCSORTID,
                A.NPROCSTATUS AS NPROCSTATUS,
                C.CNAME AS DOCSORTNAME,
                NVL(D.NJJCD, 0) NJJCD,
                D.CDOCFROM AS CDOCFROM,
                D.CDOCPRIORITY AS CDOCPRIORITY,
                D.CWENHAO,
                A.NFWQBOPT,
                D.DW
  FROM WF_DOC_GW A,
       WF_PROCNAME B,
       WF_DOCSORT C,      
       (SELECT   NVL(CFWZH, '') AS CWENHAO,
                NVL(CFWDW, ' ') AS DW,
                NDOCID,
                NJJCD,
                NVL(CDOCFROM, ' ') AS CDOCFROM,
                NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY
           FROM WF_DOC_GW_SHOUWEN SW
         UNION ALL
         SELECT NVL(CWENHAO, ' ') AS CWENHAO,
                NVL(CFWDW, '') AS DW,
                NDOCID,
                NJJCD,
                NVL(CDOCFROM, '本单位发文') AS CDOCFROM,
                NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY
           FROM WF_DOC_GW_FAWEN FW) D
 WHERE A.NPROCID = B.NPROCID AND
       A.NDOCID = D.NDOCID AND
       A.NDOCSORTID = C.NDOCSORTID AND
       C.NDOCSORTID IN (1, 2) AND
       (A.NSTATE = 0 OR (A.NSTATE = 2 AND A.NDOCSORTID = 1)) AND
       ((((INSTR(',' || A.CPROCUSERLIST || ',',
                 ',' || 31601 || ',') > 0 ) OR
       (((27301 = A.RCV_ORGID OR
       27301 = A.RCV_ENTITYID) OR
       (27301 = A.TEMPORGID OR
       27301 = A.TEMPORGID)) AND 20 = A.RCV_ROLEID))) OR
       A.NPROCUID = 31601) AND      
       (A.NMSGID = 0 OR (A.NMSGID IS NOT NULL AND A.NFWQBOPT = 10)) AND
       (A.CURTITLE IS NOT NULL) AND
       ((B.NPROCID = 20 AND A.NPROCSTATUS = 1) OR (B.NPROCID <> 20))
 ORDER BY A.DMODIFYDATE DESC;
 

 

从业务上看，这条SQL最终的结果集只有几条记录，而WF_DOC_GW_SHOUWEN和WF_DOC_GW_FAWEN表的数据量都比较多，有上千万条，而这两张表只是为了取字段的内容，没有过滤任何数据，于是想到，应该把谓词推入到视图中，这样就能用到索引，避免了全表扫描

 

SELECT /*+ push_pred(d)*/  DISTINCT A.CURTITLE AS CTITLE,
                A.DMODIFYDATE,
                A.NDOCID AS NDOCID,
                A.NPROCID AS NPROCID,
                B.CPROCNAME AS CPROCNAME,
                B.NDAYS AS TRUE_DAYS,
                (SYSDATE - A.DMODIFYDATE) AS DAYSLEFT,
                A.NDOCSORTID AS NDOCSORTID,
                A.NPROCSTATUS AS NPROCSTATUS,
                C.CNAME AS DOCSORTNAME,
                NVL(D.NJJCD, 0) NJJCD,
                D.CDOCFROM AS CDOCFROM,
                D.CDOCPRIORITY AS CDOCPRIORITY,
                D.CWENHAO,
                A.NFWQBOPT,
                D.DW
  FROM WF_DOC_GW A,
       WF_PROCNAME B,
       WF_DOCSORT C,      
       (SELECT   NVL(CFWZH, '') AS CWENHAO,
                NVL(CFWDW, ' ') AS DW,
                NDOCID,
                NJJCD,
                NVL(CDOCFROM, ' ') AS CDOCFROM,
                NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY
           FROM WF_DOC_GW_SHOUWEN SW
         UNION ALL
         SELECT NVL(CWENHAO, ' ') AS CWENHAO,
                NVL(CFWDW, '') AS DW,
                NDOCID,
                NJJCD,
                NVL(CDOCFROM, '本单位发文') AS CDOCFROM,
                NVL(CDOCPRIORITY, 0) AS CDOCPRIORITY
           FROM WF_DOC_GW_FAWEN FW) D
 WHERE A.NPROCID = B.NPROCID AND
       A.NDOCID = D.NDOCID AND
       A.NDOCSORTID = C.NDOCSORTID AND
       C.NDOCSORTID IN (1, 2) AND
       (A.NSTATE = 0 OR (A.NSTATE = 2 AND A.NDOCSORTID = 1)) AND
       ((((INSTR(',' || A.CPROCUSERLIST || ',',
                 ',' || 31601 || ',') > 0 ) OR
       (((27301 = A.RCV_ORGID OR
       27301 = A.RCV_ENTITYID) OR
       (27301 = A.TEMPORGID OR
       27301 = A.TEMPORGID)) AND 20 = A.RCV_ROLEID))) OR
       A.NPROCUID = 31601) AND      
       (A.NMSGID = 0 OR (A.NMSGID IS NOT NULL AND A.NFWQBOPT = 10)) AND
       (A.CURTITLE IS NOT NULL) AND
       ((B.NPROCID = 20 AND A.NPROCSTATUS = 1) OR (B.NPROCID <> 20))
 ORDER BY A.DMODIFYDATE DESC

  

只是加了一个hints /*+ push_pred(d)*/，时间从2秒降到了46毫秒，逻辑读也降了一半，执行计划太长，这里就不贴了，本着精益求精的态度，最后应该把中间过程的nvl函数也去掉