Re: transitivity applied on timestamp column but not with date column
- From: Mauro Pagano <mauro.pagano@xxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Sun, 15 Feb 2015 08:44:22 -0500
That section dumps of predicate list before/after check constraint
predicates generation but in this case there is none and the list remains
the same :-)
I agree that might be a little confusing
On Sun, Feb 15, 2015 at 2:37 AM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
> Hi Mauro
>
> 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@xxxxxxxxx>
> 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@xxxxxxxxx> 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))
>>>
>>>
>>>
>>>
>>>
>>
>
Other related posts:
