Re: Function-based Index

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 16 Aug 1999 11:54:25 GMT
Message-ID: <37bcfba1.3356045@newshost.us.oracle.com>

A copy of this was sent to "fumi" <fumi_at_tpts5.seed.net.tw> (if that email address didn't require changing) On 15 Aug 1999 16:45:14 GMT, you wrote:

>Hi,
>
>Oracle 8i supports the function-based indexes.
>I have consulted the SQL Reference, and Tunning manual,
>and set the "QUERY_REWRITE_ENABLED" parameter to be TRUE.
>
>I tried it using a account with DBA role privilege, it works.
>But when I tried it using the default "scott" account,
>I got a error message "ORA-01031: insufficient privileges" (see the following).
>Does anyone know which privilege is lack for scott account
>to create a function-based index?
>
>
>SQL> create table test (
> 2 a number,
> 3 b number);
>
>Table created.
>
>SQL> create index ind_test on test (a+b);
>create index ind_test on test (a+b)
> *
>ERROR at line 1:
>ORA-01031: insufficient privileges
>

I have a pretty detailed paper on function based indexes at the web site in my signature below. from that paper:

Above we now have most of the steps you need to use function based indexes. In addition to the above steps, there are some init.ora or session settings you must use and a privelege you must have. The following is a list of what needs to be done to use function based indexes:

     You must have the system privelege query rewrite to create function based indexes on tables in your own schema.

     You must have the system privelege global query rewrite to create function based indexes on tables in other schemas

     For the optimizer to use function based indexes, the following session or system variables must be set:

     QUERY_REWRITE_ENABLED=TRUE
     QUERY_REWRITE_INTEGRITY=TRUSTED

     You may enable these at either the session level with ALTER SESSION or at
the system level via ALTER SYSTEM or by setting them in the init.ora
     parameter file. The meaning of query_rewrite_enabled is to allow the

optimizer to rewrite the query allowing it to use the function based index. The meaning

     of query_rewrite_integrity=trusted is to tell the optimizer to 'trust' that the code marked deterministic by the programmer is in fact deterministic. If the code is

     in fact not deterministic (that is, it returns different output given the same inputs), the resulting rows from the index may be incorrect.

     Use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever.

     Use substr() to constrain return values from user written functions that return VARCHAR2 or RAW types. Optionally hide the substr in a view

     (recommended).

Once the above list has been satisfied, it is as easy as "CREATE INDEX" from there on in. The optimizer will find and use your indexes at runtime for you.

.....

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 16 1999 - 06:54:25 CDT