transitivity applied on timestamp column but not with date column

From: Ls Cheng <exriscer_at_gmail.com>
Date: Sun, 15 Feb 2015 00:56:43 +0100
Message-ID: <CAJ2-Qb-b9o0vi3xo8o8rPNgtBtyr1ugZkmDDzgUY_Ta6Dj-Jjg_at_mail.gmail.com>

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

Received on Sun Feb 15 2015 - 00:56:43 CET