sql语句:
SELECT D.START_DATE AS startDate,
D.END_DATE AS endDate,
D.YWFW AS ywfw,
CASE
WHEN D.YWFW LIKE '3%' THEN
'发送'
ELSE
'接收'
END AS cslx,
CASE
WHEN D.YWFW IN ('31', '32', '41') THEN
G.ERROR_INFO
WHEN D.YWFW IN ('42', '43', '44', '45') THEN
'人员信息传输失败'
WHEN D.YWFW = '33' THEN
'缴费信息发送失败'
END AS bz,
'查看明细' AS mx
FROM (SELECT SWJG_DM
FROM DM_SWJG
WHERE YXBZ = 'Y'
CONNECT BY SJ_SWJG_DM = PRIOR SWJG_DM
START WITH SWJG_DM = '23505000000') F,
GF_EXEC_PROCEDURES_RECORD D,
GF_EXEC_PROCEDURES_LOG G
WHERE D.DUMP_SEQ = G.DUMP_SEQ
AND (SELECT X.NSR_SWJG_DM
FROM DJ_NSRXX X
WHERE X.NSRDZDAH = (CASE
WHEN G.NSRDZDAH IS NOT NULL THEN
G.NSRDZDAH
WHEN G.NSRDZDAH IS NULL AND G.PER_ID IS NOT NULL THEN
(SELECT NSRDZDAH FROM GF_SBGRMXXX WHERE PER_ID = G.PER_ID)
ELSE
NULL
END)) = F.SWJG_DM
AND G.ISSUCCESS = 'N'
AND D.YWFW IN ('31', '32', '41', '42', '43', '44', '45');F5执行计划:索引:
DJ_NSRXX:NSRDZDAH、NSR_SWJG_DM
GF_EXEC_PROCEDURES_RECORD:YWFW、DUMP_SEQ
GF_EXEC_PROCEDURES_LOG:NSRDZDAH、DUMP_SEQ、PER_ID
GF_SBGRMXXX:PER_ID、NSRDZDAH其中GF_EXEC_PROCEDURES_LOG、DJ_NSRXX、GF_SBGRMXXX的数据较多,均在百万以上。麻烦各位大牛帮小弟看下。
SELECT D.START_DATE AS startDate,
D.END_DATE AS endDate,
D.YWFW AS ywfw,
CASE
WHEN D.YWFW LIKE '3%' THEN
'发送'
ELSE
'接收'
END AS cslx,
CASE
WHEN D.YWFW IN ('31', '32', '41') THEN
G.ERROR_INFO
WHEN D.YWFW IN ('42', '43', '44', '45') THEN
'人员信息传输失败'
WHEN D.YWFW = '33' THEN
'缴费信息发送失败'
END AS bz,
'查看明细' AS mx
FROM (SELECT SWJG_DM
FROM DM_SWJG
WHERE YXBZ = 'Y'
CONNECT BY SJ_SWJG_DM = PRIOR SWJG_DM
START WITH SWJG_DM = '23505000000') F,
GF_EXEC_PROCEDURES_RECORD D,
GF_EXEC_PROCEDURES_LOG G
WHERE D.DUMP_SEQ = G.DUMP_SEQ
AND (SELECT X.NSR_SWJG_DM
FROM DJ_NSRXX X
WHERE X.NSRDZDAH = (CASE
WHEN G.NSRDZDAH IS NOT NULL THEN
G.NSRDZDAH
WHEN G.NSRDZDAH IS NULL AND G.PER_ID IS NOT NULL THEN
(SELECT NSRDZDAH FROM GF_SBGRMXXX WHERE PER_ID = G.PER_ID)
ELSE
NULL
END)) = F.SWJG_DM
AND G.ISSUCCESS = 'N'
AND D.YWFW IN ('31', '32', '41', '42', '43', '44', '45');F5执行计划:索引:
DJ_NSRXX:NSRDZDAH、NSR_SWJG_DM
GF_EXEC_PROCEDURES_RECORD:YWFW、DUMP_SEQ
GF_EXEC_PROCEDURES_LOG:NSRDZDAH、DUMP_SEQ、PER_ID
GF_SBGRMXXX:PER_ID、NSRDZDAH其中GF_EXEC_PROCEDURES_LOG、DJ_NSRXX、GF_SBGRMXXX的数据较多,均在百万以上。麻烦各位大牛帮小弟看下。
http://hi.baidu.com/%C9%F1%C0%B4%D6%AE%B1%CA01/album/item/c20d4c627ead4d130d33fa66.html#IMG=c03a9634c726a0f8a71e1231
http://hi.baidu.com/%C9%F1%C0%B4%D6%AE%B1%CA01/album/item/c20d4c627ead4d130d33fa66.html#IMG=c03a9634c726a0f8a71e1231