Oracle索引列子集查询性能提升及多列索引优化疑问

1. 查询Oracle表时使用索引列的子集是否能提升性能？ #

不一定，核心取决于索引的列顺序和查询条件的匹配方式：

• 如果使用的是索引的前缀列子集（例如索引定义为(col1, col2, col3)，查询过滤/连接条件用到col1或col1+col2），Oracle可以执行index range scan（索引范围扫描），相比全表扫描，当返回数据量较小时性能会明显提升。
• 如果使用的是非前缀的列子集（例如索引是(col1, col2, col3)，查询仅用col2），Oracle无法有效利用该索引，此时性能和全表扫描无异，甚至可能更差（额外的索引块读取开销）。
• 另外，数据分布也会影响：若查询返回行数占表总行数比例过高（比如超过30%），Oracle可能直接选择全表扫描，即便能用前缀子集索引，性能提升也不显著。

2. 关于6列唯一索引的两个子问题 #

2.1 当查询仅基于其中3列进行连接时，该唯一索引是否能起到作用？ #

取决于这3列是否是唯一索引的前缀列：

• 若是前缀列（例如唯一索引为(col1, col2, col3, col4, col5, col6)，连接条件用col1+col2+col3），Oracle可以利用该索引快速定位匹配行，索引能正常发挥作用。
• 若不是前缀列（例如连接条件用col4+col5+col6），该唯一索引无法被有效利用，Oracle大概率会选择全表扫描或其他合适的索引。

2.2 创建仅包含这3列的非唯一索引是否能进一步提升性能？ #

通常会有性能提升，原因如下：

• 仅3列的非唯一索引体积更小，Oracle读取索引块的IO开销更低，扫描速度更快，避免了读取6列索引中额外的3列数据。
• 唯一索引需要维护唯一性约束，写入时开销略高于非唯一索引；但对于查询场景，更小的索引意味着更快的定位速度。
• 例外情况：如果这3列的基数极低（比如重复率极高的枚举列），查询返回行数过多，Oracle可能仍选择全表扫描，此时新索引的性能提升不明显。

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