What can be done if an index is not used due to a function on a column?

If an index is not used due to the use of a function on a column in a condition (for example, WHERE UPPER(column) = 'VALUE'), then the index cannot be used directly because the function alters the data.

Possible solutions:

• Create a functional (expression-based) index on the result of the function if the DBMS supports it. For example, in Oracle or PostgreSQL, you can create an index on UPPER(column).

• Avoid using functions in filter conditions by rewriting the query to compare with the original value, for example, by converting the constant to the required case.

• Use computed/virtual columns with indexes on them.

Example of creating a functional index in PostgreSQL:

CREATE INDEX idx_upper_column ON table_name (UPPER(column));

This will allow the index to be used with queries involving UPPER(column).