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设定参数，所以都需要注意。
解决办法(四种解决办法，推荐方案一)：
- sql修改成这样：
select * from table where endDate>to_date(?,’yyyymmddhh24miss’) and endDate <to_date(?,’yyyymmddhh24miss’);
然后将传入参数格式化成对应格式的字符串在传入，这样由Oracle将字符串转成Date类型，就很顺利的走索引区间扫描，效率最高。
当然,你也可以在传入java.util.Date时候手动类型转化为java.sql.Date:
public static java.sql.Date UtilDate2SqlDate(Date utilDate){
return new java.sql.Date(utilDate.getTime());
}
- 在建立数据库连接的时候增加一个属性
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);
连接池也根据各自的配置方式增加这个属性即可。目前看来这个属性参数是处理时间映射关系的，但是还不确定它是否会带来其他的问题，所以要慎重使用。
-
修改数据库列类型为timestamp类型。
-
依据网上资料，Oracle 11g修改了驱动 api，使用方式可能也会有改变。因为无法试验，所以也不确定具体细节。
