点击(此处)折叠或打开
- --场景: 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1072012/