Avoid implicit conversion from date to timestamp for selects with Oracle using Hibernate

I'm using Hibernate 3.2.7.GA criteria queries to select rows from an Oracle Enterprise Edition 10.2.0.4.0 database, filtering by a timestamp field. The fie开发者_Go百科ld in question is of type java.util.Date in Java, and DATE in Oracle.

It turns out that the field gets mapped to java.sql.Timestamp, and Oracle converts all rows to TIMESTAMP before comparing to the passed in value, bypassing the index and thereby ruining performance.

One solution would be to use Hibernate's sqlRestriction() along with Oracle's TO_DATE function. That would fix performance, but requires rewriting the application code (lots of queries).

So is there a more elegant solution? Since Hibernate already does type mapping, could it be configured to do the right thing?

Update: The problem occurs in a variety of configurations, but here's one specific example:

• Oracle Enterprise Edition 10.2.0.4.0
• Oracle JDBC Driver 11.1.0.7.0
• Hibernate 3.2.7.GA
• Hibernate's Oracle10gDialect
• Java 1.6.0_16

This might sound drastic, but when faced with this problem we ended up converting all DATE columns to TIMESTAMP types in the database. There's no drawback to this that I can see, and if Hibernate is your primary application platform then you'll save yourself future aggravation.

Notes:

• The column types may be changed with a simple "ALTER tableName MODIFY columnName TIMESTAMP(precisionVal)".

• I was surprised to find that indexes on these columns did NOT have to be
  rebuilt.

Again, this only makes sense if you're committed to Hibernate.

According to Oracle JDBC FAQ:

"11.1 drivers by default convert SQL DATE to Timestamp when reading from the database"

So this is an expected behaviour. To me this means that actual values coming from DATE columns are converted to java.sql.Timestamp, not that bind variables with java.util.Date are converted to java.sql.Timestamp.

An EXPLAIN PLAN output would help identifying the issue. Also, an Oracle trace could tell you exactly what type is assigned to the bind variable in the query.

If that's really happening it could be a Oracle bug.

You can work around it this way:

• Create an FBI (Function Based Index) on the DATE column, casting it to a TIMESTAMP. For example:

  CREATE INDEX tab_idx ON tab (CAST(date_col AS TIMESTAMP)) COMPUTE STATISTICS;
  

• Create a View that contains the same CAST expression. You can keep the same column name if you want:

  CREATE VIEW v AS
  SELECT CAST(date_col AS TIMESTAMP) AS date_col, col_1, ... FROM tab;
  

• Use the View instead of the Table (it's often a good idea anyway, e.g. if you were already using a View, you wouldn't need to change the code at all). When a java.sql.Timestamp variable will be used with date_col in the WHERE condition, (if enough selective) the Index will be used.

• If you find out why there was a java.sql.Timestamp (or Oracle fixes the potential bug), you can always go back just changing the View (and dropping the FBI), and it would be completely transparent to the code