Hibernate 6中Oracle row_number()分页效率低，如何改用rownum？

问题背景 #

Hibernate 6默认对Oracle采用row_number()窗口函数实现分页，但在数据量较大（如200万条）时，即便order by字段已建立索引，查询耗时仍高达11秒，效率远不如传统rownum分页。当前使用BlazeJPAQuery的业务代码及生成的低效SQL如下：

业务代码 #

BlazeJPAQuery<SyncMapTaskResponse> blazeJPAQuery = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.fields(SyncMapTaskResponse.class,
                    mapTask.mapTaskId,
                    mapTask.mapId,
                    mapTask.beginTime,
                    mapTask.endTime,
                    mapTask.dataCount,
                    mapTask.status,
                    mapTask.ip,
                    mapTask.insertTime,
                    mapTask.updateTime))
            .from(mapTask)
            .where(builder)
            .orderBy(QSyncMapTask.syncMapTask.beginTime.desc());

        Pageable pageable = paginationRequest.pageable();
        long total = blazeJPAQuery.fetchCount();
        List<SyncMapTaskResponse> list = blazeJPAQuery.offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

生成的低效SQL #

SELECT *
FROM (SELECT smt1_0.map_task_id                                             AS c0
           , smt1_0.map_id                                                  AS c1
           , smt1_0.begin_time                                              AS c2
           , smt1_0.end_time                                                AS c3
           , smt1_0.data_count                                              AS c4
           , smt1_0.status                                                  AS c5
           , smt1_0.ip                                                      AS c6
           , smt1_0.insert_time                                             AS c7
           , smt1_0.update_time                                             AS c8
           , row_number() OVER (ORDER BY smt1_0.begin_time DESC NULLS LAST) AS rn
      FROM t_sync_map_task smt1_0) r_0_
WHERE r_0_.rn <= 50
  AND r_0_.rn > 1
ORDER BY r_0_.rn;

解决方案 #

1. 自定义Oracle方言，强制使用rownum分页 #

Hibernate 6的OracleDialect默认启用窗口函数分页，通过自定义方言覆盖分页处理器即可切换为传统rownum分页：

import org.hibernate.dialect.OracleDialect;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.OracleLegacyLimitHandler;

public class CustomOracleDialect extends OracleDialect {
    @Override
    public LimitHandler getLimitHandler() {
        // 启用Oracle传统rownum分页处理器
        return OracleLegacyLimitHandler.INSTANCE;
    }
}

在Hibernate配置中指定该自定义方言：

# application.properties
spring.jpa.properties.hibernate.dialect=com.yourpackage.CustomOracleDialect

2. BlazeJPAQuery手动适配（方言不生效时备用） #

若自定义方言后仍未生成预期SQL，可手动构建嵌套查询模拟rownum分页逻辑：

Pageable pageable = paginationRequest.pageable();
long offset = pageable.getOffset();
int pageSize = pageable.getPageSize();
long maxRow = offset + pageSize;

// 先构建排序后的基础查询
BlazeJPAQuery<SyncMapTaskResponse> sortedQuery = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
        .select(Projections.fields(SyncMapTaskResponse.class,
                mapTask.mapTaskId,
                mapTask.mapId,
                mapTask.beginTime,
                mapTask.endTime,
                mapTask.dataCount,
                mapTask.status,
                mapTask.ip,
                mapTask.insertTime,
                mapTask.updateTime))
        .from(mapTask)
        .where(builder)
        .orderBy(QSyncMapTask.syncMapTask.beginTime.desc());

// 外层嵌套实现rownum分页
List<SyncMapTaskResponse> list;
if (offset == 0) {
    list = sortedQuery.limit(pageSize).fetch();
} else {
    // 处理offset>0的情况，需两层嵌套
    list = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(Projections.fields(SyncMapTaskResponse.class,
                    "t.*"))
            .from(sortedQuery.limit(maxRow), "t")
            .offset(offset)
            .limit(pageSize)
            .fetch();
}

long total = sortedQuery.fetchCount();

3. 验证预期SQL #

配置完成后，生成的SQL应变为传统rownum嵌套结构：

SELECT *
FROM (
    SELECT smt1_0.map_task_id AS c0, smt1_0.map_id AS c1, ...
    FROM t_sync_map_task smt1_0
    ORDER BY smt1_0.begin_time DESC
)
WHERE rownum <= 50
AND rownum > 1;

注意事项 #

• 用Oracle执行计划（EXPLAIN PLAN）验证order by字段的索引是否被正确命中
• 自定义方言时需注意兼容Hibernate 6的其他特性，避免版本冲突
• 手动构建分页逻辑时，需确保投影字段与实体类属性完全匹配

内容的提问来源于stack exchange，提问作者邹章鹏