我写了一条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表中的数据量比较大。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表中的数据量比较大。SQL
(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有索引的吧?
其实这不是关键;关键在于为什么就多显示g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段后,执行效率会相差这么多?
从理论上讲,我可以将G看成一个视图, 视图里面有7个字段;当我关联查询时,从视图里面取g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段和不取这两个字段,效率应该是差不多的才对啊。
难道说,只有当我要显示出g.ATTENDANCE_COUNT1,g.ATTENDANCE_COUNT2这两个字段时,视图G才会去统计它们的值吗?如果不显示,就不会统计它们的值。
如果这两行真的不影响的话,执行计划应该几乎一模一样,最多关联描述上用词不同。