Re: index on upper case

Keith Langer wrote:

> I am trying to create a case insensitive index.
>
> Below is a test index:
>
> CREATE INDEX IDX_PERSON_FIRST
> ON PERSON(UPPER(FIRSTNAME));
>
> When I run it from my local user (schema owner) account, I get a
> permission problem. When I run it from the System account, it gets
> created. But then when I run EXPLAIN PLAN on the following query:
>
> select * from person where UPPER(FIRSTNAME) = 'MIKE';
>
> I still get full table scans.
>
> So what's going on here?
>
> Keith

Tried looking over you shoulder but I couldn't get to the keyboard. Probably my fault as I broke my crystal ball and have been lazy about getting it fixed.

1. What Oracle version and edition?
2. What SQL?
3. What explain plan?
4. How many rows in the table?
5. RBO or CBO?
6. Did you analyze the table if CBO?

Did you check the google.com archives for similar Q&A before posting? Did you see where just a few days ago I posted the following?

To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.

Daniel Morgan Received on Sun Mar 02 2003 - 21:48:50 CST