Oracle带GROUP BY的查询优化：如何创建最优索引？

最优方案是创建覆盖索引，把查询涉及的所有字段都包含进去，索引创建语句如下：

CREATE INDEX idx_details_optim ON DETAILS (IDPARENT, CDSTATUT, CDTYPE, MT1, MT2);

为什么你之前的索引没被使用？ #

你之前建的(IDPARENT, CDSTATUT, CDTYPE)组合索引属于非覆盖索引——只包含了过滤和分组字段，缺少聚合需要的MT1和MT2。Oracle要是用这个索引，得先通过索引定位符合条件的行，再回表读取MT1、MT2的值。但同一IDPARENT对应700万行数据，回表的IO成本极高，优化器会判定这种方式成本远高于用仅含IDPARENT的索引，所以选了后者。

索引字段顺序的逻辑 #

• 先放首要过滤字段IDPARENT：它是精准匹配的外键，能快速缩小数据范围；
• 接着放第二个过滤字段CDSTATUT，进一步筛选出状态为'O'的行；
• 然后是分组字段CDTYPE：确保同一CDTYPE的行在索引里连续存储，大幅提升分组聚合的效率；
• 最后加MT1和MT2：让索引成为覆盖索引，彻底避免回表操作。

额外提示 #

1. 确保表和索引的统计信息最新，优化器依赖准确统计信息选最优执行计划：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'DETAILS');

2. 虽然CDSTATUT只有2种取值，但先放IDPARENT的效果通常更好——毕竟它是精准匹配，能直接定位到特定父节点的所有行。

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