Re: Function-based indexes

Of course, one could simply answer the question and spare the guy a book purchase. On the other hand, it is a pretty decent book and not a bad investment.

That said, a function based index is still a b*tree. The values stored are whatever the function returns. If you have some trivial upper case thing, then upper case values are stored. When a sql statement has a predicate that is a function call that matches the stored expression from the function based index declaration's metadata, the index is accessed and the rows are returned according the the predicates relational operator. That is, "=" means equal, so we get the rows with index values equal to the predicate.

So... select * from fred where dorkyfunc(status) = "A" gets all of the rows from index values "A", since the function put them there upon row insertion. No matter the actual value in the row's column, the function takes care of that by the magic with which you endowed it.

A more interesting use for func indexes is to manage sparse data - exceptions, if you will. Consider a table with a column that occasionally has an interesting value for retrieval, but would normally make a non-selective and therefore undesirable index. That is, most of the values are same, but the few exceptions are the ones you want to retrieve. If you built a normal non-unique index on this, you would have a crappy index that is so non-selective as to be pretty much useless.

You can use a function to return the interesting value as a string or integer for the cases you care about, and a NULL otherwise. Define the thing a non-unique and you have a fairly small index that only contains values of exceptional interest, since we don't index NULLs.

Have fun!

• ricky

Sybrand Bakker wrote:

> Buy the book of Jonathan Lewis, Practical Oracle 8i and you will know.
> According to this book Oracle adds an extra secret column to your
> table when you use function based indexes
Received on Sat Apr 07 2001 - 14:24:16 CDT