What is a Function-Based Index?
The default type of index is a b-tree index, which creates an index on one or more columns using a tree-like structure. A bitmap index is one that creates a two-dimensional map of rows and values in a specific column.
A function-based index, on the other hand, is an index that is created on the results of a function or expression.
In Oracle, when you create an index on a column (such as a b-tree index), you need to mention the value exactly (without modification) for the index to be used.
For example, if you index a column called percentage on the student table, and query the table using:
WHERE percentage = 82.95
The index should be considered.
However, if you have the same index and run a query to say:
WHERE ROUND(percentage) = 83, then the index won’t be used.
This is because the percentage is not the same as ROUND(percentage).
So, how can we use an index in this situation?
We create something called a function-based index. This means we create indexes on columns where we are performing a function on that column or running an expression.
Why Should I Create a Function-Based Index?
The reason to create a function-based index on a column is to improve the performance of a query. And that query uses a function or expression. That’s usually the only reason to use a function-based index.
If you have a query or many queries, that use a function or expression in them and you’d like to improve the performance, a function-based index might be important to you. Like every performance tactic, it depends on your database and your data. I’ve covered more reasons to use indexes and how to know when to use an index on Database.
How do we create a function-based index?
Syntax of a Function-Based Index
CREATE INDEX index_name ON table_name(function(column_name));
The parameters of this command are:
index_name: This is the name you can give to your new index.
table_name: The name of the table to create a new index on.
function: this is the function being used on the column.
column_name: This is the column in the table that you’re performing the function on for the index.
You can add in different kinds of expressions here, not just a function.
Some examples are:
ROUND(percentage)
SUBSTR(first_name, 1, 10)
first_name || ‘ ‘ || last_name
The function-based index can handle many kinds of expressions. It works in the same way as a b-tree index.
Example
Our example index is on the rounded value of the percentage column, which we saw earlier. The SQL to create this index would be:
CREATE INDEX student ON (ROUND(percentage));
This creates a new index called the student on the rounded value of the percentage column.
Now, any queries that use this function in this way should pick up the index and should get an increase in performance.
