Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!pln-w!extra.newsguy.com!lotsanews.com!newsfeed.icl.net!newsfeed.fjserv.net!colt.net!diablo.theplanet.net!news.indigo.ie!not-for-mail Reply-To: "Telemachus" From: "Telemachus" Newsgroups: comp.databases.oracle.server References: <15c7b652.0303021747.2177140d@posting.google.com> <3E62D0A2.F102BBB4@exesolutions.com> <15c7b652.0303030636.56befc98@posting.google.com> Subject: Re: index on upper case Lines: 181 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: <%1K8a.29$pK2.223@news.indigo.ie> Date: Mon, 3 Mar 2003 14:55:55 -0000 NNTP-Posting-Host: 62.77.170.89 X-Complaints-To: abuse@eircom.net X-Trace: news.indigo.ie 1046703355 62.77.170.89 (Mon, 03 Mar 2003 14:55:55 GMT) NNTP-Posting-Date: Mon, 03 Mar 2003 14:55:55 GMT Organization: Eircom.Net http://www.eircom.net Xref: newsfeed1.easynews.com comp.databases.oracle.server:178257 X-Received-Date: Mon, 03 Mar 2003 07:55:49 MST (news.easynews.com) 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" wrote in message news:15c7b652.0303030636.56befc98@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 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