为何Oracle查询在Java中运行缓慢，SQL Developer中却快很多？

我的数据库表约有10万条记录，在Oracle SQL Developer中执行以下查询时，获取10万条记录耗时约33秒：

select wflworkflo1_.REF_ID from ( select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE < to_date('20/09/2023' , 'dd/mm/yyyy') UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE < to_date('20/09/2023' , 'dd/mm/yyyy')) wflworkflo1_ WHERE REF_ID NOT IN (SELECT DISTINCT REF_ID FROM (select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE >= to_date('20/09/2023' , 'dd/mm/yyyy') UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE >= to_date('20/09/2023' , 'dd/mm/yyyy')) INT_WFL GROUP BY REF_ID) group by wflworkflo1_.REF_ID having max(wflworkflo1_.START_DATE) < to_date('20/09/2023' , 'dd/mm/yyyy');

但在Java程序中，使用以下方法执行该查询时，返回结果耗时超过1小时：

public List<String> findCurrHistStepRefIdWithStartDate(String targetStartDate)
        throws BocException {
    
    StringBuffer sql = new StringBuffer();
    sql.append(
            "select wflworkflo1_.REF_ID from ( select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE < to_date(?, 'dd/mm/yyyy') UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE < to_date(?, 'dd/mm/yyyy')) wflworkflo1_ WHERE REF_ID NOT IN (SELECT DISTINCT REF_ID FROM (select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE >= to_date(?, 'dd/mm/yyyy') UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE >= to_date(?, 'dd/mm/yyyy')) INT_WFL GROUP BY REF_ID) group by wflworkflo1_.REF_ID having max(wflworkflo1_.START_DATE) < to_date(?, 'dd/mm/yyyy')");
    
    JdbcTemplate template = getJdbcTemplate();
    
    return template.queryForList(sql.toString(), String.class,
            targetStartDate, targetStartDate, targetStartDate, targetStartDate,
            targetStartDate);

}

请问为何会存在如此巨大的性能差异？

1. 绑定变量导致的执行计划差异 #

Oracle SQL Developer中执行的是硬编码日期的SQL，Oracle会针对这个具体日期值生成最优执行计划（比如利用索引快速过滤数据）。而Java代码中使用绑定变量?，如果Oracle的统计信息（尤其是直方图）未及时更新，可能生成通用执行计划，导致索引失效、全表扫描等低效操作，大幅增加耗时。

建议先将字符串日期转为java.sql.Date类型传入，避免在SQL中调用to_date，让Oracle更好地利用索引：

// 字符串转SQL日期
SimpleDateFormat sdf = new SimpleDateFormat("dd/mm/yyyy");
Date date = sdf.parse(targetStartDate);
java.sql.Date sqlDate = new java.sql.Date(date.getTime());

// 修改SQL，移除to_date调用
sql.append("select wflworkflo1_.REF_ID from ( select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE < ? UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE < ?) wflworkflo1_ WHERE REF_ID NOT IN (SELECT DISTINCT REF_ID FROM (select REF_ID, START_DATE from OS_HISTORYSTEP WHERE START_DATE >= ? UNION ALL select REF_ID, START_DATE from OS_CURRENTSTEP WHERE START_DATE >= ?) INT_WFL GROUP BY REF_ID) group by wflworkflo1_.REF_ID having max(wflworkflo1_.START_DATE) < ?");

// 传入SQL日期参数
return template.queryForList(sql.toString(), String.class,
        sqlDate, sqlDate, sqlDate, sqlDate, sqlDate);

2. 结果集加载方式的区别 #

SQL Developer默认分批拉取结果集（比如每次获取100条展示），不会一次性加载全部数据到内存。而JdbcTemplate的queryForList会一次性将10万条记录转为String对象并放入List，消耗大量内存和CPU，导致耗时飙升。

建议改用分批处理方式，避免一次性加载全部数据：

List<String> result = new ArrayList<>();
template.query(sql.toString(), new Object[]{sqlDate, sqlDate, sqlDate, sqlDate, sqlDate},
        (rs) -> {
            while (rs.next()) {
                result.add(rs.getString("REF_ID"));
                // 可选：每处理固定条数做一次内存释放操作
            }
        });
return result;

3. 事务隔离级别与连接配置差异 #

Java程序的JDBC连接可能使用了更高的事务隔离级别（比如REPEATABLE READ），Oracle需要维护更多数据一致性快照，开销远大于SQL Developer默认的READ COMMITTED级别。

可以调整数据源配置，将事务隔离级别设为READ_COMMITTED（以Spring Boot为例）：

spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_COMMITTED

4. 原始SQL的冗余逻辑优化 #

原SQL存在大量重复子查询和to_date调用，核心需求是找出所有REF_ID的最新START_DATE早于目标日期的记录，重写后逻辑更简洁高效：

SELECT REF_ID
FROM (
    SELECT REF_ID, MAX(START_DATE) AS MAX_START_DATE
    FROM (
        SELECT REF_ID, START_DATE FROM OS_HISTORYSTEP
        UNION ALL
        SELECT REF_ID, START_DATE FROM OS_CURRENTSTEP
    ) ALL_STEPS
    GROUP BY REF_ID
) STEP_SUMMARY
WHERE MAX_START_DATE < ?;

这个版本仅需一次合并表、分组取最大日期再过滤，避免了原SQL嵌套的NOT IN子查询，在任何环境下执行性能都会显著提升。

内容的提问来源于stack exchange，提问作者David Holly