怎样让日期范围走索引
发布网友
发布时间：2022-05-23 03:20
共1个回答
热心网友
时间：2023-10-11 17:01
然后通过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，使用方式可能也会有改变。因为无法试验，所以也不确定具体细节。
