Does indexes work with group function in oracle?

Question

I am running following query.

SELECT Table_1.Field_1,
           Table_1.Field_2,
           SUM(Table_1.Field_5) BALANCE_AMOUNT
      FROM Table_1, Table_2
     WHERE Table_1.Field_3 NOT IN (1, 3)
       AND Table_2.Field_2 <> 2
       AND Table_2.Field_3 = 'Y'
       AND Table_1.Field_1 = Table_2.Field_1
       AND Table_1.Field_4 = '31-oct-2011'
     GROUP BY Table_1.Field_1, Table_1.Field_2;

I have created index for columns (Field_1,Field_2,Field_3,Field_4) of Table_1 but the index is not getting used.

If I remove the SUM(Table_1.Field_5) from select clause then index is getting used.

I am confused if optimizer is not using this index or its because of SUM() function I have used in query.

Please share your explaination on the same.

Answer 1

When you remove the SUM you also remove field_5 from the query. All the data needed to answer the query can then be found in the index, which may be quicker than scanning the table. If you added field_5 to the index the query with SUM might use the index.