Re: "between" vs. ">= and <="
- From: Robert Klemme <shortcutter@xxxxxxxxxxxxxx>
- Date: Sun, 16 Nov 2008 22:49:57 -0800 (PST)
On Nov 14, 6:46 pm, Robert Klemme <shortcut...@xxxxxxxxxxxxxx> 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
.
- References:
- "between" vs. ">= and <="
- From: Robert Klemme
- Re: "between" vs. ">= and <="
- From: sybrandb
- Re: "between" vs. ">= and <="
- From: Robert Klemme
- "between" vs. ">= and <="
- Prev by Date: Re: Oracle 10g XE with SQL Developer
- Next by Date: Re: View from CLOB chunks
- Previous by thread: Re: "between" vs. ">= and <="
- Next by thread: Re: "between" vs. ">= and <="
- Index(es):
Relevant Pages
|
