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.