Oracle 19C两大表关联查询优化咨询：窗口排序与哈希连接优化

原始查询与数据量 #

SELECT  A.CNACT, A.FACML, A.LCACT, H.CAECH, H.CMECH, H.MCCMP,H.DAHIS,
        RANK() OVER (PARTITION BY H.CNACT,H.CAECH,H.CMECH ORDER BY H.DAHIS DESC) RK
FROM NATACF A,HISTER H 
WHERE A.CNACT = H.CNACT;

表数据量：

• NATACF：74794行
• HISTER：2100720行

核心优化措施 #

1. 构建组合索引消除窗口排序开销 #

针对窗口函数的分区和排序逻辑，在HISTER表上创建覆盖组合索引，让Oracle直接利用索引的有序性计算排名，避免Window Sort的排序开销：

CREATE INDEX IDX_HISTER_RANK ON HISTER(CNACT, CAECH, CMECH, DAHIS DESC)
INCLUDE (MCCMP); -- 包含查询所需的H表其他列，避免回表查询

如果NATACF的CNACT不是主键/唯一键，建议同步创建索引实现覆盖扫描：

CREATE INDEX IDX_NATACF_CNACT ON NATACF(CNACT)
INCLUDE (FACML, LCACT);

2. 优化表连接方式 #

由于NATACF是小表（约7.5万行），HISTER是大表（约210万行），可尝试强制使用嵌套循环连接替代哈希连接（前提是HISTER的CNACT已有索引）：

SELECT /*+ USE_NL(A H) */
       A.CNACT, A.FACML, A.LCACT, H.CAECH, H.CMECH, H.MCCMP,H.DAHIS,
       RANK() OVER (PARTITION BY H.CNACT,H.CAECH,H.CMECH ORDER BY H.DAHIS DESC) RK
FROM NATACF A,HISTER H 
WHERE A.CNACT = H.CNACT;

若哈希连接更适配当前数据分布，需确保表统计信息准确，让CBO自动选择最优策略。

3. 更新表统计信息 #

Oracle优化器依赖准确的统计信息生成执行计划，执行以下语句更新两张表的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的模式名', 'NATACF', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的模式名', 'HISTER', CASCADE => TRUE);

4. 验证优化效果 #

优化后查看执行计划，确认：

• Window Sort是否被替换为基于索引的有序扫描（如INDEX FULL SCAN或INDEX RANGE SCAN）
• 连接操作是否选择了更高效的执行方式

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