Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index on upper case

Re: index on upper case

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Mon, 03 Mar 2003 08:39:54 -0800
Message-ID: <3E63855A.AD9D645C@exesolutions.com>


Tanel Poder wrote:

> Hello!
>
> Try simply to analyze the table & index.
>
> analyze table person compute statistics;
> analyze index idx_person_first compute statistics;
>
> (yes, it is recommended to use DBMS_STATS instead, but i don't remember it's
> syntax/parameters by heart)
>
> Then explain plan again.
>
> It could be, that because your table isn't analyzed, rule based optimizer is
> used for table scan, but RBO doesn't know anything about function based
> indexes...
>
> But even after analyzing, you could see FTS, because optimizer sees that all
> your 20 rows ar in single one block, therefore relatively cheap to get.
>
> Tanel.
>
> "Keith Langer" <tanalbit_at_aol.com> wrote in message
> news:15c7b652.0303030636.56befc98_at_posting.google.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_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

exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>schema_name, CASCADE=>TRUE);

and Keith ... with 20 rows not using an index is always more efficient than using an index and CBO requires current statistics to function properly.

Daniel Morgan Received on Mon Mar 03 2003 - 10:39:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US