sqlperformanceoracle-databaseoracle10goracle11g

BETWEEN clause versus <= AND >=

Is there a performance difference between using a BETWEEN clause or using <= AND >= comparisons?

i.e. these two queries:

SELECT *  
  FROM table  
 WHERE year BETWEEN '2005' AND '2010';

...and

SELECT *  
  FROM table  
 WHERE year >= '2005' AND year <= '2010';

In this example, the year column is VARCHAR2(4) with an index on it.

Solution

  • There is no performance difference between the two example queries because BETWEEN is simply a shorthand way of expressing an inclusive range comparison. When Oracle parses the BETWEEN condition it will automatically expand out into separate comparison clauses:

    ex.

    SELECT *  
  FROM table
 WHERE column BETWEEN :lower_bound AND :upper_bound

    ...will automatically become:

    SELECT *  
  FROM table
 WHERE :lower_bound <= column
   AND :upper_bound >= column

    This behavior can be verified by examining the explain plans. In this case, both queries will produce the same explain plan, and both will show the same expression used to filter the results:

    Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("YEAR"<='2010' AND "YEAR">='2005')