设法减少表扫描次数(luise)

最新推荐文章于 2019-09-15 13:21:12 发布

cmff98425

最新推荐文章于 2019-09-15 13:21:12 发布

阅读量88

点赞数

点击(此处)折叠或打开

--场景: 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;