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,
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函数也去掉