Re: "between" vs. ">= and <="

On 14 nov, 17:10, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> Hi,
>
> a client of us requested to replace ">= and <=" with "between" with a
> TIMESTAMP column. Unfortunately I am still waiting for his
> justification...  In the meantime I thought I do some researching.
> Personally I cannot think of any reason why one or the other should be
> more efficient.
>
> I did some simplistic testing but no difference visible. (Experimental
> code at end.)
>
> I even believe that I once had found a statement saying that there is
> no difference performance wise.  Any pointers?
>
> Kind regards
>
> robert
>
> SQL> show release
> release 1002000400
> SQL> set echo on
> SQL> set pagesize 100 linesize 100
> SQL> create table t1
>   2  as
>   3  select systimestamp + level as ts
>   4  from dual
>   5  connect by level <= 1000000
>   6  /
>
> Table created.
>
> SQL> begin
>   2    dbms_stats.gather_schema_stats(
>   3      user,
>   4      method_opt => 'FOR ALL COLUMNS SIZE AUTO',
>   5      granularity => 'AUTO',
>   6      cascade => true,
>   7      options => 'GATHER',
>   8      degree => null
>   9    );
>  10  end;
>  11  /
>
> PL/SQL procedure successfully completed.
>
> SQL> set autotrace traceonly
> SQL> select *
>   2  from t1
>   3  where ts between systimestamp + 10000 and systimestamp + 10001
>   4  /
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value:
> 3332582666
>
> ---------------------------------------------------------------------------
> | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
> Time     |
> ---------------------------------------------------------------------------
> |   0 | SELECT STATEMENT   |      |  2495 | 19960 |   595  (58)|
> 00:00:03 |
> |*  1 |  FILTER            |      |       |       |
> |          |
> |*  2 |   TABLE ACCESS FULL| T1   |  2495 | 19960 |   595  (58)|
> 00:00:03 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation
> id):
> ---------------------------------------------------
>
>    1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP
> (6)+10001)
>    2 - filter("TS">=SYSTIMESTAMP(6)+10000
> AND
>               "TS"<=SYSTIMESTAMP
> (6)+10001)
>
> Statistics
> ----------------------------------------------------------
>           1  recursive
> calls
>           0  db block
> gets
>        1805  consistent
> gets
>           0  physical
> reads
>           0  redo
> size
>         334  bytes sent via SQL*Net to
> client
>         377  bytes received via SQL*Net from
> client
>           2  SQL*Net roundtrips to/from
> client
>           0  sorts
> (memory)
>           0  sorts
> (disk)
>           1  rows
> processed
>
> SQL> select *
>   2  from t1
>   3  where ts >= systimestamp + 10000 and ts <= systimestamp + 10001
>   4  /
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value:
> 3332582666
>
> ---------------------------------------------------------------------------
> | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
> Time     |
> ---------------------------------------------------------------------------
> |   0 | SELECT STATEMENT   |      |  2495 | 19960 |   595  (58)|
> 00:00:03 |
> |*  1 |  FILTER            |      |       |       |
> |          |
> |*  2 |   TABLE ACCESS FULL| T1   |  2495 | 19960 |   595  (58)|
> 00:00:03 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation
> id):
> ---------------------------------------------------
>
>    1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP
> (6)+10001)
>    2 - filter("TS">=SYSTIMESTAMP(6)+10000
> AND
>               "TS"<=SYSTIMESTAMP
> (6)+10001)
>
> Statistics
> ----------------------------------------------------------
>           1  recursive
> calls
>           0  db block
> gets
>        1805  consistent
> gets
>           0  physical
> reads
>           0  redo
> size
>         334  bytes sent via SQL*Net to
> client
>         377  bytes received via SQL*Net from
> client
>           2  SQL*Net roundtrips to/from
> client
>           0  sorts
> (memory)
>           0  sorts
> (disk)
>           1  rows
> processed
>
> SQL> create index i1 on t1(ts)
>   2  /
>
> Index created.
>
> SQL> begin
>   2    dbms_stats.gather_schema_stats(
>   3      user,
>   4      method_opt => 'FOR ALL COLUMNS SIZE AUTO',
>   5      granularity => 'AUTO',
>   6      cascade => true,
>   7      options => 'GATHER',
>   8      degree => null
>   9    );
>  10  end;
>  11  /
>
> PL/SQL procedure successfully completed.
>
> SQL> select *
>   2  from t1
>   3  where ts between systimestamp + 10000 and systimestamp + 10001
>   4  /
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value:
> 2069189151
>
> --------------------------------------------------------------------------
> | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
> Time     |
> --------------------------------------------------------------------------
> |   0 | SELECT STATEMENT  |      |  2505 | 20040 |    14   (0)|
> 00:00:01 |
> |*  1 |  FILTER           |      |       |       |
> |          |
> |*  2 |   INDEX RANGE SCAN| I1   |  2505 | 20040 |    14   (0)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation
> id):
> ---------------------------------------------------
>
>    1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP
> (6)+10001)
>    2 - access("TS">=SYSTIMESTAMP(6)+10000
> AND
>               "TS"<=SYSTIMESTAMP
> (6)+10001)
>
> Statistics
> ----------------------------------------------------------
>           1  recursive
> calls
>           0  db block
> gets
>           4  consistent
> gets
>           0  physical
> reads
>           0  redo
> size
>         334  bytes sent via SQL*Net to
> client
>         377  bytes received via SQL*Net from
> client
>           2  SQL*Net roundtrips to/from
> client
>           0  sorts
> (memory)
>           0  sorts
> (disk)
>           1  rows
> processed
>
> SQL> select *
>   2  from t1
>   3  where ts >= systimestamp + 10000 and ts <= systimestamp + 10001
>   4  /
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value:
> 2069189151
>
> --------------------------------------------------------------------------
> | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
> Time     |
> --------------------------------------------------------------------------
> |   0 | SELECT STATEMENT  |      |  2505 | 20040 |    14   (0)|
> 00:00:01 |
> |*  1 |  FILTER           |      |       |       |
> |          |
> |*  2 |   INDEX RANGE SCAN| I1   |  2505 | 20040 |    14   (0)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation
> id):
> ---------------------------------------------------
>
>    1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP
> (6)+10001)
>    2 - access("TS">=SYSTIMESTAMP(6)+10000
> AND
>               "TS"<=SYSTIMESTAMP
> (6)+10001)
>
> Statistics
> ----------------------------------------------------------
>           1  recursive
> calls
>           0  db block
> gets
>           4  consistent
> gets
>           0  physical
> reads
>           0  redo
> size
>         334  bytes sent via SQL*Net to
> client
>         377  bytes received via SQL*Net from
> client
>           2  SQL*Net roundtrips to/from
> client
>           0  sorts
> (memory)
>           0  sorts
> (disk)
>           1  rows
> processed
>
> SQL> drop table t1
>   2  /
>
> Table dropped.
>
> SQL> spool off

Robert,

The request may be sensible.
In the 'old' days I had

where a=... and date_column>= and date_column<= and c=...

seeing optimized as
where (a= and date_column>=) and (datum_column<=) and c=

And gone was the index usage!
Between ... and ... is treated as one construct.

--
Sybrand Bakker
Senior Oracle DBA