Re: Date and timestamp comparision

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 <debaditya.chatterjee@gmail.com> 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