Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!haven!vrdxhq!daitc!daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: Using Indexes in Oracle
Message-ID: <341@daitc.daitc.mil>
Date: 15 Feb 89 19:27:54 GMT
References: <6555@bunny.UUCP>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Distribution: na
Organization: Defense Applied Information Technology Center
Lines: 65
In-reply-to: pm04@bunny.UUCP (Preeti Mehta)

In article <6555@bunny.UUCP>, pm04@bunny (Preeti Mehta) writes:
>Does anyone out there know of a way to perform case-insensitive string
>matching on character strings defined as indexes on an Oracle table?

Why yes, I know three ways.  They're all awful.

1) give up the indexing, perform exhaustive searches like
	uppercase(table.column) = "SEARCHSTRING"
or
	table.column = "[Ss][Ee][Aa][Rr][Cc][Hh][Ss][Tt][Rr][Ii][Nn][Gg]"
Clearly, if you need to do this often or on large tables, performance
will be unacceptable.  It's also awkward and error-prone, and must be
coded into each application.  In effect, it's not a solution at all,
it's very nearly giving up on the problem.  (If you were to decide to
case fold data values before storing them, that would be giving up).

2) maintain a case-folded partner column (implemented by triggers if
available, otherwise uglier methods), express search as
	table.partner_column = "SEARCHSTRING"
index on the partner column, not the real one.  Search on the partner,
return the contents of the real column.  This is how I implemented
full-text searching using a standard relational engine.  This is also
how an unannounced (last I heard) Oracle product works.  Highly
subvertible, costs extra constant space and time, and implementation
for detail tables is too hideous to describe.  Also, since not known
by the relational engine, query optimizer misses many opportunities
for optimization.  In effect, uses a RDBMS but abandons most of the
advantages of the relational model.

3) directly manipulate internals of index tables to fold case.  Avoids
space and time costs of method 2, but highly non-portable: will work
neither on other vendors' RDBMS nor on future versions of own RDBMS.
Not only highly subvertible, this IS a subversion.  Total kludge,
blatant example of trying to turn the system into something it isn't.

I also know a fourth way that's clean and workable, and answers the
next request you'll make: efficient substring searching.  I don't know
any vendors who support it.

4) Employ user-defined transforms on data values before generating
index values.  For instance, your problem would be solved with a
simple transform:
	index on uppercase(table.column) is indextbl
This would indicate to the relational engine that inserts and updates
to this column should generate an index value on the case folded data
value.  The query optimizer knows to use the index in one of three
ways:
	A. automatically and silently case fold
	   qualifications that select against this column
	   (perhaps with syntax to override for queries intended to
	   be case sensitive; could maintain second ordinary index
	   to speed both types of queries)
	B. spot qualifications that use the same transform
	C. require override syntax: avoids aliasing problems with
	   method B but probably not syntactically compatible with
	   standard SQL, as method A could be.

In short, with user defined transforms on index generation, it's
simple, clean, efficient, non-subvertible, general, and safe.  I know
of no vendor who supports them.  If vendors were to become convinced
that we consider this important, and that we plan to buy products that
do support it, this may change.

-- Jon
--