一个Date类型的ibatis查询走不上索引的问题

        实际工作中，发现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....');