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秒左右.
uj5u.com熱心網友回復：
我往下翻了10分鐘終于翻到底了，就不能寫簡單點么？就不能不用標量子查詢么？
你這個先想辦法把標量子查詢改成表關聯查詢
uj5u.com熱心網友回復：
忒長了，看不下去了uj5u.com熱心網友回復：
too long to investigate
uj5u.com熱心網友回復：
還有一堆外鏈接，這SQL陳述句快就怪了，內部子查詢也太多了，改成存盤程序吧，如此復雜的邏輯，恐怕資料庫設計也有問題，考慮一些反規范化設計的方法，以提高效率。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103498.html
標籤：開發