Re: Privilegies on function based indexes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Sep 1999 16:53:23 -0400
Message-ID: <CNnvN9J+P80B6iMtLXyn4GTnkdRz@4ax.com>

A copy of this was sent to "Tolik K." <tol2000_at_geocities.com> (if that email address didn't require changing) On Mon, 27 Sep 1999 14:45:34 +0200, you wrote:

>Anybody knows what additional privilegies need to
>create function based indexes on object tables?
>There is a error "No privilegies" when i trying to create
>function based index on object in my own schema.
>________________________
>Tolik.
>e-mail: tol2000_at_geocities.com
>

see the web site in my signature. I have a paper on this subject.

from that paper:

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).

--
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 Sep 27 1999 - 15:53:23 CDT