CODEWITHSUNDEEP
sqlperformanceoracle-databaseoracle10goracle11g
wweicker
wweicker

Reputation: 4963

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.

Upvotes: 48

Views: 87160

Answers (8)

luis.espinal
luis.espinal

Reputation: 10529

You better check your execution plans because there can be some weird edge cases where BETWEEN can have a different execution plan from the standard >= and <= combination.

For example, this blog post shows an explain plan difference when using partitioning in Oracle 10g. However, I was unable to reproduce the issue in 19c.

Caveat emptor obviously. But since execution plans can change over time and I really do not have an appetite to test such things, I rather not use BETWEEN at all.

Sometimes less choice is better.

Upvotes: 1

wweicker
wweicker

Reputation: 4963

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')

Upvotes: 26

要你命三千
要你命三千

Reputation: 59

run1 "X>=Y AND X<=Z"

run2 "X BETWEEN Y AND Z"

I get one Plan hash value when I run explain plan twice. But Tom's runStats_pkg get diffent result:

Run1 ran in 1 cpu hsecs
Run2 ran in 1 cpu hsecs
run 1 ran in 100% of the time

Name                      Run1    Run2        Diff
STAT...recursive calls          12      13       1
STAT...CPU used by this sessio       2       3       1
STAT...physical read total IO        0       1       1
STAT...consistent gets          18      19       1
...
...
LATCH.row cache objects         44,375   1,121     -43,254
LATCH.cache buffers chains      68,814   1,397     -67,417
STAT...logical read bytes from     655,360     573,440     -81,920
STAT...session uga memory max      123,512       0    -123,512
STAT...session pga memory      262,144  65,536    -196,608
STAT...session pga memory max      262,144  65,536    -196,608
STAT...session uga memory     -327,440  65,488     392,928

Run1 latches total versus runs -- difference and pct
Run1        Run2    Diff       Pct
203,927      28,673    -175,254    711.22%

Upvotes: 0

Benoit
Benoit

Reputation: 79185

Actually it depends on your DBMS engine.

Some database management systems will compute twice your expression (once for each comparison), and only once when you use BETWEEN.

Actually if the expression can have a non-deterministic result BETWEEN will have a different behaviour, compare the following in SQLite:

WHERE RANDOM() BETWEEN x AND y -- one random value generated

WHERE RANDOM() >= x AND RANDOM() <= y -- two distinct random values generated

This can be very time consuming if your expression is (for example) a subquery.

Upvotes: 6

Lukas Eder
Lukas Eder

Reputation: 220902

It may be worth considering the SQL standard for this (although this might not correspond to all implementations, even if it should):

Format

<between predicate> ::=
  <row value constructor> [ NOT ] BETWEEN
    <row value constructor> AND <row value constructor>

Syntax Rules

[...]

6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

Having said so, there is no difference in behaviour, although for complex X, there may be a difference in parsing time, as mentioned by Benoit here

Found in http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425391

There is no difference.

Note that BETWEEN is always inclusive and sensitive to the order of the arguments.

BETWEEN '2010' AND '2005' will never be TRUE.

Upvotes: 35

FolksLord
FolksLord

Reputation: 1000

It should be the same.

Good database engine will generate same plan for that expression.

Upvotes: 1

tbone
tbone

Reputation: 15473

When in doubt (for Oracle anyway), run an explain plan and you'll see what the optimizer wants to do. This would apply to most questions about "is there a performance difference between ...". Of course there are a lot of other tools also, but explain plan is a good start.

Upvotes: 3

Related Questions