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

On Nov 14, 11:16 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

The best way to make a decision is probably to run the same SQL with one version using >= and <= and the other using between on a large body of data several times averaging the run times of the code and then compare the average for the two versions.

Unless a noticable difference can be found I would suggest suggesting leaving the existing code alone and publishing a note to use between in new code.

In the 'old' days (7.0, 7.1) I am pretty sure I read that the CBO transformed a between into >= and <= statements. Somewhere along the line I believe that changed and between probably has been optimized. I have never tested it like I suggested above. I will have to add that to my list.

HTH -- Mark D Powell -- Received on Fri Nov 14 2008 - 11:37:47 CST