Re: Can Oracle7 be installed case insentive?

Mark Holcroft <Mark.holcroft_at_gecm.com> wrote in article <5jnut4$soi_at_gcsin3.geccs.gecm.com>...
> I don't think so but...
>
> Oracle Forms 4.5 allows for fairly efficient Case Insensitive queries om
> indexed columns.
>
> You can design your database so that as well as holding the column in
> mixed case, you have an additional column ( e.g. SURNAME_UPPER ) which
> holds it in upper case. You can populate this column using triggers to
> make sure it is always consistent. This will allow for any index on this
 

> column tobe used ( e.g. where upper('smith') = SURNAME_UPPER ).
>
>
No, Oracle cannot be installed case-insensitive (and indeed, the choice of character set early on in the installation is a critical decision which is difficult to change later....)

What forms does to produce a case insensitive query is build a where clause like:

WHERE upper(field) = upper(query_criteria)

AND field like 'XX%' 
AND field like 'xx%'
AND field like 'Xx%'
AND field like 'xX%'

where the X's represent the first two characters of query_criteria. The like clauses are included to ensure that if the field is indexed, then the indexes are used. If you just use upper(field), then you force a full table scan, since the data in the index is based on the (possibly mixed case) values in the field being indexed.

There's no reason why your query cannot do the same, its just a bit ugly.

-- 
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.