Are group function indexes supported in oracle?

Question

I execute the 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 created an index for the columns (Field_1,Field_2,Field_3,Field_4) of Table_1 , but the index is not used.

If I remove the SUM(Table_1.Field_5) clause SUM(Table_1.Field_5) from select, then the index will be used.

I am confused if the optimizer does not use this index or it because of the SUM() function that I used in the query.

Please share your explanation on the same.

Answer 1

When you delete SUM, you also remove field_5 from the request. All the data needed to answer the query can be found in the index, which can be faster than scanning the table. If you added field_5 to the index, a query with SUM could use the index.

Answer 2

If your query returns a large percentage of table rows, Oracle may decide that performing a full table scan is cheaper than a “jump” between the index and the table heap (to get the values ​​in Table_1.Field_5 ).

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

See "Indexing only: Avoid accessing the table" when using the Luke index for a conceptual explanation of what is happening.

Answer 3

As you mentioned, having a summation function causes the Index to be ignored.

function based indexes :

A functional 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 an index based on a transformed column or expression allows you to return data using the index when this function or expression is used in the WHERE clause or in the ORDER BY clause. Thus, a function-based index can be useful when frequently executed SQL statements include transformed columns or columns in expressions in a WHERE or ORDER BY clause.

However, as in all, function-based indexes have their limitations:

Expressions in a functional index cannot contain any aggregate functions. Expressions should only refer to columns in a table in a table.

Answer 4

Although I see some good answers, a couple of important points are missing here -

 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 the presence of SUM (Table_1.Field_5) in the select clause causes the index to not be used incorrectly. 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 is used, the DB will have access to the actual row of the table to get Field_5 to apply the filter. Now it completely depends on the execution plan drawn from the sql optimizer, which is cost-effective. If the SQL optimizer finds out that a full table scan is less expensive than using an index, it will ignore the index. Saying this, I will now tell you about probable problems with your index -

• As in other countries, you can simply add Field_5 to the index so that there is no need for separate access to the table.
• Your index order is important for performance. E.g. in your case, if you give the order as (Field_4,Field_1,Field_2,Field_3) , then it will be faster, since you have equality in Field_4 - Table_1.Field_4 = '31-oct-2011' . Think about it -

Table_1.Field_4 = '31-oct-2011' will give you less options for choosing the final result with Table_1.Field_3 NOT IN (1, 3) . Things can change as you make the connection. It is always better to see the execution plan and compile your / sql index accordingly.