SQL如下:
SELECT * FROM
(
SELECT ROW_.*, ROWNUM ROWNUM_ FROM
(
SELECT
D.CF_NUM,
D.CF_ZXNO,
D.CF_GDSTA,
D.CF_CONTENT,
TO_CHAR( PI.START_ , 'YYYY-MM-DD HH24:MI' ) AS FORM_START ,
TO_CHAR( PI.END_ , 'YYYY-MM-DD HH24:MI' ) AS FORM_END ,
D.CF_GDTYPE,
(select sm_itname from sm_dict_item where sm_itkey=D.CF_BU2) as CF_BU2,
D.CF_ENDTIME,
case
when D.CF_GDSTA='B' OR D.CF_GDSTA='F' OR D.CF_GDSTA='C' THEN '0'
when decode(PI.END_,null,sysdate,PI.END_+0)>TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss')
then to_char(decode(PI.END_,null,sysdate,PI.END_+0)-TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss'),'fm999999990.09') else '0' end AS CF_OUTDATEDAY,
case
when D.CF_GDSTA='B' OR D.CF_GDSTA='F' OR D.CF_GDSTA='C' THEN '否'
when decode(PI.END_,null,sysdate,PI.END_+0)>TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss') then '是' else '否' end AS CF_ISOUTDATE,
D.CF_BANAME,
(select min(tr.sm_time) from sm_trace tr where tr.sm_fid=d.CF_ID and tr.sm_dept='GS_GD0001'group by tr.sm_dept)
AS cf_fd1,
(
select sm_actor
from sm_trace
where sm_fid = d.CF_ID
and sm_time= (select min(tr.sm_time) from sm_trace tr where tr.sm_fid=d.CF_ID and tr.sm_dept='GS_GD0001'group by tr.sm_dept)
)AS cf_fd2,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and tr.sm_gdsta != 'A'
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (org2.Parentcode IS NULL OR org2.Parentcode!='GS_KF0001')
)AS CF_FD3,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (org2.Parentcode IS NULL OR org2.Parentcode!='GS_KF0001')
)AS CF_FD4,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
)
)AS CF_FD5,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and tr.sm_gdsta != 'A'
and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd6,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd7,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)
)AS cf_fd8,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and tr.sm_gdsta != 'A'
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd9,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd10,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)
)AS cf_fd11,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
)AS CF_FD12,
L.SM_CALDAY,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
where tr.sm_fid = d.CF_ID
and tr.sm_dept = 'GS_GD0001'
group by tr.sm_dept
) AS cf_fd21,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag, '1 ', '2 ', org.oflag) = '2 '
OR org.city_excode = 'GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
) AS CF_FD22,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and
(
org.oflag = '3 '
and org.city_excode != 'GS_20001'
)
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
group by org.oflag
) AS cf_fd23,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and
(
org.oflag = '4 '
and org.city_excode != 'GS_20001'
)
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
group by org.oflag
) AS cf_fd24
FROM JBPM_TASKINSTANCE T
JOIN JBPM_PROCESSINSTANCE PI ON T.PROCINST_ = PI.ID_
JOIN CCFORM_DEBIT_ALL D ON D.CF_ID = PI.ID_
LEFT JOIN SM_CALLOG L ON D.CF_ID = L.SM_FID
WHERE
(
T.END_ =(SELECT MAX(END_) FROM JBPM_TASKINSTANCE WHERE PROCINST_ = D.CF_ID AND D.CF_GDSTA IN ('K'))
OR
(
T.END_ IS NULL AND T.ISOPEN_ = 1
)
OR T.END_ =(
SELECT MAX(END_) FROM JBPM_TASKINSTANCE WHERE PROCINST_ = D.CF_ID
AND D.CF_GDSTA IN ('F')
)
)
AND
(
L.SM_ID =(SELECT MAX(SM_ID) FROM SM_CALLOG WHERE SM_FID = D.CF_ID)
OR L.SM_ID IS NULL
)
ORDER BY PI.START_ DESC) ROW_ ) WHERE ROWNUM_ <= 100 AND ROWNUM_ > 0 ;
该SQL主要消耗时间是在查询CF_FD*等字段,该系列字段均需在查询的过程中对三张表进行连接,频繁的表连接导致查询时间极长,在测试环境上11条数据需要250秒左右.
SELECT * FROM
(
SELECT ROW_.*, ROWNUM ROWNUM_ FROM
(
SELECT
D.CF_NUM,
D.CF_ZXNO,
D.CF_GDSTA,
D.CF_CONTENT,
TO_CHAR( PI.START_ , 'YYYY-MM-DD HH24:MI' ) AS FORM_START ,
TO_CHAR( PI.END_ , 'YYYY-MM-DD HH24:MI' ) AS FORM_END ,
D.CF_GDTYPE,
(select sm_itname from sm_dict_item where sm_itkey=D.CF_BU2) as CF_BU2,
D.CF_ENDTIME,
case
when D.CF_GDSTA='B' OR D.CF_GDSTA='F' OR D.CF_GDSTA='C' THEN '0'
when decode(PI.END_,null,sysdate,PI.END_+0)>TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss')
then to_char(decode(PI.END_,null,sysdate,PI.END_+0)-TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss'),'fm999999990.09') else '0' end AS CF_OUTDATEDAY,
case
when D.CF_GDSTA='B' OR D.CF_GDSTA='F' OR D.CF_GDSTA='C' THEN '否'
when decode(PI.END_,null,sysdate,PI.END_+0)>TO_DATE(D.CF_ENDTIME,'YYYY-MM-DD hh24:mi:ss') then '是' else '否' end AS CF_ISOUTDATE,
D.CF_BANAME,
(select min(tr.sm_time) from sm_trace tr where tr.sm_fid=d.CF_ID and tr.sm_dept='GS_GD0001'group by tr.sm_dept)
AS cf_fd1,
(
select sm_actor
from sm_trace
where sm_fid = d.CF_ID
and sm_time= (select min(tr.sm_time) from sm_trace tr where tr.sm_fid=d.CF_ID and tr.sm_dept='GS_GD0001'group by tr.sm_dept)
)AS cf_fd2,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and tr.sm_gdsta != 'A'
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (org2.Parentcode IS NULL OR org2.Parentcode!='GS_KF0001')
)AS CF_FD3,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (org2.Parentcode IS NULL OR org2.Parentcode!='GS_KF0001')
)AS CF_FD4,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag,'1 ','2 ',org.oflag)='2 '
OR org.city_excode='GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND (
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
)
)AS CF_FD5,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and tr.sm_gdsta != 'A'
and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd6,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd7,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='3 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)
)AS cf_fd8,
(
select min(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and tr.sm_gdsta != 'A'
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd9,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)AS cf_fd10,
(
select sm_actor from sm_trace
where sm_fid = d.CF_ID
and sm_time=
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid=d.CF_ID
and
(
org.oflag='4 '
and org.city_excode!='GS_20001'
)
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
group by org.oflag
)
)AS cf_fd11,
(
select max(tr.sm_time) from sm_trace tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and org.excode!='GS_KF0001'
and org.Parentcode!='GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode!='GS_KF0001'
)
)AS CF_FD12,
L.SM_CALDAY,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
where tr.sm_fid = d.CF_ID
and tr.sm_dept = 'GS_GD0001'
group by tr.sm_dept
) AS cf_fd21,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where
(
DECODE(org.oflag, '1 ', '2 ', org.oflag) = '2 '
OR org.city_excode = 'GS_20001'
)
and tr.sm_fid = d.CF_ID
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
) AS CF_FD22,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and
(
org.oflag = '3 '
and org.city_excode != 'GS_20001'
)
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
group by org.oflag
) AS cf_fd23,
(
select min(tr.SM_OPTIME) from CCFORM_LOCKINFO_INFO tr
left join tb_sso_org org on org.excode = tr.sm_dept
left join tb_sso_org org2 on org2.excode = org.Parentcode
where tr.sm_fid = d.CF_ID
and
(
org.oflag = '4 '
and org.city_excode != 'GS_20001'
)
and org.excode != 'GS_KF0001'
and org.Parentcode != 'GS_KF0001'
AND
(
org2.Parentcode IS NULL
OR org2.Parentcode != 'GS_KF0001'
)
group by org.oflag
) AS cf_fd24
FROM JBPM_TASKINSTANCE T
JOIN JBPM_PROCESSINSTANCE PI ON T.PROCINST_ = PI.ID_
JOIN CCFORM_DEBIT_ALL D ON D.CF_ID = PI.ID_
LEFT JOIN SM_CALLOG L ON D.CF_ID = L.SM_FID
WHERE
(
T.END_ =(SELECT MAX(END_) FROM JBPM_TASKINSTANCE WHERE PROCINST_ = D.CF_ID AND D.CF_GDSTA IN ('K'))
OR
(
T.END_ IS NULL AND T.ISOPEN_ = 1
)
OR T.END_ =(
SELECT MAX(END_) FROM JBPM_TASKINSTANCE WHERE PROCINST_ = D.CF_ID
AND D.CF_GDSTA IN ('F')
)
)
AND
(
L.SM_ID =(SELECT MAX(SM_ID) FROM SM_CALLOG WHERE SM_FID = D.CF_ID)
OR L.SM_ID IS NULL
)
ORDER BY PI.START_ DESC) ROW_ ) WHERE ROWNUM_ <= 100 AND ROWNUM_ > 0 ;
该SQL主要消耗时间是在查询CF_FD*等字段,该系列字段均需在查询的过程中对三张表进行连接,频繁的表连接导致查询时间极长,在测试环境上11条数据需要250秒左右.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货