性能 – 来自Hibernate App的查询不使用DB索引

select * 
from ( 
    select this.a, this.b, this.state, this.id 
    from view_user this 
    where this.state=:1 order by this.a asc, this.b
) 
where rownum <= :2

哪里

> id是主键
>(a,b,id)上有一个组合的唯一索引.
> view_user有大约200万条目
> view_user对其他表执行一些进一步的连接

问题

以上查询执行
  – 快速从SQLDeveloper
  – 快速从一个带有hibernate的小型Java应用程序
  – 使用hibernate的应用程序非常慢(大约100倍)
  – 绑定变量的值分别为2(来自分页的rownum起源)
  – hibernate查询是上面的“形式”.视图中实际上有大约20列.

目前的分析状况

>查询计划显示当查询来自SQlDeveloper或“小型Java应用程序”时使用索引.
>查询计划显示如果查询来自hibernate应用程序,则执行全表扫描
>数据库跟踪只显示两个不同之处：NLS设置(来自SQLDeveloper)和略有不同的格式(空格).其他一切似乎都是一样的……

版本

>休眠：2.1.8
> jdbc驱动程序：使用ojdbc14,5和6.没有区别
> Oracle：10.2和11.没有任何区别

=>关于这个问题,我很高兴有人可能会提出一些暗示.让我感到困扰的是,DB跟踪没有显示出任何差异…是的,它看起来像是关于休眠的东西.但是什么？怎么检测？

为了完整起见,这里是hibernate查询(来自日志)：

Select * from ( 
    select this.USER_ID as USER_ID0_, this.CLIENT_ID as CLIENT_ID0_, 
    this.USER_NAME as USER_NAME0_, this.USER_FIRST_NAME as USER_FIR5_0_, this.USER_REMARKS as 
    USER_REM6_0_, this.USER_LOGIN_ID as USER_LOG7_0_, this.USER_TITLE as USER_TITLE0_, 
    this.user_language_code as user_lan9_0_, this.USER_SEX as USER_SEX0_, 
    this.USER_BIRTH_DATE as USER_BI11_0_, this.USER_TELEPHONE as USER_TE12_0_, 
    this.USER_TELEFAX as USER_TE13_0_, this.USER_MOBILE as USER_MO14_0_, 
    this.USER_EMAIL as USER_EMAIL0_, this.USER_ADDRESSLINE1 as USER_AD16_0_, 
    this.USER_ADDRESSLINE2 as USER_AD17_0_, this.USER_POSTALCODE as USER_PO18_0_, 
    this.USER_CITY as USER_CITY0_, this.USER_COUNTRY_CD as USER_CO20_0_, 
    this.USER_COUNTRY_NAME as USER_CO21_0_, this.USER_STATE_ID as USER_ST24_0_, 
    this.USER_STATE as USER_STATE0_, this.USER_TEMP_COLL_ID as USER_TE26_0_, 
    this.USER_TEMP_COLL_NAME as USER_TE27_0_, this.UNIT_ID as UNIT_ID0_, 
    this.CLIENT_NAME as CLIENT_38_0_, this.PROFILE_EXTID as PROFILE39_0_
    from VIEW_USER this
    where this.USER_STATE_ID=:1 order by this.USER_NAME asc, this.USER_FIRST_NAME asc
) 
where rownum <= :2

唯一索引是user_name,user_first_name,user_id.

JDBC驱动程序正在将您的参数更改为unicode,因此varchar在到达数据库时会变为nvarchar.如果幸运的话,SQL 2008 SP1会捕获并将其转换回来.但SQL 2000和SQL 2005不会,并且查询优化器将执行全表扫描而忽略您的索引.

您可以通过向连接字符串添加连接参数sendStringParametersAsUnicode = FALSE来在JDBC层修复此问题.