Function-based Indexes in Oracle
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:
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: ~0.47 secs