Oracle查询优化：筛选egno重复且dsver不重复的数据

嘿，我来帮你捋捋这个问题～首先咱们先明确你的核心需求：要从test表中找出那些egno值存在重复（即同一个egno至少出现两次），同时dsver值是唯一的（即这个dsver在表里只出现一次）的记录。

你的原MySQL语句在Oracle里跑不通，核心原因是Oracle对GROUP BY的语法要求更严格——MySQL在某些宽松模式下允许SELECT *和GROUP BY的列不匹配，但Oracle完全遵循SQL标准，必须保证SELECT中的非聚合列都出现在GROUP BY子句里，所以原语句直接报错。

你改成的双IN子查询逻辑上是对的，但确实可能存在性能瓶颈：如果表数据量大，两次独立的子查询大概率会触发全表扫描（没有合适索引的话），资源消耗会比较高。下面给你两个更高效的优化方案：

方案1：使用窗口函数（首推） #

Oracle支持窗口函数，这是处理这类聚合过滤需求的最优解，只需要扫描表一次就能计算出所需的聚合值，效率比双IN子查询高很多：

SELECT t.*
FROM (
    SELECT 
        test.*,
        -- 计算当前egno在表中的总出现次数
        COUNT(*) OVER (PARTITION BY egno) AS egno_count,
        -- 计算当前dsver在表中的总出现次数
        COUNT(*) OVER (PARTITION BY dsver) AS dsver_count
    FROM test
) t
WHERE t.egno_count > 1  -- 筛选egno重复的记录
  AND t.dsver_count = 1; -- 筛选dsver唯一的记录

为什么这个方案更优？ #

• 仅需对test表做一次扫描（全表或索引扫描），扫描过程中同时计算每个egno和dsver的出现次数，避免了两次独立的子查询扫描，减少了IO开销。
• 逻辑非常直观，直接对应你的需求，可读性强，后期维护也方便。

方案2：优化双IN子查询（兼容传统写法） #

如果你更习惯子查询的写法，可以通过索引+EXISTS来优化性能：

第一步，先给test表创建两个针对性的索引，加速子查询的查找：

CREATE INDEX idx_test_egno ON test(egno);
CREATE INDEX idx_test_dsver ON test(dsver);

第二步，把IN替换成EXISTS（在Oracle中，EXISTS通常比IN性能更好，尤其是子查询结果集较大时，因为EXISTS是半连接，找到匹配项就停止扫描）：

SELECT t.*
FROM test t
WHERE EXISTS (
    SELECT 1 
    FROM test 
    WHERE egno = t.egno 
    GROUP BY egno 
    HAVING COUNT(*) > 1
)
AND EXISTS (
    SELECT 1 
    FROM test 
    WHERE dsver = t.dsver 
    GROUP BY dsver 
    HAVING COUNT(*) = 1
);

优化点说明 #

• 新增的索引可以让子查询快速定位到对应的egno和dsver记录，避免全表扫描。
• EXISTS的半连接特性，相比IN的全量匹配，能减少不必要的扫描次数，提升查询速度。

额外小建议 #

如果你的test表数据量极大，可以做这些补充优化：

• 定期清理无效数据，缩小表的扫描范围。
• 用EXPLAIN PLAN FOR加上你的SQL语句，查看执行计划，确认索引是否被正确使用，有没有全表扫描的情况，再针对性调整。

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