在用户现场,碰到了之前未发现的问题。
如下的sql语句,在执行时,长时间无响应,尝试过去掉部分列,就没有问题了,但是目前希望保留全部查询结构,想知道,究竟查询语句中,哪个地方引起了查询的无响应,语法上编译是通过的。
SELECT ADPRJ03.PROJECTID AS "SYS_PROCESSCASEID" ,A.PROJECTNAME AS "项目名称",B.ACTIONNAME AS "工作任务",TO_DATE(ADPRJ03.PLANDATEOFEND,'YYYY-MM-DD') AS "计划完成时间",C.USERCNAME AS "负责人",ADPRJ03.TASKDESCRIPTION AS "主要工作内容",
D.USERCNAME AS "填表人",TO_DATE(D.OPERDATE,'YYYY-MM-DD') AS "日期",F.USERCNAME AS "审核人(处理部)",G.USERCNAME AS "审核人(生产管理部)",H.ORGANIZATIONNAME AS "填表单位"
FROM ADPRJ03
LEFT JOIN ADPRJ70 A ON A.PROJECTID=ADPRJ03.PROJECTID
LEFT JOIN PSWFD07 B ON B.ACTIONID=ADPRJ03.ASSIGNMENTID
LEFT JOIN ASREG005 C ON C.USERID=ADPRJ03.EXECUTOR
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,AA.USERCNAME,PDWFC04.OPERDATE
FROM PDWFC04
LEFT JOIN ASREG005 AA ON AA.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE007' AND PDWFC04.CURRENTSTATUS='A05'
) D ON (D.PROCASEID=ADPRJ03.PROJECTID)LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,BB.USERCNAME
FROM PDWFC04
LEFT JOIN ASREG005 BB ON BB.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE008' AND PDWFC04.CURRENTSTATUS='A05') F ON (F.PROCASEID=ADPRJ03.PROJECTID)left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,CC.USERCNAME
FROM PDWFC04
LEFT JOIN ASREG005 CC ON CC.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE009' AND PDWFC04.CURRENTSTATUS='A05'
) G ON (G.PROCASEID=ADPRJ03.PROJECTID)LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,DD.USERCNAME,FF.ORGANIZATIONNAME
FROM PDWFC04
LEFT JOIN ASREG005 DD ON DD.USERID=PDWFC04.OPERATOR
LEFT JOIN (select POSTID,ORGANIZATIONID FROM ASREG004) EE ON EE.POSTID=DD.POSTID
LEFT JOIN ASREG001 FF ON FF.ORGANIZATIONID=EE.ORGANIZATIONID
WHERE PDWFC04.ACTIONID='PC064RE007' AND PDWFC04.CURRENTSTATUS='A05' )
H ON (H.PROCASEID=ADPRJ03.PROJECTID)
ORDER BY "项目名称","计划完成时间"ps:另外,也想知道,那些是我们写查询时须注意的呢?(想避免这种运行时的无响应情况发生)
如下的sql语句,在执行时,长时间无响应,尝试过去掉部分列,就没有问题了,但是目前希望保留全部查询结构,想知道,究竟查询语句中,哪个地方引起了查询的无响应,语法上编译是通过的。
SELECT ADPRJ03.PROJECTID AS "SYS_PROCESSCASEID" ,A.PROJECTNAME AS "项目名称",B.ACTIONNAME AS "工作任务",TO_DATE(ADPRJ03.PLANDATEOFEND,'YYYY-MM-DD') AS "计划完成时间",C.USERCNAME AS "负责人",ADPRJ03.TASKDESCRIPTION AS "主要工作内容",
D.USERCNAME AS "填表人",TO_DATE(D.OPERDATE,'YYYY-MM-DD') AS "日期",F.USERCNAME AS "审核人(处理部)",G.USERCNAME AS "审核人(生产管理部)",H.ORGANIZATIONNAME AS "填表单位"
FROM ADPRJ03
LEFT JOIN ADPRJ70 A ON A.PROJECTID=ADPRJ03.PROJECTID
LEFT JOIN PSWFD07 B ON B.ACTIONID=ADPRJ03.ASSIGNMENTID
LEFT JOIN ASREG005 C ON C.USERID=ADPRJ03.EXECUTOR
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,AA.USERCNAME,PDWFC04.OPERDATE
FROM PDWFC04
LEFT JOIN ASREG005 AA ON AA.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE007' AND PDWFC04.CURRENTSTATUS='A05'
) D ON (D.PROCASEID=ADPRJ03.PROJECTID)LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,BB.USERCNAME
FROM PDWFC04
LEFT JOIN ASREG005 BB ON BB.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE008' AND PDWFC04.CURRENTSTATUS='A05') F ON (F.PROCASEID=ADPRJ03.PROJECTID)left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,CC.USERCNAME
FROM PDWFC04
LEFT JOIN ASREG005 CC ON CC.USERID=PDWFC04.OPERATOR
WHERE PDWFC04.ACTIONID='PC064RE009' AND PDWFC04.CURRENTSTATUS='A05'
) G ON (G.PROCASEID=ADPRJ03.PROJECTID)LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,DD.USERCNAME,FF.ORGANIZATIONNAME
FROM PDWFC04
LEFT JOIN ASREG005 DD ON DD.USERID=PDWFC04.OPERATOR
LEFT JOIN (select POSTID,ORGANIZATIONID FROM ASREG004) EE ON EE.POSTID=DD.POSTID
LEFT JOIN ASREG001 FF ON FF.ORGANIZATIONID=EE.ORGANIZATIONID
WHERE PDWFC04.ACTIONID='PC064RE007' AND PDWFC04.CURRENTSTATUS='A05' )
H ON (H.PROCASEID=ADPRJ03.PROJECTID)
ORDER BY "项目名称","计划完成时间"ps:另外,也想知道,那些是我们写查询时须注意的呢?(想避免这种运行时的无响应情况发生)
SELECT adprj03.projectid AS "SYS_PROCESSCASEID", a.projectname AS "项目名称",
b.actionname AS "工作任务",
TO_DATE (adprj03.plandateofend, 'YYYY-MM-DD') AS "计划完成时间",
c.usercname AS "负责人", adprj03.taskdescription AS "主要工作内容",
d.usercname AS "填表人", TO_DATE (d.operdate, 'YYYY-MM-DD') AS "日期",
f.usercname AS "审核人(处理部)",
g.usercname AS "审核人(生产管理部)", h.organizationname AS "填表单位"
FROM adprj03 LEFT JOIN adprj70 a ON a.projectid = adprj03.projectid
LEFT JOIN pswfd07 b ON b.actionid = adprj03.assignmentid
LEFT JOIN asreg005 c ON c.userid = adprj03.executor
LEFT JOIN
(SELECT pdwfc04.procaseid, pdwfc04.actionid, aa.usercname,
pdwfc04.operdate
FROM pdwfc04 LEFT JOIN asreg005 aa ON aa.userid = pdwfc04.OPERATOR
WHERE pdwfc04.actionid = 'PC064RE007'
AND pdwfc04.currentstatus = 'A05') d
ON (d.procaseid = adprj03.projectid)
LEFT JOIN
(SELECT pdwfc04.procaseid, pdwfc04.actionid, bb.usercname
FROM pdwfc04 LEFT JOIN asreg005 bb ON bb.userid = pdwfc04.OPERATOR
WHERE pdwfc04.actionid = 'PC064RE008'
AND pdwfc04.currentstatus = 'A05') f
ON (f.procaseid = adprj03.projectid)
排版不清楚人家懒得看
建议你最多连接两次,
关于数据处理可以用decode函数或case when ... then ...else ...end 语句来代替
SELECT ADPRJ03.PROJECTID AS "SYS_PROCESSCASEID" ,A.PROJECTNAME AS "项目名称"
,B.ACTIONNAME AS "工作任务",TO_DATE(ADPRJ03.PLANDATEOFEND,'YYYY-MM-DD') AS "计划完成时间"
,getusercname( ADPRJ03.EXECUTOR ) AS "负责人"
,ADPRJ03.TASKDESCRIPTION AS "主要工作内容"
,decode(getusercname(D."OPERATOR"),null,D."OPERATOR") AS "填表人"
,TO_DATE(D.OPERDATE,'YYYY-MM-DD') AS "日期"
,decode(getusercname(F."OPERATOR"),null,F."OPERATOR") AS "审核人(处理部)"
,decode(getusercname(G."OPERATOR"),null,G."OPERATOR") AS "审核人(生产管理部)"
,H.ORGANIZATIONNAME AS "填表单位"
FROM ADPRJ03
LEFT JOIN ADPRJ70 A ON A.PROJECTID=ADPRJ03.PROJECTID
LEFT JOIN PSWFD07 B ON B.ACTIONID=ADPRJ03.ASSIGNMENTID
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR",PDWFC04.OPERDATE
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05'
) D ON (D.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE010' AND PDWFC04.CURRENTSTATUS='A05') F ON (F.PROCASEID=ADPRJ03.PROJECTID)
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE011' AND PDWFC04.CURRENTSTATUS='A05'
) G ON (G.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,DD.USERCNAME,FF.ORGANIZATIONNAME
FROM PDWFC04
LEFT JOIN ASREG005 DD ON DD.USERID=PDWFC04.OPERATOR
LEFT JOIN (select POSTID,ORGANIZATIONID FROM ASREG004) EE ON EE.POSTID=DD.POSTID
LEFT JOIN ASREG001 FF ON FF.ORGANIZATIONID=EE.ORGANIZATIONID
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05' )
H ON (H.PROCASEID=ADPRJ03.PROJECTID)
ORDER BY "项目名称","计划完成时间"
但是,只有第一次的调用,也就是列 "负责人",获取到了执行结果,其他地方似乎该函数就没有调用,函数调用结果都是null.
注:函数很简单,就是查询一行记录,而且,为了确认函数是否执行,我给返回值设定了初始值.
所以,初步判断后面的几次getusercname(,)调用,就没有发生?
然后explain for
select ....
就可以看执行计划了如果用第三方工具就更简单了
1.数据库服务器配置很高.
2.查询语句中使用了部分同义词.set autotrace on;
set autotrace traceonly;SELECT ADPRJ03.PROJECTID AS "SYS_PROCESSCASEID" ,A.PROJECTNAME AS "项目名称"
,B.ACTIONNAME AS "工作任务",TO_DATE(ADPRJ03.PLANDATEOFEND,'YYYY-MM-DD') AS "计划完成时间"
,getusercname( ADPRJ03.EXECUTOR ) AS "负责人"
,ADPRJ03.TASKDESCRIPTION AS "主要工作内容"
,decode(getusercname(D."OPERATOR"),null,D."OPERATOR") AS "填表人"
,TO_DATE(D.OPERDATE,'YYYY-MM-DD') AS "日期"
,decode(getusercname(F."OPERATOR"),null,F."OPERATOR") AS "审核人(处理部)"
,decode(getusercname(G."OPERATOR"),null,G."OPERATOR") AS "审核人(生产管理部)"
,H.ORGANIZATIONNAME AS "填表单位"
FROM ADPRJ03
LEFT JOIN ADPRJ70 A ON A.PROJECTID=ADPRJ03.PROJECTID
LEFT JOIN PSWFD07 B ON B.ACTIONID=ADPRJ03.ASSIGNMENTID
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR",PDWFC04.OPERDATE
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05'
) D ON (D.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE010' AND PDWFC04.CURRENTSTATUS='A05') F ON (F.PROCASEID=ADPRJ03.PROJECTID)
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE011' AND PDWFC04.CURRENTSTATUS='A05'
) G ON (G.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,DD.USERCNAME,FF.ORGANIZATIONNAME
FROM PDWFC04
LEFT JOIN ASREG005 DD ON DD.USERID=PDWFC04.OPERATOR
LEFT JOIN (select POSTID,ORGANIZATIONID FROM ASREG004) EE ON EE.POSTID=DD.POSTID
LEFT JOIN ASREG001 FF ON FF.ORGANIZATIONID=EE.ORGANIZATIONID
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05' )
H ON (H.PROCASEID=ADPRJ03.PROJECTID)
ORDER BY "项目名称","计划完成时间";希望高人给些指导.
偶就立即结帖
,B.ACTIONNAME AS "工作任务",TO_DATE(ADPRJ03.PLANDATEOFEND,'YYYY-MM-DD') AS "计划完成时间"
,getusercname( ADPRJ03.EXECUTOR ) AS "负责人"
,ADPRJ03.TASKDESCRIPTION AS "主要工作内容"
,decode(getusercname(D."OPERATOR"),null,D."OPERATOR") AS "填表人"
,TO_DATE(D.OPERDATE,'YYYY-MM-DD') AS "日期"
,decode(getusercname(F."OPERATOR"),null,F."OPERATOR") AS "审核人(处理部)"
,decode(getusercname(G."OPERATOR"),null,G."OPERATOR") AS "审核人(生产管理部)"
,H.ORGANIZATIONNAME AS "填表单位"
FROM ADPRJ03
LEFT JOIN ADPRJ70 A ON A.PROJECTID=ADPRJ03.PROJECTID
LEFT JOIN PSWFD07 B ON B.ACTIONID=ADPRJ03.ASSIGNMENTID
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR",PDWFC04.OPERDATE
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05'
) D ON (D.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE010' AND PDWFC04.CURRENTSTATUS='A05') F ON (F.PROCASEID=ADPRJ03.PROJECTID)
left join (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,PDWFC04."OPERATOR"
FROM PDWFC04
WHERE PDWFC04.ACTIONID='PC063RE011' AND PDWFC04.CURRENTSTATUS='A05'
) G ON (G.PROCASEID=ADPRJ03.PROJECTID)
LEFT JOIN (SELECT PDWFC04.PROCASEID,PDWFC04.ACTIONID,DD.USERCNAME,FF.ORGANIZATIONNAME
FROM PDWFC04
LEFT JOIN ASREG005 DD ON DD.USERID=PDWFC04.OPERATOR
LEFT JOIN (select POSTID,ORGANIZATIONID FROM ASREG004) EE ON EE.POSTID=DD.POSTID
LEFT JOIN ASREG001 FF ON FF.ORGANIZATIONID=EE.ORGANIZATIONID
WHERE PDWFC04.ACTIONID='PC063RE009' AND PDWFC04.CURRENTSTATUS='A05' )
H ON (H.PROCASEID=ADPRJ03.PROJECTID)
ORDER BY "项目名称","计划完成时间";select * from table(dbms_xplan.display);