設法減少表掃描次數(luise)

點選(此處)摺疊或開啟

--場景: SQL語句非常複雜,涉及dcc_ne_log和dcc_sys_log兩張表. 表中記錄分別為305 473, 5 037 050
-- 目前的執行時間為300秒.
SELECT DISTINCT ne_state.peer_id AS peer_name, to_char(ne_state.ne_state) AS peer_state,
(CASE WHEN ne_state.ne_state = 0 THEN
to_char(0)
ELSE
(to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log
WHERE dcc_ne_log.result <> 1
AND peer_id = ne_state.peer_id
AND log_time >= TRUNC(SYSDATE)
GROUP BY(peer_id)), 0))) END) AS err_cnt,
(CASE WHEN ne_state.ne_state = 0 THEN
to_char(0)
ELSE
(to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log in_dnl
WHERE in_dnl.direction = \'recv\'
AND in_dnl.peer_id = ne_state.peer_id
AND log_time >= TRUNC(SYSDATE)), 0))) END) AS recv_cnt,
(CASE WHEN ne_state.ne_state = 0 THEN
to_char(0)
ELSE
(to_char(NVL((SELECT SUM(LENGTH) FROM dcc_ne_log in_dnl
WHERE in_dnl.direction = \'recv\'
AND in_dnl.peer_id = ne_state.peer_id
AND log_time >= TRUNC(SYSDATE)), 0))) END) AS recv_bytes,
(CASE WHEN ne_state.ne_state = 0 THEN
to_char(0)
ELSE
(to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log in_dnl
WHERE in_dnl.direction = \'send\'
AND in_dnl.peer_id = ne_state.peer_id
AND log_time >= TRUNC(SYSDATE)), 0))) END) AS send_cnt,
(CASE WHEN ne_state.ne_state = 0 THEN
to_char(0)
ELSE
(to_char(NVL((SELECT SUM(LENGTH) FROM dcc_ne_log in_dnl
WHERE in_dnl.direction = \'send\'
AND in_dnl.peer_id = ne_state.peer_id
AND log_time >= TRUNC(SYSDATE)), 0))) END) AS send_bytes
FROM dcc_ne_log, (SELECT DISTINCT dsl1.peer_id AS peer_id, NVL(ne_diconnect_info.ne_state, 1) AS ne_state
FROM dcc_sys_log dsl1, (SELECT DISTINCT dnl.peer_id AS peer_id, DECODE(action,
\'disconnect\', 0,
\'connect\', 0,
1) AS ne_state
FROM dcc_sys_log dsl, dcc_ne_log dnl
WHERE dsl.peer_id = dnl.peer_id
AND ((dsl.action = \'disconnect\' AND dsl.cause = \'關閉對端\') OR
(dsl.action = \'disconnect\' AND dsl.cause = \'連線主機失敗\'))
AND log_type = \'對端互動\'
AND dsl.log_time = (SELECT MAX(log_time) FROM dcc_sys_log
WHERE peer_id = dnl.peer_id
AND log_type = \'對端互動\')) ne_disconnect_info
WHERE dsl1.peer_id = ne_disconnect_info.peer_id(+)) ne_state
WHERE ne_state.peer_id = dcc_ne_log.peer_id(+);
--這個語句太複雜了,語句執行了多次表掃描和複雜連線.
--為構造出err_cnt, recv_cnt, recv_byte, send_cnt, send_byte,而使用了5個SQL,這是簡化的突破口
--5個欄位部分的程式碼可改寫為
SELECT peer_id, COUNT(CASE WHEN RESULT <> 1 THEN 1 END) AS err_cnt,
COUNT(CASE WHEN direction = \'recv\' THEN 1 END) AS recv_cnt,
SUM(CASE WHEN direction = \'recv\' THEN LENGTH END) AS recv_byte,
COUNT(CASE WHEN direction = \'send\' THEN 1 END) AS send_cnt,
SUM(CASE WHEN direction = \'send\' THEN LENGTH END) AS send_byte
FROM dcc_ne_log
WHERE log_time >= TRUNC(SYSDATE)
GROUP BY peer_id;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1072012/，如需轉載，請註明出處，否則將追究法律責任。

設法減少表掃描次數(luise)

相關文章