Re: transitivity applied on timestamp column but not with date column

The additional FILTER is likely there because it wasn't possible to fully understand the filter values during parse (and not due to transitivity),if you replace the literals with binds you get the same FILTER on step 2 with (:B1<:B2). Basically the CBO takes a safer/smart way so in case the values passed are for an "always empty" range then the execution stops at the FILTER without proceeding further down.
The problem is in the CAST(TO_DATE...)) that makes the value basically a black box, if you replace it with a TO_TIMESTAMP(...) in the second TC then the FILTER step disappears.
Even though the FILTER is present in the plan it shouldn't have any significant impact on the final performance but if you want to get rid of it you can just use a plain data type conversion

I hope it helps,
Mauro

PS: tests run in 11.2.0.3, there might be small changes in other versions

On Sat, Feb 14, 2015 at 6:56 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> Hi
>
> I have a partitioned table which contains a timestamp column using
> timestamp(6), it is used in the predicate and it's the partitioning ley.
>
> I observe that for a simply query such as
>
> SELECT COUNT (*)
> FROM t100 c
> WHERE c.tr_commit_time >= CAST(TO_DATE ('20150215 0000', 'yyyymmdd
> hh24mi') as timestamp)
> AND c.tr_commit_time < CAST(TO_DATE ('20150216 0000', 'yyyymmdd
> hh24mi') as timestamp)
>
> in the execution plan it adds a filter operation such as
>
> Plan hash value: 4211770842
>
>
> ------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time | Pstart| Pstop |
>
> ------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 11 | 68
> (15)| 00:00:01 | | |
> | 1 | SORT AGGREGATE | | 1 | 11 |
> | | | |
> *|* 2 | FILTER | | | |
> | | | | -> THIS OPERATION DOES NOT OCCUR WITH DATE
> DATATYPE*
> | 3 | PARTITION RANGE ITERATOR| | 86399 | 928K| 68
> (15)| 00:00:01 | KEY | KEY |
> |* 4 | INDEX FAST FULL SCAN | T100_I1 | 86399 | 928K| 68
> (15)| 00:00:01 | KEY | KEY |
>
> ------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter(CAST(TO_DATE(' 2015-02-16 00:00:00', 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp)>CAST(TO_DATE(' 2015-02-15 00:00:00',
> 'syyyy-mm-dd hh24:mi:ss') AS timestamp))
> 4 - filter("C"."TR_COMMIT_TIME"<CAST(TO_DATE(' 2015-02-16 00:00:00',
> 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp) AND
> "C"."TR_COMMIT_TIME">=CAST(TO_DATE(' 2015-02-15 00:00:00', 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp))
>
>
>
> If the column data type is date this does not happen.
>
> I ran 10053 and saw that it happens because the optimizer generates
> transitive predicate for timestamp data type.
>
> Anyone know the reasoning?
>
> TIA
>
> Below the test case.
>
>
>
> Test case, with date:
>
> CREATE TABLE T100
> (
> TR_COMMIT_TIME DATE,
> C1 VARCHAR2(20)
> )
> PARTITION BY RANGE (TR_COMMIT_TIME)
> INTERVAL(NUMTODSINTERVAL(1,'DAY'))
> (
> PARTITION VALUES LESS THAN (TO_TIMESTAMP('20150301', 'YYYYMMDD'))
> );
>
> CREATE INDEX T100_I1 ON T100
> (TR_COMMIT_TIME)
> LOCAL;
>
> insert /*+ append */ into t100
> with tdata as (
> select rownum id
> from all_objects
> where rownum <= 1000
> )
> select trunc(systimestamp) + rownum/86400, DBMS_RANDOM.STRING('U', 20)
> from tdata a, tdata b
> where rownum <= 86400;
>
>
> SELECT COUNT (*)
> FROM t100 c
> WHERE c.tr_commit_time >= TO_DATE ('20150215 0000', 'yyyymmdd hh24mi')
> AND c.tr_commit_time < TO_DATE ('20150216 0000', 'yyyymmdd hh24mi')
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3278338672
>
>
> ---------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time | Pstart| Pstop |
>
> ---------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 8 | 59 (2)|
> 00:00:01 | | |
> | 1 | SORT AGGREGATE | | 1 | 8 |
> | | | |
> | 2 | PARTITION RANGE SINGLE| | 86399 | 674K| 59 (2)|
> 00:00:01 | 1 | 1 |
> |* 3 | INDEX FAST FULL SCAN | T100_I1 | 86399 | 674K| 59 (2)|
> 00:00:01 | 1 | 1 |
>
> ---------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - filter("C"."TR_COMMIT_TIME"<TO_DATE(' 2015-02-16 00:00:00',
> 'syyyy-mm-dd hh24:mi:ss') AND "C"."TR_COMMIT_TIME">=TO_DATE(' 2015-02-15
> 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
>
>
>
>
>
>
>
> Test case, with timestamp:
>
> CREATE TABLE T100
> (
> TR_COMMIT_TIME TIMESTAMP(6),
> C1 VARCHAR2(20)
> )
> PARTITION BY RANGE (TR_COMMIT_TIME)
> INTERVAL(NUMTODSINTERVAL(1,'DAY'))
> (
> PARTITION VALUES LESS THAN (TO_TIMESTAMP('20150301', 'YYYYMMDD'))
> );
>
> CREATE INDEX T100_I1 ON T100
> (TR_COMMIT_TIME)
> LOCAL;
>
> insert /*+ append */ into t100
> with tdata as (
> select rownum id
> from all_objects
> where rownum <= 1000
> )
> select trunc(systimestamp) + rownum/86400, DBMS_RANDOM.STRING('U', 20)
> from tdata a, tdata b
> where rownum <= 86400;
>
> SELECT COUNT (*)
> FROM t100 c
> WHERE c.tr_commit_time >= CAST(TO_DATE ('20150215 0000', 'yyyymmdd
> hh24mi') as timestamp)
> AND c.tr_commit_time < CAST(TO_DATE ('20150216 0000', 'yyyymmdd
> hh24mi') as timestamp)
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 4211770842
>
>
> ------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time | Pstart| Pstop |
>
> ------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 11 | 68
> (15)| 00:00:01 | | |
> | 1 | SORT AGGREGATE | | 1 | 11 |
> | | | |
> |* 2 | FILTER | | | |
> | | | |
> | 3 | PARTITION RANGE ITERATOR| | 86399 | 928K| 68
> (15)| 00:00:01 | KEY | KEY |
> |* 4 | INDEX FAST FULL SCAN | T100_I1 | 86399 | 928K| 68
> (15)| 00:00:01 | KEY | KEY |
>
> ------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter(CAST(TO_DATE(' 2015-02-16 00:00:00', 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp)>CAST(TO_DATE(' 2015-02-15 00:00:00',
> 'syyyy-mm-dd hh24:mi:ss') AS timestamp))
> 4 - filter("C"."TR_COMMIT_TIME"<CAST(TO_DATE(' 2015-02-16 00:00:00',
> 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp) AND
> "C"."TR_COMMIT_TIME">=CAST(TO_DATE(' 2015-02-15 00:00:00', 'syyyy-mm-dd
> hh24:mi:ss') AS timestamp))
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l