兄弟，我来帮你搞定这个1亿级Oracle表的查询优化难题！你的原查询跑几小时没结果，核心问题是子查询里的全表扫描+窗口函数的高开销，咱们一步步拆解优化：

一、先搞懂原查询的低效根源 #

你的原语句是先对全8000万条记录执行ROW_NUMBER()窗口函数，再过滤row_num=1和organization!=3——相当于先全量计算再筛选，这种逻辑完全浪费了前置过滤的机会，自然慢到离谱。

二、针对性优化方案 #

1. 把过滤条件提前，大幅减少窗口函数处理量 #

先筛掉organization=3的数据，再执行窗口函数，直接砍掉一部分不需要处理的行：

SELECT organization
FROM (
    SELECT 
        organization,
        ROW_NUMBER() OVER (PARTITION BY empno, sal ORDER BY deptno DESC) AS row_num
    FROM emp
    WHERE organization != 3 -- 提前过滤，减少窗口函数的处理行数
) x 
WHERE x.row_num = 1

这一步能直接降低窗口函数的计算基数，效果立竿见影。

2. 创建精准的组合索引，彻底避免全表扫描 #

你之前建的索引没踩中核心逻辑，应该创建**(organization, empno, sal, deptno)**的组合索引，原因如下：

• 第一个字段organization：快速定位!=3的数据集，直接跳过全表扫描
• 后续empno, sal：作为窗口函数的分区键，索引本身的有序性能让Oracle直接按分区分组，不用额外排序
• 最后deptno：是窗口函数的排序字段，包含在索引里后，排序操作可以直接利用索引的有序性，省去排序开销

如果Oracle优化器还是选择全表扫描（比如organization=3的数据占比极低），可以用索引提示强制走索引：

SELECT organization
FROM (
    SELECT 
        organization,
        ROW_NUMBER() OVER (PARTITION BY empno, sal ORDER BY deptno DESC) AS row_num
    FROM emp /*+ INDEX(emp idx_org_emp_sal_dept) */
    WHERE organization != 3
) x 
WHERE x.row_num = 1

（注：idx_org_emp_sal_dept是你创建的组合索引名）

3. 用聚合函数替代窗口函数，给优化器更多空间 #

如果业务逻辑允许，试试用KEEP聚合函数替代ROW_NUMBER()，Oracle对这种语法的优化往往更高效：

SELECT 
    DISTINCT empno,
    sal,
    MAX(organization) KEEP (DENSE_RANK FIRST ORDER BY deptno DESC) AS organization
FROM emp
WHERE organization != 3
GROUP BY empno, sal

这种写法避免了子查询嵌套，优化器可以直接利用索引做分组和排序，性能可能比窗口函数更好。

4. 更新统计信息，让优化器选对执行计划 #

1亿级别的表如果统计信息过时，Oracle优化器会做出错误的执行计划判断。执行以下命令更新表和索引的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'EMP', CASCADE => TRUE, ESTIMATE_PERCENT => 10);

ESTIMATE_PERCENT =>10表示用10%的样本量统计，既准确又不会耗时太久。

5. 考虑分区表（如果还没做分区） #

如果emp表还没分区，建议按organization或者deptno做分区。比如按organization范围分区，把organization=3的单独放在一个分区里，查询时Oracle会直接跳过这个分区，大幅减少IO开销。

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