ITPub博客

首页 > 数据库 > Oracle > 设法减少表扫描次数(luise)

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

原创 Oracle 作者:luisedalian 时间:2014-01-17 10:49:41 0 删除 编辑

点击(此处)折叠或打开

  1. --场景: SQL语句非常复杂,涉及dcc_ne_log和dcc_sys_log两张表. 表中记录分别为305 473, 5 037 050
  2. -- 目前的执行时间为300秒.

  3. SELECT DISTINCT ne_state.peer_id AS peer_name, to_char(ne_state.ne_state) AS peer_state,
  4.   (CASE WHEN ne_state.ne_state = 0 THEN
  5.      to_char(0)
  6.    ELSE
  7.      (to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log
  8.                      WHERE dcc_ne_log.result <> 1
  9.                        AND peer_id = ne_state.peer_id
  10.                        AND log_time >= TRUNC(SYSDATE)
  11.                      GROUP BY(peer_id)), 0))) END) AS err_cnt,
  12.   (CASE WHEN ne_state.ne_state = 0 THEN
  13.      to_char(0)
  14.    ELSE
  15.      (to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log in_dnl
  16.                      WHERE in_dnl.direction = \'recv\'
  17.                        AND in_dnl.peer_id = ne_state.peer_id
  18.                        AND log_time >= TRUNC(SYSDATE)), 0))) END) AS recv_cnt,
  19.                        
  20.   (CASE WHEN ne_state.ne_state = 0 THEN
  21.      to_char(0)
  22.    ELSE
  23.      (to_char(NVL((SELECT SUM(LENGTH) FROM dcc_ne_log in_dnl
  24.                      WHERE in_dnl.direction = \'recv\'
  25.                        AND in_dnl.peer_id = ne_state.peer_id
  26.                        AND log_time >= TRUNC(SYSDATE)), 0))) END) AS recv_bytes,
  27.                        
  28.   (CASE WHEN ne_state.ne_state = 0 THEN
  29.      to_char(0)
  30.    ELSE
  31.      (to_char(NVL((SELECT COUNT(*) FROM dcc_ne_log in_dnl
  32.                      WHERE in_dnl.direction = \'send\'
  33.                        AND in_dnl.peer_id = ne_state.peer_id
  34.                        AND log_time >= TRUNC(SYSDATE)), 0))) END) AS send_cnt,
  35.                        
  36.   (CASE WHEN ne_state.ne_state = 0 THEN
  37.      to_char(0)
  38.    ELSE
  39.      (to_char(NVL((SELECT SUM(LENGTH) FROM dcc_ne_log in_dnl
  40.                      WHERE in_dnl.direction = \'send\'
  41.                        AND in_dnl.peer_id = ne_state.peer_id
  42.                        AND log_time >= TRUNC(SYSDATE)), 0))) END) AS send_bytes
  43.   FROM dcc_ne_log, (SELECT DISTINCT dsl1.peer_id AS peer_id, NVL(ne_diconnect_info.ne_state, 1) AS ne_state
  44.                       FROM dcc_sys_log dsl1, (SELECT DISTINCT dnl.peer_id AS peer_id, DECODE(action,
  45.                                                                                       \'disconnect\', 0,
  46.                                                                                       \'connect\', 0,
  47.                                                                                       1) AS ne_state
  48.                                                 FROM dcc_sys_log dsl, dcc_ne_log dnl
  49.                                                 WHERE dsl.peer_id = dnl.peer_id
  50.                                                   AND ((dsl.action = \'disconnect\' AND dsl.cause = \'关闭对端\') OR
  51.                                                        (dsl.action = \'disconnect\' AND dsl.cause = \'连接主机失败\'))
  52.                                                   AND log_type = \'对端交互\'
  53.                                                   AND dsl.log_time = (SELECT MAX(log_time) FROM dcc_sys_log
  54.                                                                         WHERE peer_id = dnl.peer_id
  55.                                                                           AND log_type = \'对端交互\')) ne_disconnect_info
  56.                       WHERE dsl1.peer_id = ne_disconnect_info.peer_id(+)) ne_state
  57.   WHERE ne_state.peer_id = dcc_ne_log.peer_id(+);

  58. --这个语句太复杂了,语句执行了多次表扫描和复杂连接.
  59. --为构造出err_cnt, recv_cnt, recv_byte, send_cnt, send_byte,而使用了5个SQL,这是简化的突破口

  60. --5个字段部分的代码可改写为
  61. SELECT peer_id, COUNT(CASE WHEN RESULT <> 1 THEN 1 END) AS err_cnt,
  62.                 COUNT(CASE WHEN direction = \'recv\' THEN 1 END) AS recv_cnt,
  63.                 SUM(CASE WHEN direction = \'recv\' THEN LENGTH END) AS recv_byte,
  64.                 COUNT(CASE WHEN direction = \'send\' THEN 1 END) AS send_cnt,
  65.                 SUM(CASE WHEN direction = \'send\' THEN LENGTH END) AS send_byte
  66.   FROM dcc_ne_log
  67.   WHERE log_time >= TRUNC(SYSDATE)
  68.   GROUP BY peer_id;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072012/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5641733