Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: can't create function-based index

Re: can't create function-based index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 24 Sep 2000 10:22:52 +0100
Message-ID: <969788504.18735.1.nnrp-07.9e984b29@news.demon.co.uk>

You need the system privilege QUERY REWRITE to create function-based indexes on tables in your own schema, and GLOBAL QUERY REWRITE to create function-based indexes on table in other schemas. (NB You do not need the privilege if you are simple a user inserting rows into a table with a function-based index).

To be able to query using the index, you need two parameters set:

    query_rewrite_enabled = true
    query_rewrite_integrity = trusted

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Paul G. Young wrote in message ...

>Problem:
> I am trying to create a function-based index but receive a permissions
> error (ORA-1031).
> Suggestions greatly appreciated.
>
>Environment:
> HPUX 11.0
> compatible 8.1.5
> query_rewrite_enabled TRUE
> query_rewrite_integrity enforced
>
>This is the table description for FARM_OPERATOR, the key is on STMPNO,
>OPERATOR.
> STMPNO NOT NULL NUMBER(8)
> OPERATOR NOT NULL NUMBER(1)
> FAMNAM NOT NULL VARCHAR2(31)
> POSTCOD VARCHAR2(6)
>
>This is what I want to run. It works, but slowly. So I want to index it.
> SELECT TELENO FROM FARM_OPERATOR WHERE TO_RESPROV(POSTCOD) = 10;
>
>
>SQL script:
> CREATE OR REPLACE FUNCTION TO_RESPROV(ipostcod IN VARCHAR2) RETURN
>NUMBER
> DETERMINISTIC
> IS
> oresprov NUMBER := 0;
> c CHAR(1);
> BEGIN
>
> c := SUBSTR(ipostcod,1,1);
> IF (c IS NOT NULL) THEN
> IF (c = 'A') THEN oresprov := 10;
> ELSIF (c = 'C') THEN oresprov := 11;
> ELSIF (c = 'B') THEN oresprov := 12;
> ELSIF (c = 'E') THEN oresprov := 13;
> ELSIF (c = 'G' OR c = 'H' OR c = 'J') THEN oresprov := 24;
> ELSIF (c = 'K' OR c = 'L' OR c = 'M' OR c = 'N' OR c = 'P') THEN
>oresprov := 35;
> ELSIF (c = 'R') THEN oresprov := 46;
> ELSIF (c = 'S') THEN oresprov := 47;
> ELSIF (c = 'T') THEN oresprov := 48;
> ELSIF (c = 'V') THEN oresprov := 59;
> ELSIF (c = 'X') THEN oresprov := 60;
> ELSIF (c = 'Y') THEN oresprov := 61;
> ELSIF (c = 'Z') THEN oresprov := 62;
> END IF;
> END IF;
>
> RETURN (oresprov);
> END;
> /
>
> GRANT EXECUTE ON TO_RESPROV TO PUBLIC;
>
> CREATE INDEX IX_RESPROV ON FARM_OPERATOR (to_resprov(postcod));
>
>
>Results:
> Function created.
>
> Grant succeeded.
>
> CREATE INDEX IX_RESPROV ON FARM_OPERATOR (to_resprov(postcod))
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
>--
>PGY (Paul G. Young)
>http://www.magma.ca/~pgy
>
>
Received on Sun Sep 24 2000 - 04:22:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US