"between" vs. ">= and <="
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