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

Jun 22, 2007 / By Alex Fatkulin

Tags: ,

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.

5 Responses to “Can “between” and “>= and <=” Differ in Oracle?”

  • Barfo Rama says:

    Range partitioning was introduced in Oracle8
    List partitioning was Oracle 9i.
    Range uses less than syntax.
    Between uses less than or equal syntax.
    An optimizer might watch for a different code path for equal or less than versus less than on a numeric index, right?
    Could it be just a leftover bit of code that no one has complained about until now? That _is_ how the code evolves, right?
    No way to know, scientifically.

    But it’s always nice to see a hole in the docs exposed! Great catch!

  • Mr. Ed says:

    First off, you make it a little hard to verify this example. You’ve modified the plan output, since there is no “*” indicator for the predicate in the plan. Since you’ve cut off columns in the plan (and the end of it), we don’t know if you have cpu_costing enabled or disabled. Considering the other modifications, we don’t know for sure if you used autotrace (probably) or “explain plan”, or v$sql_plan for that matter, to get the plan.

    Second, if the second plan never touches the table, how does it magically find the values of “n”?

    On my tests on 10.2.0.1 and 9.2.0.5, I get the same plans.

  • Mr. Ed,

    I didn’t modified anything except for cutting up the right side of the plan in order to make it fit nicely. I didn’t get any filter predicates reported for that example on 10.2.0.3 autotrace.

    I didn’t specify how I get this plan since you can look at v$sql_plan, 10053 trace, explain plan, autotrace and tkprof – everything will be the same. 10G enables CPU costing by default.

    I’m too getting the same plans on 10.2.0.1. That’s why I explicitly stated the version. And yes – there is filter predicates reported on 10.2.0.1 (but not on 10.2.0.3).

    thanks for comment.

  • Mr. Ed says:

    Hmmm. So where did the filter predicates go, and where did it get values for “n” in the second plan?

    What I’m getting at is that I think the plans are still the same, but something else is going on here.

  • Mr. Ed,

    the plans are different and so are the runtime stats.

    If you still in doubt I can email you a trace (10.2.0.3). Or you can get your hands on 10.2.0.3 and see for yourself.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>