实际工作中,发现Date类型作为条件查询走不上索引的问题,由于问题完全和http://blog.csdn.net/zldeng19840111/article/details/6721589一样,为简便起见,直接采用它的实例说明。
以下为简化后的场景:通过时间范围作一个邮件发送数量的统计
java:
import java.util.Date; public List<Object> listRecentTaskInfoByStatus( Date start, Date end,String sendType) { Map<String,String> paraMap = new HashMap<String,String>(); paraMap.put(TASK_STAT_START_TIME, start); paraMap.put(TASK_STAT_END_TIME, end); return defaultDao.getObjList(nameSpace.get(sendType), paraMap, "CHANNEL_RECENT_STAT"); }
ibatis:
<select id="SELECT_CHANNEL_RECENT_STAT" resultMap="MtnPlanSendlog_RM_CHANNEL_TASK" parameterClass="java.util.HashMap"> <![CDATA[ SELECT /*+index (a SENDLOG_GSEND_IND)*/ overview_id, count(*) as tmp_count FROM MTN_PLAN_SENDLOG a WHERE is_deleted = 'n' and overview_id is not null and GMT_SEND >= #startTime# and GMT_SEND < #endTime# GROUP BY overview_id ]]> </select>
现象:
sql查询缓慢,DBA观察发现没有走上GMT_SEND的索引
原因:
由于需要小时分秒的信息,我们使用的是java.util.Date,观察发现在数据库端有类似如下函数转换
TO_TIMESTAMP(date_column) = parameter_timestamp
导致纵然加了hit也走不上索引。
为什么会触发oracle做隐式转换呢?原因是ibatis在处理传入到数据库的变量时候,如果为java.util.date类型则会自动解析为timestamp类型。具体原因可以参考java.sql.PreparedStatement和相关文档,在此不详述。
public void setParameter(PreparedStatement ps, int i, Object parameter, String jdbcType) throws SQLException { ps.setTimestamp(i, new java.sql.Timestamp(((Date) parameter).getTime())); }
JAVA传下去的是Timestamp,如果数据库中时间字段是date类型,由于timestamp类型的精度比date类型的精度高,根据oracle的策略,所以oracle会对数据库里的date类型做强制隐身转换,将date类型转换为timestamp类型,由于加了函数,那么创建在date类型上的索引将不会使用到;(好像是oracle9i后开始才有的问题)。
解决方案:
经过网上查询,认为可选且比较靠谱的解决方案主要有三种:
a.将数据库的列改为timestamp(风险较太大)
b.使用to_date('2011-03-08 15:45:43.123','yyyy-mm-dd HH24:MI:SS....')
to_date(to_char(#startTime#,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
c.将传入参数由java.util.Date转换为String,然后在ibatis端使用to_date函数解决(改动点太多)
and GMT_SEND >= to_date(#startTime#,'YYYY-MM-DD HH24:MI:SS')
d.给GMT_SEND字段建函数索引(为了走索引而走索引,不是正常的解决问题的办法)
e.cast函数转成Date类型,最终是采用这种方案,对现有程序功能无影响且直接解决问题
and GMT_SEND >= cast(#startTime# as date)
附加知识说明:
table_name表的updated_date字段是DATE类型,并建立了索引
select * from table_name t where t.updated_date > to_timestamp('2015-12-01 01:20:30.123','YYYY-MM-DD HH24:MI:SS.ff');
执行计划是全表扫描的
而如下SQL:
select * from table_name t where t.updated_date > to_date('2015-12-01 01:20:30.123','yyyy-mm-dd HH24:MI:SS....'); select * from table_name t where t.updated_date > cast(to_timestamp('2015-12-01 01:20:30.123','YYYY-MM-DD HH24:MI:SS.ff') as date); select * from table_name t where t.updated_date > to_date(to_char(to_timestamp('2015-12-01 01:20:30.123','YYYY-MM-DD HH24:MI:SS.ff'),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
执行计划是走索引的
同理,如下SQL由于给t.updated_date多加了一个trunc,也会导致用不上索引而走全表扫描。
select * from table_name t where trunc(t.updated_date) > trunc(to_date('2015-12-01 01:20:30.123','yyyy-mm-dd HH24:MI:SS....'));
可修改为:
select * from table_name t where t.updated_date > trunc(to_date('2015-12-01 01:20:30.123','yyyy-mm-dd HH24:MI:SS....')); select * from table_name t where t.updated_date > to_date('2015-12-01 01:20:30.123','yyyy-mm-dd HH24:MI:SS....');
相关推荐
Ibatis查询Id列表.doc
操作数据库 iBATIS查询,java 和spring的配置方法
Ibatis多表查询
ibatis的的增删改查和一对一、一对多查询 ibatis的的增删改查和一对一、一对多查询 ibatis的的增删改查和一对一、一对多查询 完成的项目
Ibatis多表查询,一个小小的多表查询实例教你如何用ibatis进行多表查询
ibatis学习 ibatis总结 ibatis ibatis ibatis
Ibatis复杂查询语句.doc
ibatis ,入门例子
Ibatis查询语句里,可以使用多表查询,返回多个表的值.doc
ibatis的多参数查询.doc
ibatis动态多条件组合查询 实例 说明
ibatis in action的文档,详细介绍ibatis的查询等
ibatis资料ibatis资料ibatis资料ibatis资料ibatis资料ibatis资料ibatis资料ibatis资料ibatis资料
这个文件中包含了许多ibatis的查询语句,能够让你很清楚的知道,如何实现动态的查询。
下面我们看一个最简单的入门例子,是《ibatis 开发指南》上的例子改的,不过上面讲的不仔细,我开始学的时候搞了一个晚上才把那个例子跑起来的,相信一些朋友也和我一样,在入门的时候有一点小郁闷,我把整个工程...
ibatis_动态查询条件详解及需要注意的地方
ibatis 读取oracle clob类型
ibatis and和or联合查询 .doc
这一章就来回答这个问题。 iBATIS是一种data mapper。Martin Fowler在他的《Patterns of Enterprise Application Architecture》一书中是这样描述Data Mapper的: 一个映射层,在对象和数据库间传递数据,并保持两者...