Java中Oracle时间索引的使用

Oracle中我们经常使用Date字段类型记录日期和时间，有的时候还在这个字段上建立索引。

然后通过Java程序访问数据库的时候，我们很自然的类似这样使用：

select * from table where endDate > ? and endDate < ?

然后通过PreparedStatement预编译，再通过setTimestamp传入由java.util.Date转成java.sql.Timestamp的参数(因为java.sql.Date只有日期，java.sql.Time只有时间，所以我们只能用java.sql.Timestamp类型)。我们会认为这样应该走索引区间扫描，效率应该是非常高的。

而事实上，Oracle会把sql解释成如下这样来执行：

select * from table where TO_TIMESTAMP(endDate) > ? and TO_TIMESTAMP(endDate) < ?; 

为什么？因为传入的参数是timestamp类型，Oracle从9.2版本以后支持这种类型，所以Oracle做了这样的转换，结果就是这个SQL执行变成了全表扫描。

我们做的试验，加了一个index hint，强制走时间索引字段，结果效率也不高，sql执行变成了全索引扫描，和全表扫描没多大区别。结果效率还是低。

不光直接使用JDBC会是这样，Spring，iBatis在处理传入参数是java.util.Date类型的时候，都会使用setTimestamp设定参数，所以都需要注意。

解决办法(四种解决办法，推荐方案一)：

1. sql修改成这样：

   select * from table where endDate>to_date(?,’yyyymmddhh24miss’) and endDate <to_date(?,’yyyymmddhh24miss’);

   然后将传入参数格式化成对应格式的字符串在传入，这样由Oracle将字符串转成Date类型，就很顺利的走索引区间扫描，效率最高。

2. 在建立数据库连接的时候增加一个属性oracle.jdbc.V8Compatible=true，代码如下：

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

   连接池也根据各自的配置方式增加这个属性即可。目前看来这个属性参数是处理时间映射关系的，但是还不确定它是否会带来其他的问题，所以要慎重使用。

3. 修改数据库列类型为timestamp类型。

4. 依据网上资料，Oracle 11g修改了驱动 api，使用方式可能也会有改变。因为无法试验，所以也不确定具体细节。