FAQ
All,

I am facing a performance problem which is caused due to comparing a date
column with a TIMESTAMP variable. We are using Siebel Analytics (on 10gR2)
and all date variables from the Siebel side are passed as timestamp e.g.
TIMESTAMP '2008-02-29 00:00:00'.

In the execution plan we see the following conversion

filter(INTERNAL_FUNCTION("T539185"."CREATION_DATE")>TIMESTAMP'2008-02-29

00:00:00.000000000')

which prevents the index on creation_date to be picked up. When I change the
sql manually to use a TO_DATE function the index is used and the query runs
much faster.

This article describes the issue but none of the solutions can be
implemented quickly (while some them are not acceptable).

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#08_01

I was hoping if this is a known issue and anybody has a fix purely on the
database side.

Thanks
Deba.

Search Discussions

  • Sylvester, Peter A. at Jun 4, 2008 at 11:10 pm
    This is purely conjecture, but you could try creating a function based
    index on to_timstamp(creation_date).


    --Peter
  • David Aldridge at Jun 5, 2008 at 12:31 am
    Could it be that it is a configuration item in one of the OBIEE layers that is doing this? Something that says, "accept dates in the form of timestamps"? Maybe the column that the predicate is being placed against is being tagged as a timestamp.

    ----- Original Message ----
    From: Debaditya Chatterjee
    To: oracle-l@freelists.org
    Sent: Wednesday, June 4, 2008 3:18:45 PM
    Subject: Date and timestamp comparision

    All,

    I am facing a performance problem which is caused due to comparing a date column with a TIMESTAMP variable. We are using Siebel Analytics (on 10gR2) and all date variables from the Siebel side are passed as timestamp e.g. TIMESTAMP '2008-02-29 00:00:00'.

    In the execution plan we see the following conversion

    filter(INTERNAL_FUNCTION("T539185"."CREATION_DATE")>TIMESTAMP'2008-02-29
    00:00:00.000000000')

    which prevents the index on creation_date to be picked up. When I change the sql manually to use a TO_DATE function the index is used and the query runs much faster.

    This article describes the issue but none of the solutions can be implemented quickly (while some them are not acceptable).

    http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#08_01

    I was hoping if this is a known issue and anybody has a fix  purely on the database side.

    Thanks
    Deba.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 4, '08 at 7:18p
activeJun 5, '08 at 12:31a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2021 Grokbase