to_timestamp vs. to_date when forcing native SQL
431272Jan 25 2005 — edited Feb 11 2005Hello!
I was recently encountering performance problems when querying on dates, and found some useful help on this forum. The suggestion I took was to set the <uses-native-sql> element to true in my sessions.xml file. This caused TopLink to use the native "to_date()" syntax instead of the generic "ts{}" syntax.
My query went from taking several minutes to taking a matter of seconds. So far, so good.
Recently, we upgraded from TopLink 9.0.4 (build 031126) to TopLink 9.0.4.5 (build 040930). Now, when I examine the generated SQL, I see that TopLink is using the native "to_timestamp()" syntax which once again slows my query to a crawl.
Any thoughts on a way around this issue? Is there a way to force TopLink to use the to_date() syntax for my particular Expression query?
We upgraded TopLink for specific reasons, so reverting to the prior version is not even a short-term work-around.
Other specifics:
* Our database is Oracle Database 10g Enterprise Edition Release 10.1.0.3.0.
* The database field being queried is defined as a Date in the schema.
* We are using version 9.0.1.5.0 of the Oracle drivers.