同样的sql只有查询的日期不同但执行速度相差很多

SELECT message,status,receivername,main.registno,sendername,to_char(send_time) as send_time,mobile,riskcode
from prplflsmssend ps left join prplflsmslog pl on pl.smssendid=ps.id, prplflmaininfo main
where trim(smstype)<>'' and pl.registno = main.registno and smstype='35'
and exists ( select pdrc.comCode from PrpDcompany pdrc where ( pdrc.upperPath like '00000000,42000000,42010000%' ) and main.comcode = pdrc.comCode )

and operatedate between to_date('2015-09-04 00:00:00') and to_date('2015-09-04 23:59:59') ; --执行速度 大约30分钟

select message,status,receivername,main.registno,sendername,to_char(send_time) as send_time,mobile,riskcode
from prplflsmssend ps left join prplflsmslog pl on pl.smssendid=ps.id, prplflmaininfo main
where trim(smstype)<>'' and pl.registno = main.registno and smstype='18'
and exists ( select pdrc.comCode from PrpDcompany pdrc where ( pdrc.upperPath like '00000000,42000000,42010000%' ) and main.comcode = pdrc.comCode )

and operatedate >= to_date('2015-09-09 00:00:00') and operatedate<= to_date('2015-09-09 23:59:59') 执行速度 几秒钟

执行查询计划，看慢到哪里了！

QUERY: (OPTIMIZATION TIMESTAMP: 09-10-2015 16:17:08)

SELECT message,status,receivername,main.registno,sendername,to_char(send_time) as send_time,mobile,riskcode
from prplflsmssend ps left join prplflsmslog pl on pl.smssendid=ps.id, prplflmaininfo main
where trim(smstype)<>'' and pl.registno = main.registno and smstype='35'
and exists ( select pdrc.comCode from PrpDcompany pdrc where ( pdrc.upperPath like '00000000,42000000,42010000%' ) and main.comcode = pdrc.comCode )

and operatedate >= to_date('2015-09-05 00:00:00') and operatedate<= to_date('2015-09-05 23:59:59')

Estimated Cost: 3112
Estimated # of Rows Returned: 4204

1) flowctrl.main: SEQUENTIAL SCAN

2) flowctrl.pdrc: INDEX PATH

    Filters: flowctrl.pdrc.upperpath LIKE '00000000,42000000,42010000%' 

(1) Index Name: flowctrl. 101_6
    Index Keys: comcode   (Serial, fragments: ALL)
    Lower Index Filter: flowctrl.main.comcode = flowctrl.pdrc.comcode 

NESTED LOOP JOIN

3) flowctrl.pl: SEQUENTIAL SCAN

    Filters: 
    Table Scan Filters: (((flowctrl.pl.smstype = '35' AND flowctrl.pl.operatedate <= datetime(2015-09-05 23:59:59.00000) year to fraction(5) ) AND flowctrl.pl.operatedate >= datetime(2015-09-05 00:00:00.00000) year to fraction(5) ) AND TRIM ( BOTH ' ' FROM flowctrl.pl.smstype ) != '' ) 

DYNAMIC HASH JOIN
Dynamic Hash Filters: flowctrl.pl.registno = flowctrl.main.registno

4) flowctrl.ps: INDEX PATH

(1) Index Name: flowctrl. 118_47
    Index Keys: id   (Serial, fragments: ALL)
    Lower Index Filter: flowctrl.pl.smssendid = flowctrl.ps.id 

NESTED LOOP JOIN

---------------华丽丽的分割线-------------

QUERY: (OPTIMIZATION TIMESTAMP: 09-10-2015 16:17:31)

SELECT message,status,receivername,main.registno,sendername,to_char(send_time) as send_time,mobile,riskcode
from prplflsmssend ps left join prplflsmslog pl on pl.smssendid=ps.id, prplflmaininfo main
where trim(smstype)<>'' and pl.registno = main.registno and smstype='35'
and exists ( select pdrc.comCode from PrpDcompany pdrc where ( pdrc.upperPath like '00000000,42000000,42010000%' ) and main.comcode = pdrc.comCode )

and operatedate >= to_date('2015-09-09 00:00:00') and operatedate<= to_date('2015-09-09 23:59:59')

Estimated Cost: 3112
Estimated # of Rows Returned: 4204

1) flowctrl.main: SEQUENTIAL SCAN

2) flowctrl.pdrc: INDEX PATH

    Filters: flowctrl.pdrc.upperpath LIKE '00000000,42000000,42010000%' 

(1) Index Name: flowctrl. 101_6
    Index Keys: comcode   (Serial, fragments: ALL)
    Lower Index Filter: flowctrl.main.comcode = flowctrl.pdrc.comcode 

NESTED LOOP JOIN

3) flowctrl.pl: SEQUENTIAL SCAN

    Filters: 
    Table Scan Filters: (((flowctrl.pl.smstype = '35' AND flowctrl.pl.operatedate <= datetime(2015-09-09 23:59:59.00000) year to fraction(5) ) AND flowctrl.pl.operatedate >= datetime(2015-09-09 00:00:00.00000) year to fraction(5) ) AND TRIM ( BOTH ' ' FROM flowctrl.pl.smstype ) != '' ) 

DYNAMIC HASH JOIN
Dynamic Hash Filters: flowctrl.pl.registno = flowctrl.main.registno

4) flowctrl.ps: INDEX PATH

(1) Index Name: flowctrl. 118_47
    Index Keys: id   (Serial, fragments: ALL)
    Lower Index Filter: flowctrl.pl.smssendid = flowctrl.ps.id 

NESTED LOOP JOIN

两个执行计划都一样啊 看不太懂！

between and也被解析成了 >= <= 了。 你先用 between and 了 应该是数据库服务器缓存了结果，所以在测试 >= <=就快了。 你可以试下执行顺序颠倒下，看看是执行时间！

这样你把第一条sql执行一遍的时间和第二遍看看有多大差距。