RE: Upper Case Indexes -- extended !!

I remember reading in some corner that these require enabling the query_rewrite init parameter.

alright DW folks ..here is the kinda off-topic extension to this chain ..

any of u nice folks used 8i materialized views for ur apps?? query rewrite works sometimes .. and sometimes does not .. we are using em and most of the times the refresh response time also seems to suck !!

comments .. ur experiances??

TIA
deepAk

PS: oli .. that was a neat little point on system tspitr btw

-----Original Message-----
From: Oliver Artelt [mailto:oli_at_md.transnet.de] Sent: Monday, September 25, 2000 10:31 AM To: Multiple recipients of list ORACLE-L Subject: Re: Upper Case Indexes

How about:

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:

CREATE INDEX uppercase_idx ON emp (UPPER(empname));

can facilitate processing queries such as this:

SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';

check it out,
oli

On Mon, 25 Sep 2000, Stuart Houlston wrote:
> All,
>
> I have a problem with searching a name column within the database where
the
> case of the data must be preserved. This name is then used in the search
> criteria of many queries in the application.
>
> The data is
>
> Table DEALERS
> Column NAME
> Data
> BMW Freecars Ltd
> BMW FREECARS ltd
> BMW FreeCars LTD
>
> Table DEALERS will contain several thousand entries and an index is placed
> on the NAME column to assist with lookups.
> These are all valid entries but the developers want to be able to search
on
>
> SELECT *
> FROM DEALERS
> WHERE NAME = 'BMW FREECARS LTD'
>
> This should bring back the 3 rows above.
>
> I could of course use the UPPER function around the column name but this
> will invalidate the use of my carefully designed index strategy and cause
> the query to take a thousand years to complete (well a few seconds
anyway).
>
> The only suggestion that I have come up with is to have a second column
> called UPPER_NAME on the table that hold all the values from NAME in
> UPPERCASE. This could then be indexed and used in the search criteria.
This
> column will be maintained by the use of database triggers. A bit messy but
> it should work.
>
> Any other helpful hints and tips would be gratefully accepted.
>
>
> Many Thanks
>
> Stuart

-- 
---

Oliver Artelt, System- und Datenbankadministration
---------------------------------------------------------------
  cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
   email: oli@cubeoffice.de # web: http://www.cubeoffice.de
---------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Oliver Artelt
  INET: oli_at_md.transnet.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may