Re: Function-based indexes -- please help!

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 1 May 2003 16:01:02 -0700
Message-ID: <130ba93a.0305011450.7f772731@posting.google.com>

Has to be privilege related problem. Are you using 8.1.7? Seems, at least with 9i, all you need is the "query rewrite" and the good old "connect, resource" roles - though "resource" role maybe a bit too powerful.

SQL> select grantee, granted_role from dba_role_privs where grantee='JTEST';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
JTEST                          CONNECT
JTEST                          RESOURCE

SQL> show parameters query

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
query_rewrite_enabled                string      FALSE
query_rewrite_integrity              string      enforced

SQL> grant dba to jtest
  2 ;

Grant succeeded.

SQL> conn jtest/jtest_at_athena
Connected.
SQL> desc t11

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C1                                                 NUMBER
 C2                                                 DATE

SQL> create unique index t11_fbi on t11 (c1, nvl(c2, TO_DATE('12/31/9999', 'MM/DD/YYYY'))); Index created.

SQL> drop index t11_fbi;

Index dropped.

SQL> conn jyang/jyang_at_athena
Connected.
SQL> revoke dba from jtest;

Revoke succeeded.

SQL> conn jtest/jtest_at_athena
Connected.
SQL> create unique index t11_fbi on t11 (c1, nvl(c2, TO_DATE('12/31/9999', 'MM/DD/YYYY')));
create unique index t11_fbi on t11 (c1, nvl(c2, TO_DATE('12/31/9999', 'MM/DD/YYYY')))

                                                *

ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn jyang/jyang_at_athena
Connected.
SQL> grant query rewrite to jtest;

Grant succeeded.

SQL> conn jtest/jtest_at_athena
Connected.
SQL> create unique index t11_fbi on t11 (c1, nvl(c2, TO_DATE('12/31/9999', 'MM/DD/YYYY'))); Index created.

SQL>

• Jusung Yang

alex_at_sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0.0304301034.570a1229_at_posting.google.com>...
> I have followed the things suggested on this (and other sites) for
> minimum requirements for the function-based index creation:
>
> QUERY REWRITE privilege granted to the index creator
>
> COMPATIBLE set to 8.1.7
>
>
> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE
>
> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED
>
> ... and I get the following errors:
> CREATE UNIQUE INDEX index1
> ON table1 (col1, col2, col3, NVL(dateCol1, TO_DATE('12/31/9999',
> 'MM/DD/YYYY')))
>
> ORA-01031: insufficient privileges
>
> ... or, if the index creation line is modified to this:
> CREATE UNIQUE INDEX index1
> ON table1 (col1, col2, col3, NVL(dateCol1, '31-12-9999'))
> , a different error:
>
> ORA-01743: only pure functions can be indexed
>
> Any ideas on how to work around this?
>
> Thank you in advance!
>
> --Alex
Received on Thu May 01 2003 - 18:01:02 CDT