oracle date引入哪个包,oracle 索引字段包含date类型，使用spring jdbc更新时不走索引，而是走table access full的问题...-CSDN博客

环境：

spring jdbc 3.2.3.RELEASE

oracle 11.2.0.1.0 - Production

生产环境存在大表(3000万数据)：WR_MP_HOURW_R，包含复合主键：

mp_cp char(13),

DT date

对应索引名称：WR_MP_HOURW_R_PRI。

问题:针对此表的更新很慢，每条update大概需要2秒。更新的时候使用的where 条件包含mp_cp =? and DT =?

问题分析：

通过AWR 查找到针对此表的更新有大量的物理读，因此判定更新时的执行计划有问题。通过查找对应sql的执行计划，发现没有走索引。具体查找过程如下：

1)找到对应的sql_id：通过select t.SQL_TEXT,t.SQL_ID,t.ADDRESS,t.HASH_VALUE from v$sql t where t.SQL_TEXT like '%WR_MP_HOURW_R%';

2) 根据sql_id查找计划：select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));

果然发现sql没有走索引，而是走的 table access full。正常应该走 INDEX UNIQUE SCAN。具体计划如下：

SQL_ID 16gzsf0ccjjwg, childnumber0-------------------------------------updatesl_szy_mwr_intra .WR_MP_HOURW_RsetMP_CD=:1,SPE_REG_DATA=:2,HOUR_W=:3,DT=:4whereMP_CD=:5andDT=:6

Planhash value:3498191616

-------------------------------------------------------------------------------------|Id|Operation|Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time|-------------------------------------------------------------------------------------|0|UPDATESTATEMENT||||35061(100)|||1|UPDATE|WR_MP_HOURW_R||||||*2|TABLEACCESSFULL|WR_MP_HOURW_R|21|504|35061(1)|00:07:01|-------------------------------------------------------------------------------------Query Block Name/Object Alias (identifiedbyoperation id):

-------------------------------------------------------------1-UPD$12-UPD$1/WR_MP_HOURW_R@UPD$1

Outline Data

-------------

/**//*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"UPD$1")

FULL(@"UPD$1" "WR_MP_HOURW_R"@"UPD$1")

END_OUTLINE_DATA

Peeked Binds (identifiedbyposition):

--------------------------------------5-(VARCHAR2(30), CSID=852):'2108811006101'

Predicate Information (identifiedbyoperation id):

---------------------------------------------------2-filter(("MP_CD"=:5ANDINTERNAL_FUNCTION("DT")=:6))

ColumnProjection Information (identifiedbyoperation id):

-----------------------------------------------------------2-(upd=2,3,4,5; cmp=2,3; cpy=2,3) "WR_MP_HOURW_R".ROWID[ROWID,10],

"MP_CD"[CHARACTER,13], "DT"[DATE,7], "HOUR_W"[NUMBER,22],

"SPE_REG_DATA"[CHARACTER,1]

Note

------Warning: basicplanstatisticsnotavailable. These areonlycollectedwhen:

*hint'gather_plan_statistics'isusedforthe statementor*parameter'statistics_level'issetto'ALL', at sessionorsystemlevel

===SQL_ID 62mars8u2ysj1, childnumber0-------------------------------------updatesl_szy_mwr_intra .WR_MP_HOURW_RsetMP_CD='2201050002001', SPE_REG_DATA='0', HOUR_W=1368.0,

DT=to_date('06-05-2015 06:00:00','dd-mm-yyyy hh24:mi:ss')

whereMP_CD='2201050002001'andDT=to_date('06-05-2015

06:00:00','dd-mm-yyyy hh24:mi:ss')

Planhash value:234794540

首先考虑oracle是否没有搜集表的统计信息。查看属性发现表和索引的统计都较新(oracle 基本上一个小时会收集一次，以保证执行计划是最优的)。

通过hint 处理强制走索引：/*+ INDEX(WR_MP_HOURW_R WR_MP_HOURW_R_PRI)*/ 。发现走了INDEX SKIP SCAN。

此时说明复合索引有一个字段oracle认为不在条件中或者是经过了转换。使得oracle只走了复合索引的其中一个字段。

由于我们的update条件是复合索引的两个字段都在where条件里面，所以很大可能是发生了字段类型转换。

其实从上文件指出计划中也可以发现，出现类型转换 INTERNAL_FUNCTION("DT") 。

如果oracle在索引字段发现有类型转换(如数据库是date，但是传入的是timestemp)oralce将不走索引。

当然如果是复合索引，oracle有可能会走 INDEX FAST FULL SCAN或者INDEX SKIP SCAN。然是如果数据量很大，索引全部扫描也很费时间。必须要走INDEX UNIQUE SCAN才能保证效率。

在发现了发生数据类型转换后，就只能从程序下手找问题，看是否传入的值有问题。

通过查询spring源码，发现：

类：org.springframework.jdbc.core.StatementCreatorUtils的271行开始为最终调用jdbc驱动来通过PreparedStatement设置值的地方：

第346行，如果我们在传参数的时候，没有指定对应在数据库要映射什么类型是，spring帮我们做了处理，如下

//这里说明我们没有指定要映射到数据库的什么类型

elseif(sqlType==SqlTypeValue.TYPE_UNKNOWN){

if(isStringValue(inValue.getClass())){

ps.setString(paramIndex, inValue.toString());

}//看这里，所有java.util.Date，java.util.Date，java.sql.Date，java.sql.Timestamp都被用了ps.setTimestamp处理了，这就是根本原因

elseif(isDateValue(inValue.getClass())){

ps.setTimestamp(paramIndex,newjava.sql.Timestamp(((java.util.Date) inValue).getTime()));

}

elseif(inValueinstanceofCalendar){

Calendar cal=(Calendar) inValue;

ps.setTimestamp(paramIndex,newjava.sql.Timestamp(cal.getTime().getTime()), cal);

}

else{

//Fall back to generic setObject call without SQL type specified.ps.setObject(paramIndex, inValue);

} }

privatestaticbooleanisDateValue(Class inValueType){

return(java.util.Date.class.isAssignableFrom(inValueType)&&!(java.sql.Date.class.isAssignableFrom(inValueType)|| java.util.Date.class.isAssignableFrom(inValueType)|| java.sql.Timestamp.class.isAssignableFrom(inValueType)));

}

找到问题之后如何解决：

spring为我们提供了SqlParameterValue或者SqlParameter供我们包装：

如果字段是date类型，我们传入java.util.date 此时需要封装成new SqlParameterValue(Types.TIME, value);

当然这里不能用Types.DATE 因为如果用Types.DATE最终会被转换后为java.sql.date，将会丢失时分秒。

如果字段是timestemp类型，我们传入java.util.date，此时需要封装成new SqlParameterValue(Types.TIMESTAMP, value);

这样最终oracle就不会出现数据类型转换。

修改之后再查询执行计划，oracle 顺利的走了INDEX UNIQUE SCAN。效率立即从更新一条好几秒变为瞬时。具体计划如下：

SQL_ID 62mars8u2ysj1, childnumber0-------------------------------------updatesl_szy_mwr_intra .WR_MP_HOURW_RsetMP_CD='2201050002001', SPE_REG_DATA='0', HOUR_W=1368.0,

DT=to_date('06-05-2015 06:00:00','dd-mm-yyyy hh24:mi:ss')

whereMP_CD='2201050002001'andDT=to_date('06-05-2015

06:00:00','dd-mm-yyyy hh24:mi:ss')

Planhash value:234794540

-----------------------------------------------------------------------------------------|Id|Operation|Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time|-----------------------------------------------------------------------------------------|0|UPDATESTATEMENT||||3(100)|||1|UPDATE|WR_MP_HOURW_R||||||*2|INDEXUNIQUESCAN|WR_MP_HOURW_R_PRI|1|24|2(0)|00:00:01|-----------------------------------------------------------------------------------------Query Block Name/Object Alias (identifiedbyoperation id):

-------------------------------------------------------------1-UPD$12-UPD$1/WR_MP_HOURW_R@UPD$1

Outline Data

-------------

/**//*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"UPD$1")

INDEX(@"UPD$1" "WR_MP_HOURW_R"@"UPD$1" ("WR_MP_HOURW_R"."DT"

"WR_MP_HOURW_R"."MP_CD"))