Re: can't create function-based index

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

>

>