BETWEEN 子句与 <= AND >=

2021-12-30 00:00:00 performance sql oracle11g oracle oracle10g

使用 BETWEEN 子句或使用 <= AND >= 比较之间是否存在性能差异?

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

即这两个查询:

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

...和

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

在本例中，年份列是带有索引的 VARCHAR2(4).

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

推荐答案

两个示例查询之间没有性能差异，因为 BETWEEN 只是表达 inclusive 的一种简写方式i> 范围比较.当 Oracle 解析 BETWEEN 条件时，它会自动扩展为单独的比较子句:

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:

例如

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

...会自动变成:

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

相关文章