当前位置:操作系统 > Unix/Linux >>

hints的push_pred应用

hints的push_pred应用
 
在项目中优化了一条SQL,当时从40多秒减少到了2秒,感觉很有成就感,现在反过头来又看了一次,觉得仍然有优化的余地,SQL如下
 
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表的数据量都比较多,有上千万条,而这两张表只是为了取字段的内容,没有过滤任何数据,于是想到,应该把谓词推入到视图中,这样就能用到索引,避免了全表扫描
 
Sql代码  
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,  
               
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,