按字段创建索引？ [英] Creating Indexes for Group By Fields?

问题描述

您是否需要为Oracle数据库中的分组字段创建索引？

Do you need to create an index for fields of group by fields in an Oracle database?

例如：

select * 
from some_table
where field_one is not null and field_two = ?
group by field_three, field_four, field_five

我正在测试我为上面创建的索引此查询的唯一相关索引是为field_two创建的索引。在任何其他字段上创建的其他单字段或复合索引将不会用于上述查询。这听起来是否正确？

I was testing the indexes I created for the above and the only relevant index for this query is an index created for field_two. Other single-field or composite indexes created on any of the other fields will not be used for the above query. Does this sound correct?

推荐答案

这可能是正确的，但这取决于您拥有多少数据。通常我会为我在GROUP BY中使用的列创建一个索引，但是在你的情况下，优化器可能已经决定在使用field_two索引之后返回的数据不足以证明使用GROUP的另一个索引是合理的BY。

It could be correct, but that would depend on how much data you have. Typically I would create an index for the columns I was using in a GROUP BY, but in your case the optimizer may have decided that after using the field_two index that there wouldn't be enough data returned to justify using the other index for the GROUP BY.

这篇关于按字段创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！