执行计划一样，结果一样，速度差几十倍。请教

显示全部楼层 · 2007-10-20 08:38:44

最近发现一个可疑的问题。两个句子执行计划一样，结果一样，查询速度差几十倍。第一句要58秒，第2句只要0.313秒。不知原因。下面是其autotrace内容。
SQL> set autotrace on
SQL> select count(*) count from fm_alhistory aa, fm_alhistory bb
2where bb.occur_Time=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
4and aa.occur_Time>=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
5and aa.occur_time select count(*) count from fm_alhistory aa, fm_alhistory bb
2where bb.occur_Time=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
4and aa.occur_Time=bb.occur_time
5and 1=1
6and bb.severity!=5
7and aa.MDN in(535206,440053,364592)
8and aa.severity = 5
9and aa.mdn=bb.mdn
10and aa.alnum1=bb.alnum1
11;
COUNT
----------
182

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=44)
10 SORT (AGGREGATE)
21 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FM_ALHISTORY' (C

ost=2 Card=1 Bytes=22)
32 NESTED LOOPS (Cost=5 Card=1 Bytes=44)
43 PARTITION RANGE (ITERATOR)
54 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FM_ALHISTO

RY' (Cost=3 Card=1 Bytes=22)
65
INDEX (RANGE SCAN) OF 'FM_ALHISTORY#OCCUR_TIME'

(NON-UNIQUE) (Cost=2 Card=4)
73 PARTITION RANGE (ITERATOR)
87 INDEX (RANGE SCAN) OF 'FM_ALHISTORY#OCCUR_TIME' (N

ON-UNIQUE) (Cost=1 Card=1)

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

0recursive calls

0db block gets
54743consistent gets

0physical reads

0redo size
489bytes sent via SQL*Net to client
655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

千问 · 2007-10-20 08:38:44

两句的差别就是
where bb.occur_Time=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
4and aa.occur_Time>=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
5and aa.occur_time=to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')
4and aa.occur_Time=bb.occur_time
注意，aa和bb其实是同一个表。请大家帮忙看看，速度的差别我实验过好几次。

千问 · 2007-10-20 08:38:44

执行计划有点儿区别：
ON-UNIQUE) (Cost=1 Card=4)
ON-UNIQUE) (Cost=1 Card=1)
关注中...
[ 本帖最后由 fanyu_dy 于 2007-11-26 17:32 编辑 ]

千问 · 2007-10-20 08:38:44

应该是看错了，执行计划不太可能一样。

千问 · 2007-10-20 08:38:44

原帖由 fanyu_dy 于 2007-11-26 17:29 发表
执行计划有点儿区别：
ON-UNIQUE) (Cost=1 Card=4)
ON-UNIQUE) (Cost=1 Card=1)
关注中...
对，这个不一样。这就是原因吗

千问 · 2007-10-20 08:38:44

一个迪卡尔基，一个是时间关联
怎么记录数还一样呢？

千问 · 2007-10-20 08:38:44

关注一下

千问 · 2007-10-20 08:38:44

顶起来啊

千问 · 2007-10-20 08:38:44

你看一下下面的 SQL，应该能说明问题：
[PHP]
select count(*) cnt from fm_alhistory aa, fm_alhistory bb
where aa.occur_Time between to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')

and to_date('2007-11-25 14:43:00','yyyy-mm-dd hh24:mi:ss')
and bb.occur_Time between to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')

and to_date('2007-11-25 14:43:00','yyyy-mm-dd hh24:mi:ss') ;
[/PHP]
与
[PHP]
select count(*) cnt from fm_alhistory aa, fm_alhistory bb
where aa.occur_Time between to_date('2007-11-24 14:43:00','yyyy-mm-dd hh24:mi:ss')

and to_date('2007-11-25 14:43:00','yyyy-mm-dd hh24:mi:ss')
and aa.occur_Time = bb.occur_Time ;
[/PHP]