Oracle SQL 优化全过程

来源：互联网发布：Linux安装telnet yum 编辑：程序博客网时间：2024/06/03 21:35

前两天写了一个课程结束时间的统计，要求是有课程所在院系、学生所在院系、课程相关信息以及课程结束时间等信息，其中课程结束时间是以星期做单位，没有太多思考，我很简单地写出了查询的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;

0 0

Linux安装telnet yum

原创粉丝点击

热门问题 老师的惩罚人脸识别我在镇武司摸鱼那些年重生之率土为王我在大康的咸鱼生活盘龙之生命进化天生仙种凡人之先天五行春回大明朝姑娘不必设防，我是瞎子日杂风发型日杂风五金日杂日杂百货日杂店五金日杂店日杂商店日杂五金店日杂店装修日杂用品明细清单日杂用品清单及价格日杂百货进货渠道新手开五金日杂店进货卖五金日杂利润怎么样成都日杂百货批发市场五金土产日杂进货清单五金日杂批发市场北京日杂百货批发市场广州日杂百货批发市场五金日杂店进货清单日杂百货包括什么土产日杂包括什么天平架日杂批发市场日杂包括什么丁酉日柱甲申日柱上等日柱丁未日柱甲子日柱丁丑日柱丙子日柱辛未日柱甲辰日柱乙卯日柱乙丑日柱辛丑日柱丙辰日柱日柱推算表丙申日柱辛亥日柱辛巳日柱