Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.server Subject: Re: index on upper case Date: Mon, 03 Mar 2003 08:39:54 -0800 Organization: EXE Message-ID: <3E63855A.AD9D645C@exesolutions.com> X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 References: <15c7b652.0303021747.2177140d@posting.google.com> <3E62D0A2.F102BBB4@exesolutions.com> <15c7b652.0303030636.56befc98@posting.google.com> <3e637051$1_2@news.estpak.ee> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@splish28.drizzle.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 137 Xref: newsfeed1.easynews.com comp.databases.oracle.server:178272 X-Received-Date: Mon, 03 Mar 2003 09:40:22 MST (news.easynews.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" 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 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