下面这个语句是这样的,tpr_network_pm_day a, tpr_network_cm_orig b, tpr_network_fm_day c这3张表是以天为单位每天一条记录每个表都有一个名称为begin_time的日期型字段(格式为yyyy-mm-dd),并且都建有索引要求要查出三张表中从今天开始往前一个月的每天的数据查看执行计划,发现这3张表的索引一个都没有用到开始以为是左联接的问题,后来去掉左联接(+)还是不行但是单表查询的时候可以用到索引请各位仁兄知道迷津,谢谢!SELECT D.DAYTIME BEGIN_TIME,
NVL(a.radio_recv_bytes, 0) AS radio_recv_bytes,
NVL(a.radio_send_bytes, 0) AS radio_send_bytes,
NVL(a.radio_bytes, 0) AS radio_bytes,
NVL(a.core_recv_bytes, 0) AS core_recv_bytes,
NVL(a.core_send_bytes, 0) AS core_send_bytes,
NVL(a.core_bytes, 0) AS core_bytes,
NVL(a.ethernet_send_all_bytes, 0) AS ethernet_send_all_bytes,
NVL(a.ethernet_recv_all_bytes, 0) AS ethernet_recv_all_bytes,
NVL(a.ethernet_all_bytes, 0) AS ethernet_all_bytes,
NVL(a.user_association_num, 0) AS user_association_num,
NVL(a.user_association_fail_num, 0) AS user_association_fail_num,
NVL(a.user_reassociation_num, 0) AS user_reassociation_num,
NVL(a.total_use_time, 0) AS total_use_time,
NVL(a.connect_count, 0) AS connect_count,
NVL(a.abandon_frames_rate, 0) AS abandon_frames_rate,
NVL(a.abandon_pks_rate, 0) AS abandon_pks_rate,
NVL(b.ac_num, 0) AS ac_num,
NVL(b.ap_num, 0) AS ap_num,
NVL(b.hp_num, 0) AS hp_num,
NVL(c.available_rate, 0) AS available_rate,
NVL(c.ac_num, 0) AS ac_num_of_alarm,
NVL(c.ap_num, 0) AS ap_num_of_alarm,
NVL(c.ap_alarm_num, 0) AS ap_alarm_num,
NVL(c.ac_alarm_num, 0) AS ac_alarm_num
FROM tpr_network_pm_day a,
tpr_network_cm_orig b,
tpr_network_fm_day c,
(SELECT TRUNC(SYSDATE) - ROWNUM + 1 AS DAYTIME FROM DUAL
CONNECT BY ROWNUM <= SYSDATE - ADD_MONTHS(SYSDATE, -1)) D
WHERE D.DAYTIME = a.begin_time(+)
AND D.DAYTIME = b.begin_time(+)
AND D.DAYTIME = c.begin_time(+)
NVL(a.radio_recv_bytes, 0) AS radio_recv_bytes,
NVL(a.radio_send_bytes, 0) AS radio_send_bytes,
NVL(a.radio_bytes, 0) AS radio_bytes,
NVL(a.core_recv_bytes, 0) AS core_recv_bytes,
NVL(a.core_send_bytes, 0) AS core_send_bytes,
NVL(a.core_bytes, 0) AS core_bytes,
NVL(a.ethernet_send_all_bytes, 0) AS ethernet_send_all_bytes,
NVL(a.ethernet_recv_all_bytes, 0) AS ethernet_recv_all_bytes,
NVL(a.ethernet_all_bytes, 0) AS ethernet_all_bytes,
NVL(a.user_association_num, 0) AS user_association_num,
NVL(a.user_association_fail_num, 0) AS user_association_fail_num,
NVL(a.user_reassociation_num, 0) AS user_reassociation_num,
NVL(a.total_use_time, 0) AS total_use_time,
NVL(a.connect_count, 0) AS connect_count,
NVL(a.abandon_frames_rate, 0) AS abandon_frames_rate,
NVL(a.abandon_pks_rate, 0) AS abandon_pks_rate,
NVL(b.ac_num, 0) AS ac_num,
NVL(b.ap_num, 0) AS ap_num,
NVL(b.hp_num, 0) AS hp_num,
NVL(c.available_rate, 0) AS available_rate,
NVL(c.ac_num, 0) AS ac_num_of_alarm,
NVL(c.ap_num, 0) AS ap_num_of_alarm,
NVL(c.ap_alarm_num, 0) AS ap_alarm_num,
NVL(c.ac_alarm_num, 0) AS ac_alarm_num
FROM tpr_network_pm_day a,
tpr_network_cm_orig b,
tpr_network_fm_day c,
(SELECT TRUNC(SYSDATE) - ROWNUM + 1 AS DAYTIME FROM DUAL
CONNECT BY ROWNUM <= SYSDATE - ADD_MONTHS(SYSDATE, -1)) D
WHERE D.DAYTIME = a.begin_time(+)
AND D.DAYTIME = b.begin_time(+)
AND D.DAYTIME = c.begin_time(+)
解决方案 »
- 求助几个问题
- 【求救】oracle 存储过程 报错:check compiler log
- 请问,job有命名规则吗?
- oracle 执行truncate操作后,block块中数据有没有被清空
- 关于 exists 的问题?
- 紧急求助:怎么在SQL Server中建立dts包转换数据到oracle
- 一个用参数传入SQL查询条件中where 后的所有内容的过程,不知为什么报错。就差一点就完成了,就是过不去。
- 100分求一个SQL语句,关于两个表的全连接
- 请教,关于Oracle中的图片数据问题
- 用oracle查询数据表t时,为什么加上hometown='浙江省'后,查询速度慢了5分钟呢?详情请看:
- 如何对session里无sid的表解锁?
- oracle如何读取表或物化视图的properties
from d,a,b,c
再看执行计划,看是否走nl且用上了索引。没有的话再加nl和index hint.