July 3, 2011

Using Function Based index appropriately

Very often requests to enhance performance of queries resembling to the following one:

SELECT col1
,col2
,col3
FROM t
WHERE flag != 'N';

populate forums like OTN. Requesters have already created an index on the flag column and are complaining about the query which is not performing well and which is not using the index they created to cover the flag where clause column. The main problem here is the “!=” operator. The Oracle Cost based Optimizer (CBO) is unable to use an index to handle this operator. Fortunately, there is a possibility to enhance this kind of queries by taking the advantage of using a function based index. And this is what I aim to explain here via this blog article:

Nothing noticeable happened; we are still full scanning our T table. You know why? This is because to take advantage of using the newly created function based (FBI) index, we have to rewrite the query to make the query and the index consistent with each other.

The query using the FBI index is not returning the correct number of rows because, now, the status column has null values in it. Both the query and the FBI index should be re-factored in order to take into account the NULL values of the status column:

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.