Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Oracle 11g Case Insensitive Issue

    Hi,
    For our .net framework 4.0 Application we need case insenstive search. So I have tried setting NLS_COMP=LINGUISTIC & NLS_SORT=BINARY_CI in environmental variable.. But this is not working. But when I tried firing trigger on each logon and setting NLS_COMP for each session.. It is working.. But because of performance reason we don't want to fire on trigger for each database logon.

    Pls Let me know why it is not working setting environmental variable. We are using Oracle 11g client and 10g server and connecting from our .net application through ODP.NET.

    Regards,
    Raghu

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Not sure about the .net interface, but can you issue an "alter session" statement from there ?

    It works in Sql*Plus:

    Code:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> create table test (col varchar2(128));
    Table created.
    
    SQL> insert into test values ('alabama');
    1 row created.
    SQL> insert into test values ('Alaska');
    1 row created.
    SQL> insert into test values ('arizona');
    1 row created.
    SQL> insert into test values ('Arkansas');
    1 row created.
    
    SQL> select col from test where col like 'a%';
    
    COL
    ----------------------------------------------
    alabama
    arizona
    
    SQL> alter session set NLS_SORT=BINARY_CI;
    Session altered.
    
    SQL> alter session set NLS_COMP=LINGUISTIC;
    Session altered.
    
    SQL> select col from test where col like 'a%';
    
    COL
    ----------------------------------------------
    alabama
    Alaska
    arizona
    Arkansas
    
    SQL>
    Addendum:
    Did some digging and found out a couple of things:

    1.) It worked for me with environment variables for DOS and Linux environments in any client/server combination (but all 11.2.0.3)
    2.) This feature ( NLS_COMP=LINGUISTIC) was introduced first in 10.2. Are you sure, your server isn't 10.1 ?
    3.) Oracle obviously applies the "upper" function internally. The execution plans changed to full table scans or at least index full scans in all cases I tested.
    Last edited by magicwand; 04-05-12 at 20:41. Reason: Added some findings
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't use .net.

    If @magicwand's suggestion doesn't "work" with .net, maybe you could use an old trick, such as UPPER function:
    Code:
    select col from test where UPPER(col) like UPPER('a%');
    EDIT: Nah! Disregard this message, I must be blind for not seeing the same said by @magicwand in the 3rd addendum point.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by magicwand View Post
    3.) Oracle obviously applies the "upper" function internally. The execution plans changed to full table scans or at least index full scans in all cases I tested.
    But you can create an index to support that query:

    Code:
    SQL> CREATE INDEX idx_lastname_ci
      2         ON employees (nlssort(last_name, 'NLS_SORT=BINARY_CI'));
    
    Index created.
    
    SQL> set autotrace traceonly explain
    
    SQL> select count(*)
      2  from employees
      3  where last_name = 'foo';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1756381138
    
    --------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |     1 |     9 |   518   (1)| 00:00:07 |
    |   1 |  SORT AGGREGATE    |           |     1 |     9 |            |          |
    |*  2 |   TABLE ACCESS FULL| EMPLOYEES |   172 |  1548 |   518   (1)| 00:00:07 |
    --------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("LAST_NAME"='foo')
    
    SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
    
    Session altered.
    
    SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
    
    Session altered.
    
    SQL> select count(*)
      2  from employees
      3  where last_name = 'foo';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1463289962
     
    -------------------------------------------------------------------------------------
    | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                 |     1 |    40 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |                 |     1 |    40 |            |          |
    |*  2 |   INDEX RANGE SCAN| IDX_LASTNAME_CI |  3000 |   117K|     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access(NLSSORT("LAST_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('666F6F00') )
    If the columns contain any language other than english, I would strongly recommend not using BINARY_CI, but e.g. XGerman_CI or XFrench_AI otherwise the case-insensitive comparison of special characters will not work correctly (think, "résume" or

    For details see the manual: Linguistic Sorting and String Searching

    An expresssion "where upper(colname) = upper('Foo')" can be supported by creating a function based index on that expression.

    If a locale aware comparison is needed, the upper() function can also be called with an approriate nls_comp parameter (see the above link to the manual).

    Quote Originally Posted by raghuk31
    But because of performance reason we don't want to fire on trigger for each database logon
    What "performance reasons"?
    Are you simply assuming this will be "slow" or did you actually test this in a real environment?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What kind of performance hit are you getting with the login trigger. The alter sessions and the trigger should happen so fast you wouldn't see any delay.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Apr 2012
    Posts
    2

    Oracle case sensitivity issue

    Hey,
    The client do not want to execute any triggers.. So that option is out & they don't want to convert to upper case also.. So let me know any other suggestion..

    Its seems .NET 4 & ODP.Net connectivity is having some problem.. Becos some other team also facing same problem on upgrading from .Net 2.0 to 4.0

    Regards,
    Raghu

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •