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

I thought it was transitivity because the 10053 trace shows this ccomment

"C"."TR_COMMIT_TIME">=CAST(TO_DATE(' 2015-02-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) AND "C"."TR_COMMIT_TIME"<CAST(TO_DATE(' 2015-02-11 00:05:00', 'syyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "C"."TR_COMMIT_TIME">=CAST(TO_DATE(' 2015-02-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) AND "C"."TR_COMMIT_TIME"<CAST(TO_DATE(' 2015-02-11 00:05:00', 'syyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) AND CAST(TO_DATE(' 2015-02-11 00:05:00', 'syyyy-mm-dd h

Thanks for explaining about CAST, I didnt know that makes the predicate "unknown" :-)

Cheers

On Sun, Feb 15, 2015 at 4:34 AM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:

> Hi Ls,
>
> 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