Re: index on upper case

Keith Langer wrote:

> Thanks for the step by step instructions for this Oracle novice.
>
> It turns out that the critical step was inserting new data into the
> table. For some reason, the index doesn't seem to take effect until
> the table data is changed. Some sort of caching problem, I guess.
>
> Keith
>
> "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<%1K8a.29$pK2.223_at_news.indigo.ie>...
> > SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 3 14:45:26 2003
> >
> > (c) Copyright 2000 Oracle Corporation. All rights reserved.
> >
> >
> > Connected to:
> > Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
> > With the Partitioning, OLAP and Oracle Data Mining options
> > JServer Release 9.2.0.2.0 - Production
> >
> > SQL> show parameter query
> >
> > NAME TYPE VALUE
> > ------------------------------------ ------- ------------------------------
> > query_rewrite_enabled string false
> > query_rewrite_integrity string enforced
> > SQL> CREATE TABLE names (firstname varchar2(10));
> >
> > Table created.
> > SQL> insert into names values('ulysses');
> >
> > 1 row created.
> >
> > SQL> insert into names values('telemachus');
> >
> > 1 row created.
> >
> > SQL> insert into names values('nestor');
> >
> > 1 row created.
> >
> > SQL> insert into names values('penelope');
> >
> > 1 row created.
> > SQL> CREATE INDEX IDX_PERSON_FIRST
> > 2 ON NAMES(UPPER(FIRSTNAME));
> >
> > Index created.
> >
> > SQL> ANALYZE TABLE NAMES COMPUTE STATISTICS;
> >
> > Table analyzed. (Yes I know I should use DBMS_STATS)
> >
> > SQL> alter session set query_rewrite_enabled=true;
> >
> > Session altered.
> >
> > SQL> alter session set query_rewrite_integrity=trusted;
> >
> > Session altered.
> >
> > SQL> set autotrace on exp;
> > SQL> select firstname from names where upper(firstname)='ULYSSES';
> >
> > FIRSTNAME
> > ----------
> > ulysses
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=8)
> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NAMES' (Cost=3 Card=1 By
> > tes=8)
> >
> > 2 1 INDEX (RANGE SCAN) OF 'IDX_PERSON_FIRST' (NON-UNIQUE) (C
> > ost=2 Card=1)
> >
> >
> >
> >
> >
> >
> > "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

You assumption is not valid but I am glad it is now working. Inserting new data would have no affect.

Daniel Morgan Received on Mon Mar 03 2003 - 19:54:37 CST