时间列条件的问题

显示全部楼层 · 2006-4-17 13:46:34

对于这样的一个SQL语句

select c.classesholding,

c.classtype,
...
... (中间省略)
where h.datestamp = trunc(sysdate - 30 - 3)

and m.starttime >= trunc(sysdate - (30 + 3))

and m.starttime = trunc(to_date('20070501','yyyymmdd') - (30 + 3))

and m.starttimespool off
[/COLOR][/FONT]

千问 · 2006-4-17 13:46:34

对于转化时间的情况

Execution Plan
----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=967 Card=15 Bytes=93

0)

10 HASH JOIN (Cost=967 Card=15 Bytes=930)

21 VIEW (Cost=430 Card=1 Bytes=44)

32 SORT (GROUP BY) (Cost=430 Card=1 Bytes=502)

43 HASH JOIN (Cost=428 Card=1 Bytes=502)

54 TABLE ACCESS (BY INDEX ROWID) OF 'R_

REPORT' (Cost=1 Card=1 Bytes=462)

65
INDEX (RANGE SCAN) OF 'IDX_REPORT_TIME

' (NON-UNIQUE) (Cost=2 Card=1)

74 VIEW OF 'T_COMPLETEDCLASS_V' (Cost=427 Card=

15 Bytes=600)

87
SORT (UNIQUE) (Cost=427 Card=15 Bytes=1132)

98
UNION-ALL

109
HASH JOIN (Cost=334 Card=14 Bytes=1036)

11 10
TABLE ACCESS (BY INDEX ROWID) OF 'T_

OCR' (Cost=2 Card=2 Bytes=38)

12 11
NESTED LOOPS (Cost=323 Card=141 Bytes=49

35)

13 12
TABLE ACCESS (FULL) OF 'T_CLASSS

CHEDULE' (Cost=149 Card=87 Bytes=1392)

14 12
INDEX (RANGE SCAN) OF 'T_ONLINEC

LASSROSTEN2' (NON-UNIQUE) (Cost=1 Card=10)

15 10
TABLE ACCESS (BY INDEX ROWID) OF 'R_HO

STINFO' (Cost=10 Card=1918 Bytes=74802)

16 15
INDEX (RANGE SCAN) OF 'IDX_HOSTINFO_HOST

ID' (NON-UNIQUE) (Cost=2 Card=38)

179
TABLE ACCESS (BY INDEX ROWID) OF 'R_HOST

INFO' (Cost=2 Card=1 Bytes=39)

18 17
NESTED LOOPS (Cost=89 Card=1 Bytes=96)

19 18
TABLE ACCESS (FULL) OF 'T_WBTSTUDE

NTPROGRESS' (Cost=87 Card=1 Bytes=57)

20 18
INDEX (RANGE SCAN) OF 'IDX_HOSTINFO_HOST

ID' (NON-UNIQUE) (Cost=1 Card=515)

211 VIEW (Cost=537 Card=1453 Bytes=26154)

22 21 SORT (GROUP BY) (Cost=537 Card=1453 Bytes=20342)

23 22 VIEW OF 'T_COMPLETEDCLASS_V' (Cost=534 Card=14

53 Bytes=20342)

24 23 SORT (UNIQUE) (Cost=534 Card=1453 Bytes=109216)

25 24
UNION-ALL

26 25
HASH JOIN (Cost=425 Card=1376 Bytes=101824)

27 26
TABLE ACCESS (FULL) OF 'T_CC

E' (Cost=149 Card=87 Bytes=1392)

28 26
MERGE JOIN (Cost=274 Card=217562 Bytes=12618

596)

29 28
TABLE ACCESS (BY INDEX ROWID) OF 'R_HO

STINFO' (Cost=10 Card=767151 Bytes=29918889)

30 29
INDEX (RANGE SCAN) OF 'IDX_HOSTINFO_HOST

ID' (NON-UNIQUE) (Cost=2 Card=138087)

31 28
SORT (JOIN) (Cost=264 Card=22278 Bytes=423

282)

32 31
TABLE ACCESS (FULL) OF 'T_OCR

' (Cost=173 Card=22278 Bytes=423282)

33 25
TABLE ACCESS (BY INDEX ROWID) OF 'R_HOSTIN

FO' (Cost=2 Card=212 Bytes=8268)

34 33
NESTED LOOPS (Cost=89 Card=77 Bytes=7392)

35 34
TABLE ACCESS (FULL) OF 'T_W_

PROGRESS' (Cost=87 Card=1 Bytes=57)

36 34
INDEX (RANGE SCAN) OF 'IDX_HOSTINFO_HOSTID

' (NON-UNIQUE) (Cost=1 Card=212)

Statistics
----------------------------------------------------------

21recursive calls

0db block gets

4983345consistent gets

488668physical reads

0redo size

686bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

2rows processed
[/COLOR]
[/FONT]

千问 · 2006-4-17 13:46:34

说明，执行计划中有些表名已修改，请见谅
每个表中都有，几十或者几百万行数据

千问 · 2006-4-17 13:46:34

我比较了执行计划，每个地方的ｃｏｓｔ，ｃａｒｄ都差不多，是否使用了ｉｎｄｅｘ，或者ｆｕｌｌ　ｔａｂｌｅ　ｓｃａｎ都是一样的。
但为什么执行时间上却差了很多？