Re: Function-based indexes

On Wed, 04 Apr 2001 09:31:47 -0400, Vikas Agnihotri <onlyforposting_at_yahoo.com> wrote:

>I can see that FBI (!) would be useful in the classic case of case
>insensitive string matching
>
>where upper(column)=upper('input string')
>
>To serve the above query, one would
>create index on table (upper(column))
>
>But if I know the case in which a particular data element is stored
>and were to use a
> where column='This Way'
>
>would it still use the FBI?
>
>Of course, I am assuming that you cannot have a FBI and any other
>index (btree or bitmap) on the same column?
>
>In general, how exactly does the FBI work internally? The docs mention
>that it stores the data using a 'expression tree' and not a 'btree'.
>What does this mean exactly? Does Oracle evaluate the function and
>actually store the upper-cased values in the index?
>
>If it is not a btree, arent there any leaf nodes? How is the index
>organized then?
>
>Thanks...

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 (I already can imagine the application were someone is nearing 1000 columns and wants to add a function based index on about every column, lol)

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Apr 04 2001 - 09:26:27 CDT