- UID
- 34698
- 阅读权限
- 20
- 帖子
- 1338
- 精华贴数
- 0
- 技术排名
- 1735
- 技术积分
- 1457
- 社区排名
- 1501
- 社区积分
- 935
- 注册时间
- 2003-1-15
- 精华贴数
- 0
- 技术积分
- 1457
- 社区积分
- 935
- 注册时间
- 2003-1-15
- 论坛徽章:
- 28
|
最初由 lfree 发布
[B]你能将这个的执行计划贴上来吗?
我估计你没有建立CARESULT的TEACHINGCLASSID
[/B]
远程没有连接上,下面执行计划是在开发用服务器上执行的用户是昨天从现场exp/imp回来的
[php]
SQL> set autotrace traceonly
SQL> select weekno ,coursename,classno,weekdayno,B.name,EMPLOYEENAME
2 from COURSE_TEACHER C,caresult A ,classroom B
3 where A.CLASSROOMID = B.CLASSROOMID and C.TEACHINGCLASSID = A.TEACHINGCLA
SSID
4 and A.teachingclassid in (select teachingclassid from teachingtask
5 where termid='t010') ;
已选择43958行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF 'CARESULT'
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'CLASSROOM'
7 6 INDEX (UNIQUE SCAN) OF 'PK_CLASSROOM' (UNIQUE)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'TEACHINGTASK'
9 8 INDEX (UNIQUE SCAN) OF 'PK_TEACHINGTASK' (UNIQUE)
10 1 SORT (JOIN)
11 10 TABLE ACCESS (FULL) OF 'COURSE_TEACHER'
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
224180 consistent gets
1477 physical reads
0 redo size
1048164 bytes sent via SQL*Net to client
32733 bytes received via SQL*Net from client
2932 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
43958 rows processed
=============================================================================================================
SQL> SELECT WEEKNO FROM CARESULT WHERE WEEKDAYNO='2' AND CLASSNO='1' and TEACHIN
GCLASSID='CCf65fbad8'
2 order by WEEKNO;
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (FULL SCAN) OF 'PK_CARESULT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
251 consistent gets
0 physical reads
0 redo size
473 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
=========================================================================================
SQL> select distinct t.GRADEYEAR,t.TERMID executeterm,te.termname,t.courseid,c.C
OURSEKINDCODE, t.studytypecode,decode(t.studytypecode,'2','专业选修课','3','公共
选修课') studytypecodename,
2 t.PERIODNUM1,t.PERIODNUM2,t.PERIODNUM3,t.PERIODNUM4,t.teachingclassid, t.te
achingclassname, t.studentnum,t.TERMNO,t.COMMENTS, c.coursename,
3 c.COURSENO,c.DEPARTMENTID,c.STAFFROOMID,c.ISOTHERLANG,c.CREDITHOURNUM,c.TE
ACHMETHODCODE,c.REFBOOKNAME,c.PRECOURSENAME,
4 decode(c.exammethodcode,'1','笔试','2','口试','3','考查','4','操作','其他')
exammethodcode,c.brief,C.COMMENTS COURSEcomments,
5 (select count(distinct studentno) from course_student where TEACHINGCLAS
SID=t.TEACHINGCLASSID)rr,ct.EMPLOYEENAME
6 from teachingtask t,course c,Course_Teacher ct,term te where c.COURSEID=t.
COURSEID and t.TERMID=te.TERMID and ct.TEACHINGCLASSID=t.TEACHINGCLASSID and t
.STUDYTYPECODE='3'
7 and t.TERMID='t010' ;
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF 'COURSE_TEACHER'
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'TEACHINGTASK'
7 6 INDEX (UNIQUE SCAN) OF 'PK_TEACHINGTASK' (UNIQUE
)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'TERM'
9 8 INDEX (UNIQUE SCAN) OF 'PK_TERM' (UNIQUE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'COURSE'
11 10 INDEX (UNIQUE SCAN) OF 'PK_COURSE' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19497 consistent gets
0 physical reads
0 redo size
2715 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
10 rows processed
[/php] |
|