Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: tanalbit@aol.com (Keith Langer)
Newsgroups: comp.databases.oracle.server
Subject: Re: index on upper case
Date: 3 Mar 2003 06:36:23 -0800
Organization: http://groups.google.com/
Lines: 103
Message-ID: <15c7b652.0303030636.56befc98@posting.google.com>
References: <15c7b652.0303021747.2177140d@posting.google.com> <3E62D0A2.F102BBB4@exesolutions.com>
NNTP-Posting-Host: 24.123.66.214
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1046702184 22746 127.0.0.1 (3 Mar 2003 14:36:24 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 3 Mar 2003 14:36:24 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.server:178255
X-Received-Date: Mon, 03 Mar 2003 07:36:16 MST (news.easynews.com)

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@exesolutions.com> wrote in message news:<3E62D0A2.F102BBB4@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