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

"Thomas Liang" <liangluosheng_at_china.com> wrote

> you can write
> SELECT * FROM table WHERE UPPER(field1)='name'

Just be careful when doing this as every single row read, call this function. When dealing with large volumes of data, this results in very noticeable performance overheads. As a rule, always try and perform the function on the data projected (i.e. the columns in the SELECT cause and not in the WHERE clause) and on the literals given in the WHERE clause as criteria.

But then - where you have the situation where the column is case sensitive, you are pretty much forced to apply the function for every single row processed. My suggestion is to review the table design and if performance is an issue - update the column to be case insensitive and enforce it via UPDATE and INSERT triggers.

--
Billy