Tuesday, July 15, 2008

Multi-column function based index

Today I was looking at an SQL statement with a slow response. The statement does a lookup on a person using 'upper(lastname) and upper(firstname)'. There is a functioned-based index (fbi) on the lastname column, but by checking the statistics it turned out to have a rather low selectivity and the CBO went for a full table scan. I don't know why it has not occurred to me before, but today it dawned upon me that it may be possible to create an index like this:

create index foo_idx on foo(bar(a), bar(b)) ;

There is an example in Tom Kyte's last book, but I guess the tidbit got lost on its way to or inside my hippocampus. I have always thought about the fbi as a way to index the result of a function (which could take many arguments, but only return one value). By looking at the fbi as an index on a shadow-column it becomes obvious that more than one column can be indexed this way, just like any other multi-columned index.

Point is of course that each of lastname and firstname has a very low density (at least in this part of the world), but the combination of them is not too bad.

Or may be it was something else that went right, one should always supply a proof these days. I reapplied the old index with the same slow response as a result. Also I gathered statistics again every time an index was created, and the results where consistent.