Understanding Function Based Indexing in Oracle

Oracle introduces a new type of index, the function-based index. As the name implies, a function-based index is based on the result of a function, rather than being directly based on a value in one or more columns in a table.

Indexes are very useful for improving data access and data selection. But there are times when you would like to access data rapidly based on a transformation of a piece of data by means of a function. For instance, you might want to select a name based on the characters of the name, regardless of case. You could select on the value of UPPER(NAME), where all characters are converted to uppercase before a comparison is made. But to implement this selection criterion, your Oracle database would have to select values in the index, perform the function, and then make the selection. A function-based index automatically stores the result of a function, so you could run the same query against a function-based index that had already applied the UPPER() function against the NAME column.
This function-based index could avoid the overhead of executing the function against index entries and gain the full performance benefits provided by the index. In the next lesson, you will learn how to create a function-based index.

You can create function-based indexes. Any query that performs a function on a column generally does not use that column's index. Therefore, this query could not use an index on the Title column:

'MY LEDGER';

select * from BOOKSHELF
where Title = 'MY LEDGER';

You can create indexes that allow function-based accesses to be supported by index accesses. Instead of creating an index on the column Title, you can create an index on the column expression UPPER(Title), as shown in the following listing:

create index BOOKSHELF$UPPER_TITLE on
BOOKSHELF(UPPER(Title));

Although function-based indexes can be useful, be sure to consider the following questions when creating them:

1. Can you restrict the functions that will be used on the column? If so, can you restrict all functions from being performed on the column
2. Do you have adequate storage space for the additional indexes?
3. When you drop the table, will you be dropping more indexes (and therefore more extents) than before? How will that impact the time required to drop the table?

Function-based indexes are useful, but you should implement them sparingly. The more indexes you create on a table, the longer all insert, update, and delete operations will take.