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.DWFROM 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 CDOCPRIORITYFROM WF_DOC_GW_SHOUWEN SWUNION ALLSELECT NVL(CWENHAO, ' ') AS CWENHAO,NVL(CFWDW, '') AS DW,NDOCID,NJJCD,NVL(CDOCFROM, '本单位发文') AS CDOCFROM,NVL(CDOCPRIORITY, 0) AS CDOCPRIORITYFROM WF_DOC_GW_FAWEN FW) DWHERE A.NPROCID = B.NPROCID ANDA.NDOCID = D.NDOCID ANDA.NDOCSORTID = C.NDOCSORTID ANDC.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 OR27301 = A.RCV_ENTITYID) OR(27301 = A.TEMPORGID OR27301 = A.TEMPORGID)) AND 20 = A.RCV_ROLEID))) ORA.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.DWFROM 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 CDOCPRIORITYFROM WF_DOC_GW_SHOUWEN SWUNION ALLSELECT NVL(CWENHAO, ' ') AS CWENHAO,NVL(CFWDW, '') AS DW,NDOCID,NJJCD,NVL(CDOCFROM, '本单位发文') AS CDOCFROM,上一个:Oracle存储过程及问题备忘
下一个:RMAN异机备份的方法