Re: function based indexes

cheechong wrote:

> hi Daniel,
>
> nope. these 2 lines are not found in my .ora file.
> so these are the 2 lines that are causing the problem?
>
> --
> chee chong
>
> Originally posted by Da Morgan
> > cheechong wrote:
> >
> > > i was using Oracle 9i version 9.2.
> > > i used to be able to create such a functional index in Oracle
> > 8.1.7.
> > > any idea? thanks.
> >
> > Did you check to see if the following are set in the initSID.ora /
> > spfile?
> >
> > query_rewrite_enabled=TRUE
> > query_rewrite_integrity=TRUSTED
> >
> Daniel Morgan
>
> --
> Posted via http://dbforums.com

They are required for the creation of function based indexes.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_510a.htm#2071327

To quote:
To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. 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.

Daniel Morgan Received on Sat Mar 01 2003 - 17:51:06 CST