Re: "between" vs. ">= and <="
Date: Sun, 16 Nov 2008 22:49:57 -0800 (PST)
Message-ID: <138553a5-d328-4292-ac0f-f2bb3f10e0ea@40g2000prx.googlegroups.com>
On Nov 14, 6:46 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 14.11.2008 17:16, sybrandb wrote:
> > The request may be sensible.
> > In the 'old' days I had
>
> When (i.e. which Oracle version) was that?
Sybrand, any version numbers?
> > 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.
>
> Spooky. Thanks for the hint. I'll do another test with an additional
> column.
Same story: predicates look identical and there is no indication of a different treatment. Frankly, it would have surprised me - but you never know.
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, mod(level, 7) as a 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 a = 3 and ts between systimestamp + 10000 and systimestamp
+ 10001
4 /
no rows selected
Execution Plan
Plan hash value: 3332582666
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 358 | 3580 | 407 (25)| 00:00:02 | |* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 358 | 3580 | 407 (25)| 00:00:02 |
Predicate Information (identified by operation id):
1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP(6)+10001) 2 - filter("A"=3 AND "TS">=SYSTIMESTAMP(6)+10000 AND
"TS"<=SYSTIMESTAMP(6)+10001)
Statistics
1 recursive calls
0 db block gets
2199 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
366 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select *
2 from t1
3 where a = 3 and ts >= systimestamp + 10000 and ts <= systimestamp
+ 10001
4 /
no rows selected
Execution Plan
Plan hash value: 3332582666
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 358 | 3580 | 407 (25)| 00:00:02 | |* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 358 | 3580 | 407 (25)| 00:00:02 |
Predicate Information (identified by operation id):
1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP(6)+10001) 2 - filter("A"=3 AND "TS">=SYSTIMESTAMP(6)+10000 AND
"TS"<=SYSTIMESTAMP(6)+10001)
Statistics
1 recursive calls
0 db block gets
2199 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
366 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 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 a = 3 and ts between systimestamp + 10000 and systimestamp
+ 10001
4 /
no rows selected
Execution Plan
Plan hash value: 3861020667
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 348 | 3480 | 24 (0)| 00:00:01 | |* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 348 | 3480 | 24 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 4500 | | 14 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP(6)+10001)
2 - filter("A"=3)
3 - 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
284 bytes sent via SQL*Net to client
366 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select *
2 from t1
3 where a = 3 and ts >= systimestamp + 10000 and ts <= systimestamp
+ 10001
4 /
no rows selected
Execution Plan
Plan hash value: 3861020667
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 348 | 3480 | 24 (0)| 00:00:01 | |* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 348 | 3480 | 24 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 4500 | | 14 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(SYSTIMESTAMP(6)+10000<=SYSTIMESTAMP(6)+10001)
2 - filter("A"=3)
3 - 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
284 bytes sent via SQL*Net to client
366 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> drop table t1
2 /
Table dropped.
SQL> spool off
Cheers
robert Received on Mon Nov 17 2008 - 00:49:57 CST