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

"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 <="

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html