[Oracle-l] Date and timestamp comparision

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

----- 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