Function-Based indexes

Indexes in Oracle are like indexes in books and documents. It is simply a way of finding things quicker than looking through the whole thing. But, there can be some tricky little gotchas when it comes to Oracle data and real world scenarios.

I was recently tasked with adding an index to a column in a table. At first glance this is a stupid simple task. Think of the dumbest, most clueless fucktard you know. They could create an index. It is that simple. The confusion and trickiness and why I have a job is knowing when to use an index and how to use an index.

CREATE INDEX index_name ON table_name (column_name [, another_column_name...]);

Not heavy lifting is it? Bro, do you even index?

So, as any good DB dude should do, I ran some queries to get to know the data I was tasked with indexing. So, it turns out that the column I was supposed to index was largely null. This is a problem. Nulls are not included in indexes. How do you index the absence of something? Now, I could go ahead and create an index on the column and all of the non-null values would be indexed appropriately. But, would that be enough? I needed to find out how the column is referenced in the system. I ran a query to look through all of the database objects for references to the column and/or table. I might cover this in another post. I was able to tell that the column was not used in any WHERE clauses in the DB code. I went to an App guy to get perspective on the application and how it interacted with the column. So, it turns out that most of the calls from the App were checking on the NULL status of the column. Well, that's no good. All of those calls would not use our shiny new index and the Application would continue to be dog-ass slow.

Now we get to the meat of this post. Recent versions of Oracle are able to create indexes on function calls on columns. I believe it was added in 8i but wouldn't swear to it. No matter, we are on 11g. The statement I wrote is:

CREATE INDEX FBIDX_MYTAB_NVL_MYCOLUMN ON MYTABLE( NVL(MYCOLUMN, 'some value'));

I named the column FB to indicate Function-Based and IDX so I can know at a glance that it is an Index if I am looking at multiple objects in a list or something. Remember, meaningful names are never a bad idea. The interesting part of this is the NVL function on MYCOLUMN. NVL says, if MYCOLUMN is null, pretend it is 'some value' so I can compare it to other things. So, even when MYCOLUMN is null, NVL(MYCOLUMN, 'some value') is equal to 'some value'. And 'some value' can be indexed. Of course, to fully utilize the Funciton-based index, all of the calls that check the NULL status of the column have to be changed to NVL(MYCOLUMN, 'some value') = 'some value'. But, the bottom line is that the NULL values get indexed and interaction with the table is improved.