Oracle查询隐式类型转换不走索引

1. 框架

mybatis，oracle

2. 问题

昨天DBA发来邮件说有一个SQL引起CPU高负载。查看了一下SQL，根据时间条件查询，时间是有索引的，但是CPU会突然飙高。

SELECT * FROM (
SELECT t1.*,rownum as rn
FROM (
SELECT count(:"SYS_B_0") as COUNT ,T.USER_ID, T.USER_NAME
FROM TEST T
WHERE :"SYS_B_1"=:"SYS_B_2" AND CREATED_TIME >= :4 AND CREATED_TIME <= :5
 group by T.USER_ID,T.USER_NAME ORDER BY COUNT desc ) t1
WHERE rownum <= :6 ) t2
WHERE rn >= :7

然后看一下SQL的执行计划，发现该SQL进行了全表扫描。

可以看到查询的时候使用了过滤器，这就意味着传过来的日期和数据库中的日期格式不对应，可以断定是由于隐式转换导致了时间索引没有用，导致了CPU的飙升。

3. 原因

数据库中的类型是Date类型，传值过去的是Timestamp类型，Java代码里使用的是java.util.Date，配置文件并没有指定类型。那么问题应该就是出在mybaits上了。

mybaits把日期类型直接转化为Timestamp类型，来看下代码：

org.apache.ibatis.type.BaseTypeHandler<T>
  public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
    if (parameter == null) {
      if (jdbcType == null) {
        throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters.");
      }
      try {
        ps.setNull(i, jdbcType.TYPE_CODE);
      } catch (SQLException e) {
        throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
        <span>		</span>"Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " +
        <span>		</span>"Cause: " + e, e);
      }
    } else {
      setNonNullParameter(ps, i, parameter, jdbcType);
    }
  }

org.apache.ibatis.type.DateTypeHandler
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setTimestamp(i, new Timestamp((parameter).getTime()));
  }

org.apache.ibatis.type.DateOnlyTypeHandler
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setDate(i, new java.sql.Date((parameter.getTime())));
  }

这里我们看到日期的handler有两个，一个是Timestamp，一个默认是sql.Date。

经过调试代码，发现日期的默认处理handler是DateTypeHandler，那么传到数据库中的值就变成了Timestamp类型了。

主要原因（摘抄）

在9i以后，11g以前的oracle jdbc中存在date类型丢失时间的bug，原因是其jdbc驱动将oracle的date类型处理为java.sql.date 类型，这就丢失了时间部分；事实上，如果是使用ibatis，pojo属性的类型设置为java.util.date，确保 jdbctype不为 date或者time，则避免了这个bug。因为此时ibatis会以java.sql.timestamp来处理该字段。

总的来说就是时间精度处理引起的问题。

4. 解决方法

如果查询是按照日期来的话，可以在参数中指定jdbcType类型为date。

如果需要更精确的时间查询，可以传入字符串，使用to_date()函数进行转化日期。

http://hidba.org/