Re: Case insensitive queries

Good to see your still kicking ...

Serge Rielau wrote:
>
> Hmm, time for my monthly Oracle posting I s'pose.
>
> While functional indexes are neat in themselves they do not provide a
> match for case-insensitive search. Reason being is that often
> the user requires both the case insensitive (predicate) and the
> sensitive (select list) part. So a clean index access quickly turns
> either into a requirement for duplicate storing of the same data (index
> includes both) or an index scan with subsequent data page fetch which is
> quite literaly a drag...

I also see additional situations - I want case insensitivity on 'this part' of the where clause, but not 'that part'.

I can just see someone asking for a wild-carded, case-insensitive query on a 200M row, unindexed, unicode UTF-16 column in a warehouse, and then being totally puzzled that it takes a while. Probably'ld end up muttering "but it's real fast in a 20 row Access database". But I s'pose in this day and age the trade of between storage, memory and CPU cycles is no longer an issue.

And, who is responsible for de-sensitizing the user's input (eg: bind var) - developer or query engine?

What about joins - what's the cost to join two or more columns that contain mixed-case data? Does one respect, or ignore, sensitivity on the join (remembering that in some locales, DuPont and Dupont are different!) or is that yet another parameter?

And is there any impact due to I18N? Is there a linear case-insensitivity across all code sets and locales? Or are we wandering around yet-another "global, as long as it's US-centric" situation?

The whole thing can be taken to ridiculous extremes. (Perhaps the problem has not been thought out, and the solution for one ain't the solution for all?)

>
> Sometimes competitors do good stuff and one is ill-advised to ignore it.

Yes, they do. Not saying this should be ignored. Just suggesting that there are (IMO) acceptable workarounds that take this a long way back from "Oracle is bad, let's panic" level.

Admittedly I haven't looked it square in the eye, so I wouldn't know if the competitor has totally thunked it out. But I have seen too many hair-brained half-implementations of stuff that look nice on the surface and totally fall flat when serious stuff starts happening.

>
> Cheers
> Serge
>
> PS: I can claim being unbiased since DB2 (at present) also does not
> support case-insensitivity.. :-|

One wonders why?

> PPS: I ain't a crusty DB2 DBA, so I don't need to feel offended by
> Daniel :-)
>

I shouldn't really speak on his behalf, but I suspect you're welcome to feel offended by Daniel (or me) any time you wish. Wouldn't want you left out, just 'cause you're in the wrong camp <bg> .... Received on Fri Nov 28 2003 - 21:09:21 CST