Re: "between" vs. ">= and <="

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message
> news:ed159bde-2e5b-4c13-8f77-5700c0b8d9b1_at_o4g2000pra.googlegroups.com...
>>
>> Hi,
>>
>> a client of us requested to replace ">= and <=" with "between" with a
>> TIMESTAMP column. Unfortunately I am still waiting for his
>> justification... In the meantime I thought I do some researching.
>> Personally I cannot think of any reason why one or the other should be
>> more efficient.
>>
>> I did some simplistic testing but no difference visible. (Experimental
>> code at end.)
>>
>> I even believe that I once had found a statement saying that there is
>> no difference performance wise. Any pointers?
>>
>> Kind regards
>>
>> robert
>>
>>
>
>>
>> SQL> set autotrace traceonly
>> SQL> select *
>> 2 from t1
>> 3 where ts between systimestamp + 10000 and systimestamp + 10001
>> 4 /
>>
>>
>
>> Predicate Information (identified by operation
>> id):
>> ---------------------------------------------------
>>
>> 1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP(6)+10001)
>> 2 - filter("TS">=SYSTIMESTAMP(6)+10000 AND
>> "TS"<=SYSTIMESTAMP(6)+10001)
>>
>
>
> There is no difference - check the predicates section of
> the plan. "Between" is transformed to ">= and <="

This also confirms what I was taught. Essentially, BETWEEN is good because in many situations it makes the code more readable for developers and the intention clear, but from an execution perspective, the two give the same results.

if the code is to be maintained in-house, maybe the client just wants the changes because they feel it will make things clearer to future maintainers?

Tim

-- 
tcross (at) rapttech dot com dot au