How to make WHERE clause case insensitive: From SQL Server querying Oracle linked server

sql-server oracle linked-server case-insensitive openquery
Peter Bridger picture Peter Bridger · Sep 7, 2011 · Viewed 32.4k times · Source

We have a MS SQL Server 2005 installation that connects to an Oracle database through a linked server connection.

Lots of SELECT statements are being performed through a series of OPENQUERY() commands. The WHERE clause in the majority of these statements are against VARCHAR columns.

I've heard that if the WHERE clause is case sensitive, it can have a big impact on performance.

So my question is, how can I make sure that the non-binary string WHERE clauses are being performed in a case insensitive way for maximum performance?

Answer

Branko Dimitrijevic picture Branko Dimitrijevic · Sep 7, 2011

It's actually the other way around:

Case sensitive...

WHERE column = :criteria

...will use index on column directly and perform well.

Case insensitivity typically requires something like this...

WHERE UPPER(column) = UPPER(:criteria)

...which does not use index on column and performs poorly (unless you are careful and create a functional index on UPPER(column)).

I'm not sure whether OPENQUERY() changes anything, but from purely Oracle perspective both case-sensitive and insensitive queries can be made performant, with the insensitive ones requiring special care (functional index).

Related questions

"Cannot create an instance of OLE DB provider" error as Windows Authentication user

I am trying to run openrowset from MS SQL Server on an Oracle server. When i execute the following command: select * from OPENROWSET('OraOLEDB.Oracle','srv';'user';'pass', 'select * from table') the following error occurs Msg 7302, Level 16, State 1, Line 1 Cannot …

Read More
Calling Oracle stored procedure with output parameter from SQL Server

I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure): CREATE OR REPLACE PROCEDURE my1.spGetDate(CurrentDate OUT VARCHAR2) IS BEGIN -- …

Read More
linked server problem at sql server while connecting to oracle dbms

I have created a linked-server definition according to the article at : http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/ My aim is to transfer rows to tables at Oracle 11gR2. After creating linked server, whenever I try to select a table using a …

Read More