[…] Function based indexes in Oracle can help to build the index according to some function, because functions are always conflicting with indexes and indexes cannot work fine with all kinds of functions. In SQL server doesn’t have function based indexes but instead you can make an index on a computed column. […]
One of the largest problems with indexes is that indexes are often suppressed by developers and ad-hoc users. Developers using functions often suppress indexes. There is a way to combat this problem. Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions, or maybe a PL/SQL function or C callout.
The following example shows how to create a function-based index:
An index that uses the UPPER function has been created on the ENAME column. The following example queries the EMP table using the function-based index:
The function-based index (EMP_IDX) can be used for this query. For large tables where the condition retrieves a small amount of records, the query yields substantial performance gains over a full table scan. The following initialization parameters must be set (subject to change with each version) to use function-based indexes (the optimization mode must be cost-based as well). When a function-based index is not working, this is often the problem.
TIP
Function-based indexes can lead to dramatic performance gains when used to create indexes on functions often used on selective columns in the WHERE clause.
To check the details for function-based indexes on a table, you may use a query similar to this:
In oracle apps, It is often recommended to use function based index on Standard tables. Is there a benefit of that? Does it affect Insert/update in a same way as other indexes? If I write a poorly performing Function, Do I slow down the data load as well?