Can “between” and “>= and <=" Differ in Oracle?

To between or not to between? Sometimes I wonder how “simple” things can end up being not where you would expect them to be.

Let’s take the following question, for example. Is there any difference between using:
where column between n and m and where column>=n and column<=m?

Looks like a simple one, eh? Oracle’s documentation is dead clear on this:

[Between] means “greater than or equal to low value and less than or equal to high value.”

They are the same from a semantic point of view. But SQL is a declarative language. In other words, you wouldn’t expect same execution plan with two semantically identical statements, would you? But we can actually observe that between can be transparently transformed into “greater than or equal to low value and less than or equal to high value” by the optimizer itself:

SQL> select *
  2   from dual
  3   where dummy between 'A' and 'Z';
–– plan omitted for the sake of clarity
Predicate Information (identified by operation id):
––––––––––––––––––––––––––––––––––––––––––

   1 - filter("DUMMY">='A' AND "DUMMY"<='Z')

So it should be safe to assume that both statements are interchangeable with each other, since they all lead to the same filter or access predicate. Right?

There is at least one known (to me) example where both statement produce different execution plans. You never know until you test it.

We start by creating a simple list-partitioned table with the local index:

SQL> create table t (n number, m number not null)
  2  partition by list (n)
  3  (
  4    partition t_0 values (0),
  5    partition t_1 values (1)
  6  );

Table created.

SQL> create index i_t_m on t (m) local;

Index created.

The plan with between:

SQL> select /*+ index(t i_t_m) */ m
  2    from t
  3    where n between 0 and 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 555829789

------------------------------------------------------------
| Id  | Operation                          | Name  | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |     1 |
|   1 |  PARTITION LIST ALL                |       |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |     1 |
|   3 |    INDEX FULL SCAN                 | I_T_M |     1 |
------------------------------------------------------------

And the other plan:

SQL> select /*+ index(t i_t_m) */ m
  2    from t
  3    where n >= 0 and n <= 1;

Execution Plan
--------------------------------------------
Plan hash value: 1131641999

--------------------------------------------
| Id  | Operation          | Name  | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |
|   1 |  PARTITION LIST ALL|       |     1 |
|   2 |   INDEX FULL SCAN  | I_T_M |     1 |

That is exactly how we discovered it. After switching to between, the query magically started to perform slower.

Actually, by looking at the 10053 trace for between you will find the same transformation:

FPD:   Current where clause predicates in SEL$1 (#0) :
         "T"."N">=0 AND "T"."N"<=1

Apparently, there are still different code paths in the optimizer for handling those statements at least somewhere during partition elimination. The lesson is: even in such a “simple” situation it may be a good idea to setup a test case to evaluate your exact situation.

P.S.: The observations are from 10.2.0.3 so it might be a good idea to check this one against your own release.