Monday, January 06 2014
Keywords: Oracle, case insensitive, case sensitive, si, queries, query, small case, capital case, lower case, upper case
How to make your Oracle queries case insensitive
By default Oracle queries are case sensitive. SQL Server DBAs have a hard time adjusting to this fact...especially since Microsoft's leading RDBMS is case-insensitive by default...Ever since mid 1990s I haven't seen a single client install their SQL Server with a case-sensitive collation even though that option is clearly provided at setup time. Which would make you think why would Oracle choose such unpopular setting? My guess would be the performance: Case Sensitive searches are logically faster.
This week we had a user story that read "Make existing Oracle 11g database's queries case insensitive with little effort". The story had indeed only been weighted out to have two story points worth of efforts...so the solution had to be something quick and efficient...And with that little info I started exploring.
One of the first thoughts in my mind was "whatever solution I find, it must work for both: indexed as well as non-indexed fields". The reasoning behind it was that if Oracle was by default case sensitive, then its indexes must be stored in a case-sensitive order on the binary tree as well...therefore there was theoretically a possibility that an indexed case-insensitive search would behave differently than a non-indexed case-insensitive search.
My suspicion was proved to be right.
So with that in mind I created a test table in our otherwise case sensitive Oracle database:
create table CLASSMAST ( "PRIMARY_CLASS" varchar2 (100) primary key, "CLASS_DESC" varchar2 (100));
Inserted 5 records with the same ‘hello world’ value but each with a unique mix of lower/upper case letters, into the primary key index field of a table.
In the same 5 records, inserted the same text, with different lower/upper case letters into another non-indexed field.
The above is to make 100% sure that indexed and non-indexed fields will behave the same way! Since indexes are actually saved in a case-sensitive way on the disk...in this case column "PRIMARY_CLASS" is a primary key index of this table.
The default behavior:
The above proves that the default behavior is case-sensitive. The last 2 queries only showed 3 records and not 5.
Lets make the session’s queries case insensitive:
The above queries are now case-insensitive. All requests sent afterwards within the same session will also be case-insensitive.
WHAT ABOUT THE PERFORMANCE?
So now we knew that we were getting the correct results...no records were missing out in the output. However we needed to test the performance. We set the following 2 session parameters in the middle-tier (data access layer), for Oracle connections only..I mention this since our data access layer handles both SQL Server as well as Oracle.
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;
Another way of doing the very same, depending on where and when to use it, would be:
execute immediate 'alter session set NLS_COMP=LINGUISTIC';
execute immediate 'alter session set NLS_SORT=BINARY_CI';
These settings didn't break any regression tests in our application, that was the good news. However some of our complex queries suddenly became a whopping 100x slower on a moderately large table containing only a million records!!!.
The reason, I found out, was that our case-insensitive searches within these sessions were completely invalidating our case-sensitive indexes. And this particular database had lots of varchar2 indexes.
The final solution:
Creating case-insensitive indexes!
create index ci_some_index_name on my_table_name (NLSSORT (my_column_name, 'NLS_SORT=BINARY_CI') ) ;
And we were back to our normal speed. Case closed.
Written by Ramin Haghighat