sql查询效率问题

[bai_zzj]

我写了一条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表中的数据量比较大。

[陈字文]

你们的这个表结构设计的挺厉害的.. year = '2012~2013' 比较一下两个SQL执行计划. 优化sql ,优化索引. 或者跑个10046试一下.

• 打赏
• 举报

回复 赞

[shy315]

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有索引的吧？

• 打赏
• 举报

回复 赞

[bai_zzj]

我比较了一下2个sql语句的执行计划，好像都差不多。至于表的索引，暂时还没建。 其实这不是关键；关键在于为什么就多显示g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段后，执行效率会相差这么多？ 从理论上讲，我可以将G看成一个视图， 视图里面有7个字段；当我关联查询时，从视图里面取g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段和不取这两个字段，效率应该是差不多的才对啊。 难道说，只有当我要显示出g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段时，视图G才会去统计它们的值吗？如果不显示，就不会统计它们的值。

• 打赏
• 举报

回复 赞

[shy315]

不取这两个字段，oracle优化也许就不会去关联这个表了。 如果这两行真的不影响的话，执行计划应该几乎一模一样，最多关联描述上用词不同。

• 打赏
• 举报

回复 赞