Does indexes work with group function in oracle?

I am running following query.

SELECT Table_1.Field_1,
           Table开发者_如何学Python_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.

---

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.

---

If your query is returning the large percentage of table's rows, Oracle may decide that doing a full table scan is cheaper than "hopping" between the index and the table's heap (to get the values in Table_1.Field_5).

Try adding Table_1.Field_5 to the index (thus covering the whole query with the index) and see if this helps.

See the Index-Only Scan: Avoiding Table Access at Use The Index Luke for conceptual explanation of what is going on.

---

As you mentioned, the presence of the summation function results in the the Index being overlooked.

There are function based indexes:

A function-based index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 + col2.

Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a WHERE clause or an ORDER BY clause. Therefore, a function-based index can be beneficial when frequently-executed SQL statements include transformed columns, or columns in expressions, in a WHERE or ORDER BY clause.

However, as with all, function based indexes have their restrictions:

Expressions in a function-based index cannot contain any aggregate functions. The expressions must reference only columns in a row in the table.

---

Though I see some good answers here couple of important points are being missed -

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;

Saying that having SUM(Table_1.Field_5) in select clause causes index not to be used in not correct. Your index on (Field_1,Field_2,Field_3,Field_4) can still be used. But there are problems with your index and sql query.

Since your index is only on (Field_1,Field_2,Field_3,Field_4) even if your index gets used DB will have to access the actual table row to fetch Field_5 for applying filter. Now it completely depends on the execution plan charted out of sql optimizer which one is cost effective. If SQL optimizer figures out that full table scan has less cost than using index it will ignore the index. Saying so I will now tell you probable problems with your index -

1. As others have states you could simply add Field_5 to the index so that there is no need for separate table access.
2. Your order of index matters very much for performance. For eg. in your case if you give order as (Field_4,Field_1,Field_2,Field_3) then it will be quicker since you have equality on Field_4 -Table_1.Field_4 = '31-oct-2011'. Think of it this was -

Table_1.Field_4 = '31-oct-2011' will give you less options to choose final result from then Table_1.Field_3 NOT IN (1, 3). Things might change since you are doing a join. It's always best to see the execution plan and design your index/sql accordingly.