Re: denormalize using function based indexes

On Fri, 09 Jun 2000 11:01:01 GMT, sam <roadrash_at_my-deja.com> wrote:

>Can function based indexes be used to implement
>denormalization?
>
>i have tables
> tab1 (att1 varchar2(100), att2 varchar2(200)
> att3 varchar2(200), value number);
> tab2 (value number , val2 number);
>
> select tab2.val2 from tab1, tab2
> where tab1.att1 = my_var1
> and tab1.att2 = my_var2
> and tab1.att3 = my_var3
> and tab1.value = tab2.value;
>
>Is there any way to bend function-based
>indexes in a way that gets rid of the join?
>
>I cannot denormalize the app I'm working on
>by adding tables but indexes are fair game.
>Ideas, speculations, and example code appreciated,
>--
>

I'm not 100% sure what you have in mind but I believe any function used for a function based index has to be deterministic, meaning that it must be guaranteed to return the same value when supplied with a given argument, regardless of database of package values. Basically if the function needs to read table B to determine it's result it can't be used in a function based index so can't solve your problem. Received on Fri Jun 09 2000 - 00:00:00 CDT