Re: turn off/on case insensetive search in Oracle DBMS

Andy Hallam <ahm_at_exel.co.uk> wrote:
>> Actually the most common workaround would be to include an uppercase
 column
>> for your searchable column and populate this with a before insert trigger.
>> Current versions of Oracle also allow you to create an index on
>> UPPER(column_name).
>
>You've got to be kidding; actually have a duplicate column holding the data
>as uppercase ?. Now that takes the biscuit. What about if you want to be
>flexible enough (bad word for ORACLE that) to allow a user to search on any
>character field thay want - have a duplicate column for each - get real.

The "duplicate column" is an index. If you just want case insensitive searching, and you want an efficient query, you need the index anyway which inhernetly duplicates information (modulo a hash cluster). If you don't need the index, don't create it. Either way the front-end can query on UPPER(column_name) with equivalent efficiency.

I agree, there will be duplication if you need both case sensitive and case insensitive matching on the same column. I agree that there's also a problem with this approach if users are constructing ad-hoc queries rather than using a front-end.

How does the database you're porting from handle this? I can't at first glance see how to construct a single B*Tree (or hash table) that allows both case sensitive and case insensitive matches without duplicating information (and hence increasing size and insert cost).

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/