Oracle Performance Optimization Operations 15: Function-based indexes require equality matching

In the previous blog examples, we created a function-based index, but if you execute the following query:

<span style= "FONT-SIZE:18PX;" >select * from EMP where substr (ename,1,1) = ' S ';</span>

The resulting execution plan will still be (TABLE ACCESS full) because the function-based index can only take effect if the data column is able to match the equation.

This requires a high level of planning and maintenance for such indexes. Note that adding an index to a table is a very risky operation because it causes many query execution plan changes.

However, if we use a function-based index, this is not a problem because Oracle uses this type of index only if the query uses a matching built-in function.

Oracle Performance Optimization Operations 15: Function-based indexes require equality matching