sql查询效率问题

bai_zzj 2013-07-11 10:25:50
我写了一条sql语句,如下:
select 
a.ID AS ID, a.P_ID AS P_ID, a.YEAR as YEAR, a.TERM as TERM, a.TEACHING_DATE AS TEACHING_DATE, a.WEEK_NO AS WEEK_NO, a.WEEK AS WEEK, a.TOPIC_HOUR AS TOPIC_HOUR, a.CHAPTER AS CHAPTER,
a.DISCIPLINE as DISCIPLINE, a.SAFETY_EDUCATION AS SAFETY_EDUCATION, a.MULTI_MEDIA as MULTI_MEDIA, a.MEMO AS MEMO,
b.CLASS_NAME as CLASS_NAME,
c.COURSE_NAME as COURSE_NAME,
d.ACADEMY_NAME as ACADEMY_NAME,
e.DEPT as DEPT, e.USERNAME as USERNAME,
f.AREA_CODE as AREA_CODE, f.AREA_NAME as AREA_NAME
, g.student_count
, g.ATTENDANCE_COUNT1
, g.ATTENDANCE_COUNT2
--,(nvl(g.student_count, 0) - nvl(g.ATTENDANCE_COUNT1, 0)) as ATTENDANCE_COUNT1
--,(nvl(g.student_count, 0) - nvl(g.ATTENDANCE_COUNT2, 0)) as ATTENDANCE_COUNT2
from T_TEACHING_LOG a
left join T_CLASSES b on b.CLASS_CODE = a.CLASS_CODE
left join T_COURSE c on c.COURSE_CODE = a.COURSE_CODE
left join t_specialty d on b.specialty_code = d.specialty_code
left join T_USER e on e.ID = a.U_ID
left join T_AREA f on f.AREA_CODE = a.AREA_CODE

left join (
select o.P_ID, o.WEEK_NO, o.WEEK, o.TOPIC_HOUR,
(select count(*) from S_STATUS a where a.class_code = o.class_code and a.state_code = '01') AS student_count,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '1' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT1,
(select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '2' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT2
from T_ATTENDANCE_MAIN o
where o.year = '2012~2013' and o.term = '第二学期'
) g on g.P_ID = a.p_Id and g.WEEK_NO = a.WEEK_NO and g.WEEK = a.WEEK and g.TOPIC_HOUR = a.TOPIC_HOUR
where a.year = '2012~2013'
and a.term = '第二学期'

这条sql语句执行时,用了一个小时也没有查询出数据;
但是如果说我将g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2(也就是代码中的第10,11行)这两行注释掉,不查询出来,就只要4秒左右就能显示结果。
请问下,这是什么原因? 难度就因为多显示视图中的2个字段,查询速度就会相差这么多?
PS:ATTENDANCE表中的数据量比较大。
...全文
110 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shy315 2013-07-11
不取这两个字段,oracle优化也许就不会去关联这个表了。 如果这两行真的不影响的话,执行计划应该几乎一模一样,最多关联描述上用词不同。
回复
bai_zzj 2013-07-11
我比较了一下2个sql语句的执行计划,好像都差不多。至于表的索引,暂时还没建。 其实这不是关键;关键在于为什么就多显示g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段后,执行效率会相差这么多? 从理论上讲,我可以将G看成一个视图, 视图里面有7个字段;当我关联查询时,从视图里面取g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段和不取这两个字段,效率应该是差不多的才对啊。 难道说,只有当我要显示出g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段时,视图G才会去统计它们的值吗?如果不显示,就不会统计它们的值。
回复
shy315 2013-07-11
select o.P_ID, o.WEEK_NO, o.WEEK, o.TOPIC_HOUR, 
    (select count(*) from S_STATUS a where a.class_code = o.class_code and a.state_code = '01') AS student_count, 
    (select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '1' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT1, 
    (select count(*) from T_ATTENDANCE a where a.M_ID = o.ID and substr(a.topic_hour,2,1) = '2' and a.ATTENDANCE in('旷课', '事假', '病假', '公假')) AS ATTENDANCE_COUNT2 
    from T_ATTENDANCE_MAIN o 
    where o.year = '2012~2013' and o.term = '第二学期'
这段不合理,大数据量表关联写在select嵌套中,会扰乱执行计划。不如先写成function试试速度。 T_ATTENDANCE.M_ID有索引的吧?
回复
陈字文 2013-07-11
你们的这个表结构设计的挺厉害的.. year = '2012~2013' 比较一下两个SQL执行计划. 优化sql ,优化索引. 或者跑个10046试一下.
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-07-11 10:25
社区公告
暂无公告