前两天写了一个课程结束时间的统计,要求是有课程所在院系、学生所在院系、课程相关信息以及课程结束时间等信息,其中课程结束时间是以星期做单位,没有太多思考,我很简单地写出了查询的SQL,但是执行效率太低,现将整个SQL的优化过程称述如下:
优化前:
视图一:
sql 代码
- CREATE OR REPLACE VIEW V_A_WEIXM_SUB_BAD
- (TEACHINGCLASSID, TEACHINGCLASSNAME, TERMID, COURSENAME, COURSENO,
- TOTALPERIODNUM, CLASSNO, TERMNAME, DKNAME, STNAME,
- DSNAME, NUM, TEACHER1, TEACHER2, TEACHER3,
- BEGINWEEK, REST, WEEKDAY)
- AS
- select
- distinct a.teachingclassid,a.teachingclassname,a.termid,b.coursename,b.COURSENO,b.TOTALPERIODNUM,
- c.CLASSNO,l.TERMNAME,e.name dkname,h.NAME stname,j.NAME dsname,
- g.num,k.TEACHER1,k.TEACHER2,k.TEACHER3,a.BEGINWEEK,
- (select max(weekno) from caresult where caresult.teachingclassid=a.teachingclassid ) rest,
- decode(
- (
- select weekdayno from
- (
- select distinct max(s.weekdayno) weekdayno,s.teachingclassid from
- (
- select distinct max(weekno) weekno,teachingclassid from caresult group by teachingclassid
- ) m,caresult s
- where m.teachingclassid=s.teachingclassid and m.weekno=s.weekno group by s.teachingclassid
- ) where teachingclassid=a.teachingclassid
- )
- ,1,'星期一',2,'星期二',3,'星期三',4,'星期四',5,'星期五',6,'星期六',0,'星期日') WEEKDAY
- from
- teachingtask a,course b,course_classes c,
- (select distinct classno,count(classno) num from students group by classno)g,
- staffroom h,classes i,
- TEACHERSETTING k,term l,department j,department e
- where
- a.courseid=b.courseid and a.teachingclassid=c.teachingclassid and
- b.departmentid=e.departmentid and b.STAFFROOMID=h.STAFFROOMID and
- c.CLASSNO=i.CLASSNO and i.DEPARTMENTID=j.DEPARTMENTID and
- g.CLASSNO=c.CLASSNO and k.teachingclassid(+)=a.teachingclassid and
- a.TERMID=l.TERMID
- and a.TERMID='t012' order by e.name,h.NAME,b.COURSENO,b.coursename,teachingclassname;
优化后:
视图二:
sql 代码
- CREATE OR REPLACE VIEW V_A_WEIXM_SUB
- (TEACHINGCLASSID, TEACHINGCLASSNAME, TERMID, COURSENAME, COURSENO,
- TOTALPERIODNUM, CLASSNO, TERMNAME, DKNAME, STNAME,
- DSNAME, NUM, TEACHER1, TEACHER2, TEACHER3,
- BEGINWEEK, REST, WEEKDAY)
- AS
- select
- distinct a.teachingclassid,a.teachingclassname,a.termid,b.coursename,b.COURSENO,b.TOTALPERIODNUM,
- c.CLASSNO,l.TERMNAME,e.name dkname,h.NAME stname,j.NAME dsname,
- g.num,k.TEACHER1,k.TEACHER2,k.TEACHER3,a.BEGINWEEK,
- m.weekno rest,
- decode(m.weekdayno,1,'星期一',2,'星期二',3,'星期三',4,'星期四',5,'星期五',6,'星期六',0,'星期日') WEEKDAY
- from
- teachingtask a,course b,course_classes c,
- (select distinct classno,count(classno) num from students group by classno)g,
- staffroom h,classes i,
- (
- select
- a.weekdayno,a.weekno,b.teachingclassid,a.trueflag from
- (select weekdayno,weekno,teachingclassid,(to_number(weekno)*10+to_number(weekdayno)) trueflag from caresult) a,
- (select teachingclassid,max(to_number(weekno)*10+to_number(weekdayno)) checkflag from caresult group by teachingclassid)b
- where b.teachingclassid=a.teachingclassid and b.checkflag=a.trueflag
- ) m,
- TEACHERSETTING k,term l,department j,department e
- where
- a.courseid=b.courseid and a.teachingclassid=c.teachingclassid and
- b.departmentid=e.departmentid and b.STAFFROOMID=h.STAFFROOMID and
- c.CLASSNO=i.CLASSNO and i.DEPARTMENTID=j.DEPARTMENTID and
- g.CLASSNO=c.CLASSNO and k.teachingclassid(+)=a.teachingclassid and
- a.TERMID=l.TERMID and m.teachingclassid(+)=a.teachingclassid
- and a.TERMID='t012' order by e.name,h.NAME,b.COURSENO,b.coursename,teachingclassname;