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: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 3 Mar 2003 14:55:55 -0000
Message-ID: <%1K8a.29$pK2.223@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 - 08:55:55 CST

Original text of this message

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