Creating Indexes for Group By Fields?

Question

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

For example:

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

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?

Answer 1

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.