Oracle 19c哈希连接中分区列索引扫描成本低于全分区访问原因咨询

核心前提：你对两种访问路径的读取块数判断有误 #

两种路径读取的P表数据块数量并不相等，成本差异完全来自P表访问开销的差距，和C表无关（两份执行计划中C表的访问成本均为539K，无变化）：

• 走索引路径时，P侧总开销为索引范围扫描的231 + 回表的1642 = 1873
• 禁用索引全扫P表时，P侧总开销为8152，比索引路径高6000+，刚好对应总执行成本7K的差值

具体合理性可分为三点解释 #

1. 全分区扫描的实际读取块数远高于索引回表
   P表是复合分区结构，cat为一级分区键，单个cat分区下还有316个二级子分区。全分区扫描需要读取该一级分区下所有高水位线以下的块，包括预留空间块、已删除行留下的空块、半满块，即使这些块中没有有效数据也会被读取。而索引范围扫描+批量回表的路径，会先通过仅占231个块的索引拿到所有符合条件的6万多行的ROWID，排序后只读取实际存储了这些行的数据块，避免了大量无效块的读取。
2. ROWID批量回表的开销极低
   Oracle 11g之后引入的TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED特性，会将拿到的ROWID按物理存储位置排序，尽量连续读取数据块，随机IO的开销被压到最低。如果索引IX_P_CAT的聚簇因子较低（接近表的块数），回表的IO开销几乎和连续读一致。
3. 优化器成本计算基于准确的统计信息
   你已经确认表和索引都完成了统计信息收集，优化器的成本计算是基于实际的存储参数（如聚簇因子、块使用率、高水位线等）得出的结论，1280K的成本确实比1287K更低，符合实际执行效率。

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