Oracle执行计划JOIN机制咨询：嵌套循环与哈希连接共存问题

你的查询看似只有T1和过滤后T2的一个JOIN，但执行计划同时出现两种连接类型，核心原因是Oracle优化器基于统计信息（表数据量、索引情况、分区属性等）选择了混合连接策略，以下是两种最常见的场景及执行流程：

场景1：T2为分区表，采用分区-wise混合连接 #

如果T2是按LAST_UPDATE_DATE（或关联字段）分区的表，优化器会针对时间过滤条件命中的分区做精细化处理：

• 第一步：根据LAST_UPDATE_DATE的范围条件，筛选出T2中符合要求的分区（比如2010-2014年对应的分区）。
• 第二步：对每个筛选出的小数据量分区，采用Nested Loops连接：以T2分区内的ROW_ID为驱动，通过T1的ROW_ID索引（如果存在）快速定位匹配数据——单个分区数据量小，Nested Loops的单次索引查找开销远低于Hash Join的构建成本，效率更高。
• 第三步：将所有分区连接后的结果集，通过Hash Join合并：多个分区的结果集属于独立数据集，Hash Join能高效完成大数据量的合并操作，最终输出统一结果。

场景2：基于数据冷热拆分的混合连接 #

如果T1的数据存在明显的冷热划分（比如热数据有索引覆盖、冷数据无索引），优化器会拆分数据分别处理：

• 第一步：拆分T1数据：根据统计信息，将T1分为两部分——带ROW_ID索引的小体量热数据，以及无索引的大体量冷数据。
• 第二步：Nested Loops处理热数据：以T1热数据为驱动表，通过索引快速匹配T2中符合时间条件的数据，得到第一部分结果。
• 第三步：Hash Join处理冷数据：先将T2中符合时间条件的全部数据构建Hash表，再用T1的冷数据作为探测表，通过Hash匹配得到第二部分结果。
• 第四步：合并两部分结果，输出最终查询数据。

额外说明 #

Oracle优化器的连接策略选择完全依赖准确的统计信息，如果统计信息过时或不准确，可能出现看似不符合预期的混合连接逻辑，此时可以收集最新的表统计信息来验证。

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