Digital Sanctum

Personal blog of Shane Witbeck Tags · Archive · About

Function-based Indexes in Oracle

Published: 30 Apr 2010

I’ve been meaning to investigate something that came to mind a while back and just now got around to it while researching a bug. My feeling was that there may be a faster way of executing queries that involve date comparisons and oracle functions like trunc. An example is

According to what I’ve read, this is going to have a negative impact on performance and possibly result in a full table scan caused by trunc(created). Even if there’s an index on the created column, because we call trunc(created), it effectively negates the index unless the index is a function-based index. For more on this, read:

Oracle SQL Tuning with function-based indexes

The article gives three ways to fix these kind of index validation issues but the function-based index (FBI) seems to be the easiest route.

I decided to see what difference the missing function-based index would make. Here’s a quick summary for the above query:

Time before adding FBI: ~0.72 secs
Time after adding FBI: ~0.25 secs

Difference: <b>~0.47 secs</b>
Tags: #database#oracle#function-based-indexes