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 = '第二学期'