Re: index on upper case

1. 9.2 Enterprise
2. Not sure I understand the question. I posted the SQL for the index and for the query.

 CREATE INDEX IDX_PERSON_FIRST
     ON PERSON(UPPER(FIRSTNAME)); 3. explain plan set Statement_ID='TEST' FOR select * from person where UPPER(FIRSTNAME) = 'MIKE';

4. About 20 rows. It's just a test case.

5. CHOOSE (CBO). 6. Not sure how to analyze a table. Here's the script for creating the table:

CREATE TABLE PERSON (FIRSTNAME VARCHAR2(50),     LASTNAME VARCHAR2(50)) I have checked the Google archives and that's how I found the suggestion for creating an index on UPPER. I changed the privileges on this account to allow query rewrites (but I still don't understand why the account could create a "normal" index without this privilege, but not an index on a function). I dropped the index which was created by System and recreated it under the schema owner account.

I then set QUERY_REWRITE_ENABLED to TRUE and QUERY_REWRITE_INTEGRITY to TRUSTED.

The result? I still get full table scans. To compare, I created a second index on FirstName without the UPPER function.

1. Plan for UPPER case query:

explain plan set Statement_ID='TEST' FOR select * from person where UPPER(FIRSTNAME) = 'MIKE';

  TABLE ACCESS FULL PERSON 2) Plan for case insensitive query:

explain plan set Statement_ID='TEST' FOR select * from person where FIRSTNAME = 'MIKE';

  TABLE ACCESS BY INDEX ROWID PERSON
    INDEX RANGE SCAN IDX_PERSON_FIRST2 Any other suggestions? Thanks for the initial ones.

Keith

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E62D0A2.F102BBB4_at_exesolutions.com>...
> 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 Mon Mar 03 2003 - 08:36:23 CST