Oracle复合连接谓词导致优化器行数估算偏差两个数量级如何解决

该问题根因为Oracle优化器默认对多字段等值关联+IN列表谓词的场景，会采用平均选择性估算逻辑，不会叠加多列统计信息的组合选择性计算，因此出现两个数量级的估算偏差，可通过以下方案解决：

• 调整动态采样级别：将会话或全局参数optimizer_dynamic_sampling设置为4及以上，硬解析时优化器会自动采样A表匹配IN列表的实际数据量，推导正确的关联基数，由于A表仅1万行，采样开销可忽略。
• 修改IN列表展开阈值：设置隐含参数_optimizer_in_list_to_or_expansion_threshold为0，强制优化器将所有IN列表展开为OR条件组合，结合你已经创建的(num,val)多列扩展统计信息，可准确计算每个IN值组合的选择性之和，修正估算偏差。
• 使用HINT手动修正基数：无需调整参数的场景下，可在SQL中添加/*+ OPT_ESTIMATE(JOIN A C SCALE_ROWS=100) */ HINT，直接将关联步骤的估算行数放大100倍，对齐你当前两个数量级的偏差，也可通过/*+ CARDINALITY(A 实际匹配行数) */直接指定A表过滤后的行数。
• 重构IN列表为临时表关联：将IN列表中的num、val取值预先存入临时表，用临时表关联A表替代IN列表谓词，优化器会基于临时表的统计信息做标准关联基数计算，估算准确率更高，适合IN列表长度较长的场景。

注意：隐含参数调整建议先在会话级别测试，确认不影响其他业务SQL运行后再考虑全局生效。

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