Oracle VARCHAR2列求和查询性能劣化原因及优化方案咨询

问题场景 #

需要对如下示例表的C2列求和：

当前使用的查询语句：

SELECT NVL(SUM(C2),0)
FROM table
WHERE C3 = 'A'
AND C4 = 1 
AND C1 <> 'LG8';

数据量较小时查询速度快，但数据量增长后，通过TkProf分析发现该查询耗时占比极高。目前C3、C4、C1列均建有非唯一索引，但执行计划仅对C4列使用索引，未利用其他索引。

耗时原因分析 #

• 单索引选择性不足：如果C4=1的记录在表中占比很高，仅用C4索引扫描后需要大量回表操作，再过滤C3='A'和C1<>'LG8'，回表的IO开销会随着数据量增长急剧上升。
• 统计信息过时：数据库优化器依赖最新的统计信息评估索引成本，如果统计信息未及时更新，优化器可能无法判断组合索引的效率，从而选择了单索引执行计划。
• 不等值条件无法高效利用索引：C1 <> 'LG8'是不等值过滤，单独的C1索引对该条件的支持性很差，无法通过索引快速排除这条记录，只能在回表后逐行过滤。

优化方案 #

1. 创建组合覆盖索引 #

这是最有效的优化手段，创建包含过滤条件和查询字段的组合索引，避免回表操作：

-- Oracle 12c及以上版本，使用INCLUDE包含C2列（不参与索引排序，减少索引大小）
CREATE INDEX idx_table_c4c3c1_c2 ON table (C4, C3, C1) INCLUDE (C2);

-- Oracle 12c以下版本，将C2加入索引列
CREATE INDEX idx_table_c4c3c1_c2 ON table (C4, C3, C1, C2);

组合索引的顺序优先选择过滤后结果集最小的列（比如先C4，再C3，最后C1），这样能快速缩小扫描范围，同时覆盖C2列，直接在索引中完成求和计算，无需回表。

2. 验证并移除冗余条件 #

检查LG8这条记录是否满足C3='A' AND C4=1：如果LG8的C3不是'A'或者C4不是1，那么C1 <> 'LG8'这个条件完全多余，可以直接删除，减少过滤步骤。

3. 更新统计信息 #

确保数据库统计信息最新，让优化器能准确评估索引成本：

-- Oracle环境下更新表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', '表名');

4. 临时强制使用索引（不推荐优先使用） #

如果优化器仍未选择组合索引，可以使用hint强制指定，但这是临时方案，优先保证统计信息准确和索引合理：

SELECT /*+ INDEX(table idx_table_c4c3c1_c2) */ NVL(SUM(C2),0)
FROM table
WHERE C3 = 'A'
AND C4 = 1 
AND C1 <> 'LG8';

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