Re: index on upper case

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
Received on Mon Mar 03 2003 - 15:19:45 CST