ID与ID_version分栏表索引优化异常解析及替代方案咨询

问题场景 #

我有一张包含四列的表：ID、ID_version、name、address。一个ID对应多个ID_version，当前示例中有3个ID值，其中一个ID有两个ID_version，总计存在4种组合：1-1、1-2、2-1、3-1，每种组合的行数大致相同。

查询语句为：

select address from table where ID=1 and ID_version=1;

我尝试了三种索引策略，结果差异明显：

• 策略1：创建包含所有列的索引组织表，列顺序为ID、ID_version、name、address，执行计划显示快速全扫描，基数极高
• 策略2：调整索引顺序为ID_version、ID、name、address，执行计划显示范围扫描，但基数仍然很高
• 策略3：将ID与ID_version合并为一个字符串列（表变为3列），采用类似策略1的索引组织表，执行计划显示范围扫描，基数和一致性读数值远低于前两种方案

请问：

1. 为什么会出现这种差异？
2. 当ID与ID_version为独立列时，是否存在其他能实现低基数、低一致性读的索引策略？

现象解释 #

策略1（ID、ID_version开头的索引组织表） #

索引组织表出现快速全扫描，核心原因是优化器判断全扫描成本更低，或是统计信息不准确：

• 你的数据集中ID唯一值仅3个，优化器可能认为ID=1的过滤性极差，即便加上ID_version=1，也觉得全扫整个索引段更划算
• 快速全扫描会遍历整个索引结构，因此基数等于表的总行数，一致性读自然居高不下

策略2（ID_version、ID开头的索引） #

ID_version的唯一值只有2个（1和2），作为索引首列时，ID_version=1仅能过滤掉一半数据，剩下的结果包含ID=1、ID=2、ID=3的所有行，优化器计算的基数是ID_version=1的总行数，所以基数依然很高。同时索引顺序与查询过滤逻辑（先ID后ID_version）不匹配，无法精准定位目标组合，导致范围扫描的范围过大。

策略3（合并列的索引） #

合并后的字符串列（比如ID_ver_str = concat(ID, '-', ID_version)）有4个唯一值，每个值对应一组唯一行组合。查询时where ID_ver_str = '1-1'是对高选择性列的精准匹配，优化器可以直接通过索引范围扫描定位到目标数据块，基数就是该组合的行数，一致性读因此大幅降低。

独立列的替代优化策略 #

当ID和ID_version作为独立列时，可通过以下方式实现低基数和低一致性读：

• 创建覆盖型复合索引，列顺序为ID、ID_version，包含address：

  create index idx_id_version_addr on table(ID, ID_version) include(address);
  

  这种索引完全匹配查询的过滤顺序，且能直接从索引中获取address无需回表，优化器可精准定位到ID=1 and ID_version=1的行，基数等于该组合的行数，一致性读会显著降低。
• 更新统计信息：若之前执行计划异常是因统计信息过时，执行以下命令更新表和索引的统计信息：

  exec dbms_stats.gather_table_stats(ownname => '你的用户名', tabname => '表名', cascade => true);
  

  准确的统计信息能让优化器正确判断ID=1 and ID_version=1的过滤性，选择最优的索引范围扫描。
• 强制索引提示：若优化器仍选择全扫描，可在查询中添加索引提示强制使用复合索引：

  select /*+ index(table idx_id_version_addr) */ address from table where ID=1 and ID_version=1;
  

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