“Oracle 9i/Oracle 10g” 查询 参数java.util.date 未使用索引

假设我们使用这样的sql通过绑定变量(类型为java.util.date)查询数据库，其中end_date是date类型且建立了索引。
    “select count(*) from table1 where end_date >= :1 and end_date <= :2”

通常，面对这样的sql，我们希望它的执行计划走index range scan。然而在默认情况下oracle CBO是不会选择走索引地，以上面这语句为例，oracle实际走的是table full scan。

为什么会这样呢？
这类问题是oracle在9.2以后引入了TIMESTAMP才开始出现地。
在 9.2之前，oracle只有DATE，而没有TIMESTAMP。在jdbc preparedStatement.setTimestamp时，绑定变量的类型会被正确的设置为DATE。而在9.2之后，oracle开始支持 TIMESTAMP了，这两者都能支持精度为yyyy-MM-dd hh24:mi:ss的时间（当然TIMESTAMP能支持到纳秒级别），但jdbc driver的api未变同样在preparedStatement.setTimestamp时，oracle driver就得选择到底该把绑定变量的类型设置为DATE还是TIMESTAMP呢？估计是由于TIMESTAMP的精度更高，oracle最终默认选择了将绑定变量的类型设置为了TIMESTAMP。那么这个时候，如果面对实际属性为DATE的列，那么就会导致oracle隐式地进行形如 “TO_TIMESTAMP(date_column) = parameter_timestamp”转换，要知道oracle CBO不会选择被某函数作用的列上的索引，除非是函数索引。因此，最终也会导致最上面的情况使用table full scan而不是index range scan。

Oracle就没有提供别的方法来正确地提供绑定变量吗？oracle提供了几个方法来解决这个问题
1.升级到11g并使用新的正确的driver api。
2.将DATE列全都改成TIMESTAMP列。
3.使用V8Compatible flag。

 

当然还有一个笨笨的方法

在sql里面直接to_date().

下面只对V8Compatible展开说一下。

 

 

 

Oracle 写道

Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

java -Doracle.jdbc.V8Compatible="true" MyApp

 

上面是oracle官网上的原话。简单地说就是设置oracle.jdbc.V8Compatible="true"，不管你是在使用连接属性还是系统变量都可以。

Properties prop=new Properties();
        prop.setProperty("user","platdb1");
        prop.setProperty("password","platdb1");
        prop.setProperty("oracle.jdbc.V8Compatible","true");        
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test", prop);

 

因此，那些使用JDBC和IBATIS以及自己写的封装框架的同学们要注意了，preparedStatement.setTimestamp在oracle 9.2以后默认都是将绑定变量的类型设置为TIMESTAMP。

我最近遇到问题就是在ibatis中，传入参数java.util.date，ibatis会在内部将使用DateTypeHandler，

public void setParameter(PreparedStatement ps, int i, Object parameter, String jdbcType)
      throws SQLException {
    ps.setTimestamp(i, new java.sql.Timestamp(((Date) parameter).getTime()));
}

 

就是上面这个方法使用的是setTimestamp(在ibatis3 beta8中，依旧是类似代码)。在oracle9.2/10里还可以使用V8Compatible flag，而在oracle 11g里已经不再使用V8Compatible来区分了，而是更改了driver api，setTimestamp方法只会将绑定变量类型设置为TIMESTAMP。

------------顽强的分隔符------------

我也没想好题目是啥，就按关键字拟了一个。

------------顽强的分隔符------------

 

JE里既有不少相关的文章，各位同学可以搜一搜。

在oracle forums里有个帖子也讨论了这个问题。

 

最后附上Oracle FAQ关于这个问题的说明

Oracle 写道

Simple Data Types
What is going on with DATE and TIMESTAMP?

This section is on simple data types. :-)

Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.

In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp. Unfortunately if you were relying on DATE values to contain time information, there is a problem.

There are several ways to address this problem in the 9.2 through 10.2 drivers:

*

Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.
*

Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it?).
*

Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.
*

Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.
java -Doracle.jdbc.V8Compatible="true" MyApp

Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default. There is no need to set V8Compatible to get the correct mapping. V8Compatible is strongly deprecated. You should not use it at all. If you do set it to true it won't hurt anything, but you should stop using it.

Although it was rarely used that way, V8Compatible existed not to fix the DATE to Date issue but to support compatibility with 8i databases. 8i (and older) databases did not support the TIMESTAMP type. Setting V8Compatible not only caused SQL DATE to be mapped to Timestamp when read from the database, it also caused all Timestamps to be converted to SQL DATE when written to the database. Since 8i is desupported, the 11.1 JDBC drivers do not support this compatibility mode. For this reason V8Compatible is desupported.

As mentioned above, the 11.1 drivers by default convert SQL DATE to Timestamp when reading from the database. This always was the right thing to do and the change in 9i was a mistake. The 11.1 drivers have reverted to the correct behavior. Even if you didn't set V8Compatible in your application you shouldn't see any difference in behavior in most cases. You may notice a difference if you use getObject to read a DATE column. The result will be a Timestamp rather than a Date. Since Timestamp is a subclass of Date this generally isn't a problem. Where you might notice a difference is if you relied on the conversion from DATE to Date to truncate the time component or if you do toString on the value. Otherwise the change should be transparent.

If for some reason your app is very sensitive to this change and you simply must have the 9i-10g behavior, there is a connection property you can set. Set mapDateToTimestamp to false and the driver will revert to the default 9i-10g behavior and map DATE to Date.

 

 

 

评论

2 楼 angel6278 2012-03-31  

还真是碰到这个问题了……

1 楼 J-catTeam 2010-07-23  

谢谢LZ，遇到这个问题了