Re: Aarrrggghhh!!! Creating an index shouldn't be so hard!

Hi - did you get this working yet?

I just did this yesterday, worked ok.

1. First, grant QUERY REWRITE to sysop.

That should let you create the index it.

2. Once you've created it, the optimizer won't use it unless you set QUERY_REWRITE_ENABLED to true, either at the system or session level.

3. And, of course, the table should be analyzed.

Works for me, good luck to you,

Yosi

ismgr_at_pctc.com wrote:

> Well, I must admit to being flummoxed. The nature of this problem guarantees
> that it'll be easy, and I'll be embarrased. Well, tough. I'm tired of
> beating my head against the wall.
>
> I'm trying to create a function-based index on a table. Here's what I do:
>
> create user sysop identified by password
> default tablespace data
> temporary tablespace temp;
>
> grant CREATE SESSION to sysop;
> grant CREATE TABLE to sysop;
> grant UNLIMITED TABLESPACE to sysop;
> grant CREATE PUBLIC SYNONYM to sysop;
> grant DROP PUBLIC SYNONYM to sysop;
> grant CREATE DATABASE LINK to sysop;
> grant CREATE VIEW to sysop;
> grant CREATE TRIGGER to sysop;
> grant CREATE PROCEDURE to sysop;
>
> connect sysop/password
>
> drop table issuer;
>
> create table issuer (
> ISSUER_ID number(9) primary key,
> ISSUER_NAME varchar(80)
> );
>
> create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME));
>
> And here's what I get:
>
> Table created.
>
> create index ISSUER_NAME_IDX on ISSUER (UPPER(ISSUER_NAME))
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> (The '*' is actually under the 'I' in 'ISSUER_NAME')
>
> First, the reading of ora-1031 isn't even close to making sense. Then, if
> it's a privilege problem, I can't find any mention of any privilege that I
> don't have that I should need.
>
> Please relieve my groggy brain by pointing out the obvious problem so I can
> go hide under my desk. Thanks.
>
> ---
> Dennis Taylor
> ---
> Don't worry about people stealing your ideas. If your ideas
> are any good, you'll have to ram them down people's throats. -- Please see
> the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis Taylor
> INET: ismgr_at_pctc.com
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San
> Diego, California -- Public Internet access / Mailing Lists
> -------------------------------------------------------------------- To
> REMOVE yourself from this mailing list, send an E-Mail message to:
> ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message
> BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
> you want to be removed from). You may also send the HELP command for other
> information (like subscribing).

--
Thanks,

Yosi


---------------------------------------------------------