The Transcender Team Weblog

Using Function-Based Indexes to Improve Oracle Performance

There are a number of reasonably easy steps that database administrators and database developers can take to improve the performance of both an Oracle database and the applications that run against the database. Some of these steps would probably be considered the responsibility of the DBA; others would typically be handled by the application developer. Of course, there are a number of shops where the IT professional wears multiple hats, so I’ll just provide the tuning information and leave it to you to determine who in your shop might find this useful.

Function-Based Indexes

Let’s take a look at the world of indexes when it comes to speeding up data retrieval. Normally, it would be good practice to put an index on a column of a table when the following conditions are met:

1. The table is relatively large.
2. The column doesn’t have many nulls.
3. The data in the column has good cardinality.
4. There isn’t a lot of DML activity against that column going on in your database.
5. Your application code often uses that column to search or sort.
6. The indexed column isn’t typically involved in a mathematical expression or a function.

Take a look at this code:

SELECT student_id, major, class, advisor FROM students WHERE last_name = ‘Monsalvatge’

Assuming there are thousands of rows in the students table, and that there are very few rows where last_name is NULL, there are very few last names that are identical, last names don’t need to be updated often, and that our statement runs many times during the day, then according to the 6 rules above it would be a good idea to create a B-tree index on that column. (I wrote more about B-tree indexes in this post here.)

But now consider this situation. Suppose the way last names got entered into the table wasn’t very well controlled. Different users used different capitalization standards when they entered the last names. Some had the last name in all caps, some had it in mixed case, and others had it in all lowercase. If the last name stored in the database is ‘MONSALVATGE’ or ‘monsalvatge’ or ‘MonSalvatge’, it will not match the literal ‘Monsalvatge’ string in the SELECT statement, regardless of whether there is or isn’t an index on the last_name column.

But you’re a smart person, so you have a fix. You write your code like this:

SELECT student_id, major, class, advisor FROM students WHERE INITCAP(last_name) = ‘Monsalvatge’

Now the SELECT statement will find what it’s supposed to find regardless of how the capitalization of last_name is stored in the database. Good job!

That’s the good news, but there’s some bad news as well. Remember that index we built on the last_name column? It’s still there, but guess what? According to rules above, the column we have indexed (at least in this specific SELECT statement) is having the function INITCAP applied to the column before the WHERE clause comparison is made. When that happens, the optimizer is unable to use the index on that column. The same would be true is we performed any other manipulation or function to the last_name column.

Consider this SELECT:

SELECT student_id, major, class, advisor FROM students WHERE tuition > 5000

In this case, if the six conditions listed above are met, then you probably want an index on the tuition column. However, if there was B-tree index on the tuition column and your SELECT was written like this:

SELECT student_id, major, class, advisor FROM students WHERE
tuition + 1 > 5000

then the optimizer would be unable to use that index because it has a mathematical expression on the index column (namely, add 1 dollar).

Interestingly enough, this SELECT statement is logically equivalent to the previous one:

SELECT student_id, major, class, advisor FROM students WHERE
tuition > 5000 – 1

Recall from algebra class that adding 1 (one) to the left side of an equation or inequality is the same thing as subtracting 1 (one) from the right-hand side. Furthermore, the optimizer, when building the parse tree and execution plan, will be able to choose using that index on tuition, since the functions and/or mathematical work is on the other side of the equation/inequality.

So how do you solve this dilemma? If you don’t put in the function to adjust the capitalization on last name, then you won’t get all of the correct answers. That’s unacceptable! If you put the INITCAP function into your SQL statement, you will get all of the correct answers, but the performance will take a hit since the optimizer won’t be able to use the index you created on last_name.

To give you the best of both worlds, Oracle created function-based indexes. This is simply an index that is built, not on the column, but on the function or mathematical expression defined on the column. For example, to create an index on the INITCAP function applied to the last name column, you simply type the following:

CREATE INDEX my_func_indx_1 ON students(INITCAP(last_name))

And, if you want to create an index on the tuition column plus $1, you would simply type the following:

CREATE INDEX my_index ON students(tuition + 1)

You can even have more than one function-based index on the same column. For example, you might have one index on the INITCAP of last_name, and another on the UPPER of last_name.

Search your application code, and I’ll bet you that there are a number of places where these function-based indexes will speed up the performance of your queries.

Until next time,

–Bob the orcltestguy