避免将日期隐式转换为使用Hibernate的Oracle进行选择的时间戳 [英] Avoid implicit conversion from date to timestamp for selects with Oracle using Hibernate

问题描述

我使用Hibernate 3.2.7.GA条件查询来从Oracle Enterprise Edition 10.2.0.4.0数据库中选择行，并通过时间戳字段进行过滤。问题中的字段类型为Java中的 java.util.Date ，Oracle中的 DATE 。

结果该字段被映射到 java.sql.Timestamp ，Oracle将所有行转换为 TIMESTAMP ，然后与传入值进行比较，绕过索引，从而破坏了性能。

一个解决方案是使用Hibernate的 sqlRestriction（）以及Oracle的 TO_DATE 函数。这将修复性能，但需要重写应用程序代码（大量查询）。

那么是否有更优雅的解决方案？由于Hibernate已经做过类型映射，它可以配置成正确的事情吗？

更新：问题发生在各种配置中，但是这里有一个具体的例子：

• Oracle企业版10.2.0.4.0


• Oracle JDBC驱动程序11.1.0.7.0
  
• Hibernate 3.2.7.GA


• Hibernate的Oracle10gDialect


• Java 1.6.0_16
Oracle数据库常见问题解答：



$ b $ b

默认情况下，11.1版驱动程序将SQL DATE转换为时间戳


所以这是一个预期的行为。
对我来说，这意味着来自 DATE 列的实际值被转换为 java.sql.Timestamp ，不是将 java.util.Date 的绑定变量转换为 java.sql.Timestamp 。



EXPLAIN PLAN 输出将有助于识别问题。另外，Oracle跟踪可以准确地告诉您在查询中绑定变量的类型是什么。




如果真的发生了这种情况，可能是Oracle的一个bug。





你可以这样解决它：






• 创建一个FBI（<一个href =http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref928 =nofollow>基于功能的索引） c $ c> DATE 列，将其转换为 TIMESTAMP 。例如：
  
  

    CREATE INDEX tab_idx ON选项卡（CAST（date_col AS TIMESTAMP））COMPUTE STATISTICS; 
    




• 创建一个包含相同 CAST 表达式。您可以保留相同的列名称：



    CREATE VIEW v AS 
   SELECT CAST（date_col AS TIMESTAMP） AS date_col，col_1，... FROM标签; 
    




• 使用视图而不是表格（无论如何，这通常是一个好主意，例如，如果你已经在使用View，你根本不需要改变代码）。当 java.sql.Timestamp 变量将与 date_col 在 WHERE 条件（如果选择足够的话）将使用该指数。

如果你发现为什么有一个 java.sql.Timestamp （或者Oracle修复了潜在的bug），你总是可以回头改变视图（并放弃FBI），并且它对代码完全透明。





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

解决方案

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

这篇关于避免将日期隐式转换为使用Hibernate的Oracle进行选择的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！