Re: case insensitive indexes?

In article <Pine.NXT.3.90.950607083247.4024A-100000_at_iluvatar> John Jones <john_at_iluvatar.tip.duke.edu> writes:
>Path: nntp.teleport.com!psgrain!rainrgnews0!cheetah.pacinfo.com!news.uoregon.edu!cs.uoregon.edu!reuter.cse.ogi.edu!uwm.edu!spool.mu.edu!bloom-beacon.mit.edu!news.kei.com!news.mathworks.com!news.duke.edu!usenet
>From: John Jones <john_at_iluvatar.tip.duke.edu>
>Newsgroups: comp.databases.oracle
>Subject: Re: case insensitive indexes?
>Date: Wed, 7 Jun 1995 08:33:31 -0400 (EDT)
>Organization: Duke University, Durham, NC, USA
>Lines: 8
>Message-ID: <Pine.NXT.3.90.950607083247.4024A-100000_at_iluvatar>
>References: <jimm-0506950023450001_at_192.0.2.1>
>NNTP-Posting-Host: iluvatar.tip.duke.edu
>Mime-Version: 1.0
>Content-Type: TEXT/PLAIN; charset=US-ASCII
>X-Sender: john_at_iluvatar
>In-Reply-To: <jimm-0506950023450001_at_192.0.2.1>

>Not sure about the case insensitive index, but you can just convert to
>all upper case in your searches by using upper().
 

>--------------------------------------------------------------------
>John Jones | my views are my own.........
>Oracle Consultant | no matter what company they are
>john_at_iluvatar.tip.duke.edu | coming from.
>--------------------------------------------------------------------
I tried that and it does work. However, it is slowwwwww. If you use explain plan you will see that it does a full table scan. Example, You want to search for someone with a last name of 'Jo%' so select * from employee where upper(lastname) like 'JO%'; will produce a full table scan even though you have an index on the lastname. So what to do? I really do not want to shadow data by upper casing it in the large number of places that I would have to. Any ideas?

Jim Kennedy Received on Wed Jun 07 1995 - 00:00:00 CEST