SQL等价改写优化案例精选资料.docVIP

SQL 等价改写优化案例精选一．统一天下解释为：巧用各类技巧合并代码，减少表扫描次数。案例 1 注重 COUNT(CASE WHEN)之类的合并技巧select distinct ne_state.peer_id peer_name,to_char(ne_state.ne_state) peer_state,(casewhen ne_state.ne_state = 0 thento_char(0)else(select distinct to_char(nvl(ne_active.active, 0))from dcc_sys_log,(select peer_id,decode(action,active,1,de-active,0,0) active,max(log_time)from dcc_sys_logwhere action = activeor action = de-activegroup by (peer_id, action)) ne_activewhere dcc_sys_log.peer_id = ne_active.peer_id(+)and dcc_sys_log.peer_id = ne_state.peer_id)end) peer_active,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl((select count(*)from dcc_ne_logwhere dcc_ne_log.result 1and peer_id = ne_state.peer_idand log_time betweentrunc(sysdate) and sysdategroup by (peer_id)),0)))end) err_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl((select count(*)from dcc_ne_log in_dnlwhere in_dnl.direction = recvand in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)))end) recv_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl((select sum(length)from dcc_ne_log in_dnlwhere in_dnl.direction = recvand in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)))end) recv_byte,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl((select count(*)from dcc_ne_log in_dnlwhere in_dnl.direction = sendand in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)))end) send_cnt,(casewhen ne_state.ne_state = 0 thento_char(0)else(to_char(nvl((select sum(length)from dcc_ne_log in_dnlwhere in_dnl.direction = sendand in_dnl.peer_id =ne_state.peer_idand log_time betweentrunc(sysdate) and sysdate),0)))end) send_bytefrom dcc_ne_log,(select distinct dsl1.peer_id peer_id,nvl(ne_disconnect_info.ne_state, 1) ne_statefrom dcc_sys_log dsl1,(select distinct dnl.peer_id peer_id,decode(action,disconnect,0