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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 14 Nov 2008 08:10:51 -0800 (PST)
Message-ID: <ed159bde-2e5b-4c13-8f77-5700c0b8d9b1@o4g2000pra.googlegroups.com>

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 Received on Fri Nov 14 2008 - 10:10:51 CST

Original text of this message