Java中Oracle时间索引的使用

发布于 2022年 05月 19日 18:24

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,使用方式可能也会有改变。因为无法试验,所以也不确定具体细节。

推荐文章