Re: function based index and DB - startupparameters

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f7abcf4$0$13656$afc38c87_at_news.optusnet.com.au>...
> fuxs wrote:
>
> > ORA SQL - Ref for 9i Release 2 advises to set two startup - paramerters to
> > work with function based indexes:
> >
> > <quote>
> > In addition, in order for Oracle to use
> > function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter
> > must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set
> > to TRUSTED.
> > </quote>
> >
> > Is it really necessary to set QUERY_REWRITE_INTEGRITY to TRUSTED? I'd like
> > staying with 'ENFORCED'.
> >
>
> It's necessary, unfortunately. It's a well-known issue that the setting you
> want for function-based index use conflicts with what you may well want for
> the use of (say) materialized views.
>
> Jonathan Lewis mentions it in his practical Oracle 8i book, amongst other
> sources.
>
> Regards
> HJR
I thought this was only for programmer defined functions, to ensure that the function always returned the same value given the same inputs. With Oracle's internally written functions like UPPER, I thought this was not the case.

My memory has been known to fail me, and I don't have a test case handy, but I remember trying this and not setting the TRUSTED parameter, but the FBI based on something like UPPER was still used.

Any insight?

Regards,

Steve Received on Wed Oct 01 2003 - 15:52:08 CDT