Thank you again.

I will test and implement this in 10G in a few months.
I am not sure what the impact will be if I set the
two parameters(nls_comp and nls_sort) at the system
level. But, thank you for providing me with a good information.

Regarding #2 and the answer "in 10g, yes" ...

My observations/questions:
1. I'm assuming statistics were gathered automatically (10g feature)
2. Is RBO still available in 10g?
3. I know Cardinality is an estimation ... but why wouldn't the optimizer estimate on the side of caution with "Card=<#rows in table>" (or some function of it) rather than "Card=1"?
4. What was the point of first_rows? For the method to be deterministic, the FBI has to be used (hints are just ... hints). In fact, any query against a table with 3 rows should probably result in a FT ... here the FT would translate in a bug in the application.

I'm highly suspicious of relying on indexes for business logic when querying Oracle (for DML? ... yes, FTIs are very useful as you show in your last book) ... is my suspicion unfounded? Can Oracle really be forced to always use an index (FBI or not)?

Thanks.

1) no, there weren't any 

2) yes, but not supported and not by default

3) small table.  forced the cbo to guess with the hint so the index would be used.

4) to bias the index, table was too small to use an index "for real" otherwise. I was trying to demonstrate that "yes, this case insensitive stuff can be as efficient as case sensitive, given the correct indexes are in place"

I'm not relying on the index for anything here.  I was merely demonstrating that the index can in fact be used transparently.

ops$tkyte@ORA10G> select /*+ FULL(t) */ * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=1 Bytes=12)


Full scans work just as well.  I would never suggest on relying on a query plan for getting data "just so".  Either plan works the same (although they could return the data in very different order of course! but if you need a certain order, that is what order by is all about) 

"I'm not relying on the index for anything here"
I can see now you don't.

So it is all about these:
alter session set nls_comp=ansi; --works in 9i
alter session set nls_sort=binary_ci;

Guess the "binary_ci" is the thing that makes this a 10g solution only. I'll look it up in the docs.

Thanks.

Hello Tom,
may be most of the peoply might not be having 10g right now but I was astonished to see the answer, I was expecting some function based indexes and all any special reason for this.

regards
Bhagat Singh

curious -- you did see a function based index:

ops$tkyte@ORA10G> create index t_idx on
  2  t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );


but any special reason for what exactly? 

Haven't got 10g, so cannot verify this, but it has been reported on another website
(</code> http://www.quest-pipelines.com/pipelines/dba/index.asp <code>, that while = (and presumably <, > etc) all pick up on this case-insensitive setting, the LIKE operator does not.

Also changing the nls_sort parameter means you cannot mix case-sensitive and case-insensitive predicates in a query.
This means you have a choice of
1) Have both case-sensitive and case-insensitive indexes
2) Risk some queries not have the correct sensitivity index available on some predicates
3) Choose whether the database as a whole should be case-sensitive or insensitive, and design your indexes accordingly

Personally, I still prefer the 'old-tech' UPPER(col)='...' with a function based index on the UPPER(col).
That allows the case-insensitivity to be applied at the lowest level so has more flexibility.
For example, with a table with Address, City, State and Zip Code, you could decide to be case-insensitve for Address, but case-sensitive for state because you store them all in upper case.

Recently I encountered the same problem and we couldnt use Function Based Indexes or ad an extra index. The way Oracle Forms translates Queries on Case Insensitive fields gives another option:

select * from t where lower(data)='hello' and (data like 'h%' or data like 'H%')

This way the query will still run alot faster without using Funtion Based Index

Hopefully it will be usefull.

I read in the oracle 9 documentation that one can get case insensitive comparison by saying
alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=GENERIC_BASELETTER;
This does appear to work for queries like
select * from emp where ename = 'miller'
But it does not appear to work with queries using LIKE, such as
select * from emp where ename like 'mill%'

When doing case insensitive searches, the parameters for 9i work, but we cannot use LIKE operators. How to make it work ?

These are the parameters that we are using..

alter session set nls_comp=ansi;
alter session set nls_sort=GENERIC_BASELETTER;

I forgot to mention.. our application is totally case insensitive. That means we always do case insensitive searches.

Please let me explain the issue we have, and I would really appreciate if you can help me.

Our application is web based and has been designed to work with both Oracle and SQL Server. It is a licensing system.

The entire application logic and processing of data is being done in the application. (This design is not under my control) We use no procedures, functions, triggers etc. Some how I forced my management to use unique constraints, referential integrity. We have all columns as NVARCHAR2 & NTEXT. When doing searches, for eg. COMPANY_NAME, we added a shadow column, which the application makes sure that it keeps it up to date. For all other searches, we do have indexes on the necessary columns, but all are case insensitive searches (on SQL Server) We do not use UPPER function at all.

If we have to search for First name, last name, and in the admin section configure the drop downs for country, provinces etc. When we switched to oracle, all the searches started failing. I didn't make sense for us to tell the user that the searches are case sensitive, because the user does not care.

Can I still have a work around in doing case insensitive searches. I cannot use oracle text because my management thinks that dba should have very little knowledge or no knowledge of the database to install our product.

I totally agree to all the issues that you put forth. Its not my decision, my CTO decides it. We did have a lot of data integrity issues, but his argument is

"is we add restrictions on the database it would be difficult if the restriction changes for a different client"

Our application is entirely based on some configuration tables.

Our application was first designed for Oracle only. But when oracle became expensive (about 1 million to own) about 3 years back my CTO decided to make our application portable to both oracle and sql server. And finally our application became more oracle less sql server.

We had lot of issues with multiuser and locking. And a lot of performance issues. I am trying to work with the parameters I have.

Is there an alternative in oracle for case insensitive searches or we just have to live with it ?

Thanks a lot for your help.

I was talking about Oracle being expensive about 3 years back. Not now. Moreover ours is a webbased application, hence we could not use named user license.

Based on your suggestion, we will surely revisit the application.

Hi Tom.

If I set an Oracle 9iR2 database to be case insensitive ie.

alter session set nls_comp=ansi;
alter session set nls_sort=GENERIC_BASELETTER;


And I have an index on say a name column, will the above setting always cause Oracle to do a full table scan and not use the index ?

Thanks

Hi Tom,
I have two questions about sorting.

Application generates sql and sorting clauses dynamically and usually we get something like:

select a, b, c
from abc_table
order by 3

Now i want to make it order by case-insensitive-on-3rd-col and i did:

select a, b, c
from abc_table
order by upper(3)

but it doesn't work. Is it really applying upper to number 3 and then sorting on nothing??? as it seems not to sort at all. (with upper(c) it works, but it's difficult to change application)

And second question, to make it work more intelligent way, i wanted to alter database not session, but it looks as I can only change nls_sort in pfile and need to restart database. As this is 24x7 system, it's not easy, so is there any option like alter system... or alter database set nls_sort, without restart?

Thank you,
Jelena

Hi Tom,

1. I read in this thread that LIKE operator does not work with case insensitive searches.
I tried the following 

alter session set nls_comp=ansi;
alter session set nls_sort=binary_ci ;
SQL> select * from t where data like :x || '%' ;
DATA
--------------------
Hello
HeLlO
HELLO

Correct me if i am wrong in my understanding, but it seems that LIKE works fine, just like =,<,> etc.

2. What's the importance of this statement:
alter session set nls_comp=ansi;

The default nls_comp is binary. The nls_sort=binary_ci does not work fine nls_comp=binary. Aren't we doing a binary case insensitive comparison here?

Thanks
Deepak
 
   


 

you don't show us what you set :x to, I suspect 'H'.... (and they are all like H% - without CI processing...)


But look:

ops$tkyte@ORA10GR2> create table t ( data varchar2(20) );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 'Hello' );

1 row created.

ops$tkyte@ORA10GR2> insert into t values ( 'HeLlO' );

1 row created.

ops$tkyte@ORA10GR2> insert into t values ( 'HELLO' );

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> variable x varchar2(20)
ops$tkyte@ORA10GR2> exec :x := 'hello';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> alter session set nls_comp=ansi;

Session altered.

ops$tkyte@ORA10GR2> alter session set nls_sort=binary_ci;

Session altered.

ops$tkyte@ORA10GR2> select * from t where data = :x;

DATA
--------------------
Hello
HeLlO
HELLO

ops$tkyte@ORA10GR2> select * from t where data like :x||'%';

no rows selected

ops$tkyte@ORA10GR2> select * from t where upper(data) like Upper(:x||'%');

DATA
--------------------
Hello
HeLlO
HELLO

 

Hi Tom,

if you set 

alter session set nls_comp=linguistic ;

LIKE OPERATOR in the WHERE CLAUSE for case insensitive comparison works. (i forgot to replace ansi with linguistic in my previous post).

drop table t ;
create table t ( data varchar2(20) );
insert into t values ( 'Hello' );
insert into t values ( 'HeLlO' );
insert into t values ( 'HELLO' );

alter session set nls_comp=linguistic ;
alter session set nls_sort=binary_ci;

variable x varchar2(25)
exec :x := 'hello';

SQL> select * from t where data like :x;

DATA
--------------------
Hello
HeLlO
HELLO

SQL> select * from t where data like :x || '%';

DATA
--------------------
Hello
HeLlO
HELLO

SQL> select * from t where data = :x ;

DATA
--------------------
Hello
HeLlO
HELLO

For NLS_SORT=ANSI option, Oracle Manual says : A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC.


Tom, could you please elaborate on what does NSL_COMP=BINARY option do? This is the defualt setting set by Oracle.

Thanks
Deepak

 

Thanks Tom & Deepak for very good solution..
Presently working on sqlserver conversion ..
I want to make queries case insensitive...
My boss do not want changes in query so he opted for nls_sort/nls_comp approach

One approach is to use of on logon trigger..
Second approach is to put it in spfile so it will be globally applicable.
I had two queries..

1.
Personally i feel 1st approach will be better as there will be control as i can make it user based case insensitive.
DECLARE
p_user VARCHAR2(50);
begin
execute immediate
'alter session set nls_date_format = ''DD-MM-YYYY'' ';
SELECT user
INTO p_user
FROM dual;
IF p_user='SA' THEN
execute immediate
'alter session set nls_sort = BINARY_CI';
execute immediate
'alter session set nls_comp = linguistic ';
END IF;

end;

2.
while putting in spfile i had crated spfile added parameters. But there is problem though it shows correct parameters it's not behaving as expected. Please advice.

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Feb 8 16:52:07 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@DIAMOND> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY_CI
sys@DIAMOND> show parameter nls_comp



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

nls_comp string linguistic

sys@DIAMOND> ed
Wrote file afiedt.buf

1 select ISDEFAULT ,ISSES_MODIFIABLE, ISSYS_MODIFIABLE ,ISINSTANCE_MODIFIABLE

2* , ISMODIFIED ,ISADJUSTED , ISDEPRECATED from v$PARAMETER where name='nls_sort'

3 /

ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP

--------- ----- --------- ----- ---------- ----- -----

FALSE TRUE FALSE FALSE FALSE FALSE FALSE



sys@DIAMOND> conn scott/tiger@diamond

Connected.

scott@DIAMOND> select * from t_case where ename like 'king';

ENAME

----------

king



scott@DIAMOND> alter session set nls_sort=BINARY_CI;
Session altered.

scott@DIAMOND> select * from t_case where ename like 'king';

ENAME
----------
KING
King
king



Please guide me..

The 'i' match parameter tells Oracle to be case-insensitive.

Indexes won't be used for REGEXP_LIKE, though.

SQL> create table t ( data varchar2(20) );

Table created.

Elapsed: 00:00:00.28
SQL> insert into t values ( 'Hello' );

1 row created.

Elapsed: 00:00:00.01
SQL> insert into t values ( 'HeLlO' );

1 row created.

Elapsed: 00:00:00.01
SQL> insert into t values ( 'HELLO' );

1 row created.

Elapsed: 00:00:00.00
SQL> select * from t where regexp_like(data,'hel','i');

DATA
--------------------
Hello
HeLlO
HELLO

3 rows selected.

Elapsed: 00:00:00.02
SQL>  

Need your opinion … Weather we should be using case insensitive feature, specifically when we are dealing with high performance sensitive queries and TB size databases?

I understand that Oracle Text index allows case insensitive search. Is there any way we can make the query either case sensitive or case insensitive on demand?

What then is the best way to handle the following situation?

SELECT ... WHERE CONTAINS(col1, 'abc') > 0

and

-- exact match
SELECT ... WHERE col1 = 'aBc'

ops$tkyte%ORA10GR2> create table t ( data varchar2(20) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 'Hello' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'HeLlO' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'HELLO' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx_ci on t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

Index created.

ops$tkyte%ORA10GR2> create index t_idx    on t( data );

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>ops$tkyte%ORA10GR2> variable x varchar2(25)
ops$tkyte%ORA10GR2> exec :x := 'Hello';

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select /*+ first_rows */ * from t where data = :x;

DATA
--------------------
Hello


Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_IDX |     1 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DATA"=:X)

Note
-----
   - dynamic sampling used for this statement


ops$tkyte%ORA10GR2> alter session set nls_comp=ansi;

Session altered.

ops$tkyte%ORA10GR2> alter session set nls_sort=binary_ci;

Session altered.

ops$tkyte%ORA10GR2> select /*+ first_rows */ * from t where data = :x;

DATA
--------------------
Hello
HeLlO
HELLO


Execution Plan
----------------------------------------------------------
Plan hash value: 164467589

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX_CI |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("DATA",'nls_sort=''BINARY_CI''')=NLSSORT(:X,'nls_sort=''BI
              NARY_CI'''))

Note
-----
   - dynamic sampling used for this statement

So when I execute a case sensitive query, I turn off the setting, and turn it back on when I execute a case insensitive query? Would this work in an application that uses connection pooling?

Also would this work well if I have a case insensitive query that has wildcard at both ends of the predicate?

select /*+ first_rows */ * from t where data = '%' || :x || '%';

ops$tkyte%ORA10GR2> alter session set nls_comp=LINGUISTIC ;

Session altered.

ops$tkyte%ORA10GR2> alter session set nls_sort=binary_ci;

Session altered.

ops$tkyte%ORA10GR2> select * from t where data like '%'||:x||'%';

DATA
--------------------
Hello
HeLlO
HELLO

ops$tkyte%ORA10GR2> select * from t where data = :x;

DATA
--------------------
Hello
HeLlO
HELLO

If my application needs to support all the following operators, some case sensitive, some case insensitive, would Oracle Text index be the best solution since the Text index supports both case insensitive and wildcard searches very well?


UPPER (<column>) LIKE '%ABC%'
UPPER(<column>) NOT LIKE '%ABC%'
<column> = 'abc'
<column> <> 'abc'
UPPER(<column>) = 'ABC'
UPPER(<column>) LIKE 'ABC%'
UPPER(<column>) LIKE '%ABC'
UPPER(<column>) IN ('ABC', 'DEF', 'XYZ')
UPPER(<column>) NOT IN ('ABC', 'DEF')

Hi Tom,

I have read your followups on this topic.
I have certain constraints: -
1. I can not change my queries(so can't use UPPER(<col>)..)

2. This looks wonderful
alter session set nls_comp=ansi;
alter session set nls_sort=binary_ci;
But 9i doesn't support 'binary_ci' value.

3. I tried this: -
alter session set nls_comp=ansi;
alter session set nls_sort=GENERIC_BASELETTER;
But this doesn't work for LIKE operator.

Please tell me the best solution considering constraint#1.

Info: - I have to fire case-insensitive queries on table which contains more than 3,000,000 records. & has this structure:
create table tabName (sno number,
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200));

Regards,
cPiyush.

Hmmm...

Even if I will use a view then I will have to change all my queries which are currently selecting from <table> & I will have to make them to select from <view>.

I think I will have to upgrade to 10g.

No other choice remains, as other options are making me more complex.

But I am thinking abt one thing that if setting
alter session set nls_comp=ansi;
alter session set nls_sort=GENERIC_BASELETTER;
works for 'equals to' to make case insensitive then why they didn't make it for 'like'(Basically this is also a 'itrative equals to').

Regards,
cPiyush.

I am using Oracle 10.2.0.1

I wanted to repeat your example, but using optimizer statistics rather than the 'first_rows' hint. I don't seem to have the statistics right, as the index isn't being used:

SQL> create table t ( data varchar2(20) );

Table created.

SQL> insert into t
  2  select 'Current' from all_objects;

41052 rows created.

SQL> 
SQL> insert into t
  2  select 'Historical' from dual;

1 row created.

SQL> 
SQL> 
SQL> create index t_idx on
  2  t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

Index created.

SQL> 
SQL> 
SQL> 
SQL> set autotrace traceonly

SQL> alter session set nls_comp=ansi;

Session altered.

SQL> alter session set nls_sort=binary_ci;

Session altered.

SQL> 
SQL> select * from t
  2  where data = 'CURRENT';

41052 rows selected.


Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 470836197                                                                                              
                                                                                                                        
-------------------------------------------------------------------------------------                                   
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT            |       |   465 |  5580 |    18   (0)| 00:00:01 |                                   
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   465 |  5580 |    18   (0)| 00:00:01 |                                   
|*  2 |   INDEX RANGE SCAN          | T_IDX |   186 |       |    57   (0)| 00:00:01 |                                   
-------------------------------------------------------------------------------------                                                                                                                                                          
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   2 - access(NLSSORT("DATA",'nls_sort=''BINARY_CI''')=HEXTORAW('63757272656E                                           
              7400') )                                                                                                  
                                                                                                                        
Note                                                                                                                    
-----                                                                                                                   
   - dynamic sampling used for this statement                                                                           


Statistics
----------------------------------------------------------                                                              
         44  recursive calls                                                                                            
          0  db block gets                                                                                              
       5738  consistent gets                                                                                            
        117  physical reads                                                                                             
          0  redo size                                                                                                  
     553170  bytes sent via SQL*Net to client                                                                           
      30477  bytes received via SQL*Net from client                                                                     
       2738  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
      41052  rows processed                                                                                             

SQL> 
SQL> select * from t
  2  where data = 'HISTORICAL';


Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 470836197                                                                                              
                                                                                                                        
-------------------------------------------------------------------------------------                                   
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT            |       |   465 |  5580 |    18   (0)| 00:00:01 |                                   
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   465 |  5580 |    18   (0)| 00:00:01 |                                   
|*  2 |   INDEX RANGE SCAN          | T_IDX |   186 |       |    57   (0)| 00:00:01 |                                   
-------------------------------------------------------------------------------------                                   
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   2 - access(NLSSORT("DATA",'nls_sort=''BINARY_CI''')=HEXTORAW('686973746F72                                           
              6963616C00') )                                                                                            
                                                                                                                        
Note                                                                                                                    
-----                                                                                                                   
   - dynamic sampling used for this statement                                                                           


Statistics
----------------------------------------------------------                                                              
         11  recursive calls                                                                                            
          0  db block gets                                                                                              
         80  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
        415  bytes sent via SQL*Net to client                                                                           
        381  bytes received via SQL*Net from client                                                                     
          2  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
          1  rows processed                                                                                             
<b>SQL> exec dbms_stats.gather_table_stats('compass',tabname=>'t',cascade=>true,method_opt=>'for all columns size 2',estimate_percent=>null)</b>
PL/SQL procedure successfully completed.

SQL> select * from t
  2  where data = 'CURRENT';

41052 rows selected.


Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 1601196873                                                                                             
                                                                                                                        
--------------------------------------------------------------------------                                              
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                              
--------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT  |      | 41052 |   360K|    22  (14)| 00:00:01 |                                              
|*  1 |  TABLE ACCESS FULL| T    | 41052 |   360K|    22  (14)| 00:00:01 |                                              
--------------------------------------------------------------------------                                              
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   1 - filter(NLSSORT("DATA",'nls_sort=''BINARY_CI''')=HEXTORAW('6375727                                                
              2656E7400') )                                                                                             


Statistics
----------------------------------------------------------                                                              
          8  recursive calls                                                                                            
          0  db block gets                                                                                              
       2823  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
     553170  bytes sent via SQL*Net to client                                                                           
      30477  bytes received via SQL*Net from client                                                                     
       2738  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
      41052  rows processed                                                                                             

SQL> 
SQL> select * from t
  2  where data = 'HISTORICAL';


Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 1601196873                                                                                             
                                                                                                                        
--------------------------------------------------------------------------                                              
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                              
--------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT  |      |     1 |     9 |    22  (14)| 00:00:01 |                                              
|*  1 |  TABLE ACCESS FULL| T    |     1 |     9 |    22  (14)| 00:00:01 |                                              
--------------------------------------------------------------------------                                              
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   1 - filter(NLSSORT("DATA",'nls_sort=''BINARY_CI''')=HEXTORAW('6869737                                                
              46F726963616C00') )                                                                                       


Statistics
----------------------------------------------------------                                                              
          8  recursive calls                                                                                            
          0  db block gets                                                                                              
         86  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
        415  bytes sent via SQL*Net to client                                                                           
        381  bytes received via SQL*Net from client                                                                     
          2  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
          1  rows processed                                                                                            

alter session set nls_comp=linguistic nls_sort=binary_ci;

one "alter session" is enough

I was playing with nls_sort , nls_comp to test the case sensitive search . Interesting enough , when I reset the settings ... Case insensitive search works , even after the resetting the nls_XXX parameters.

SQL> select empno , ename , job from scott.emp where ename like 'smith';

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      8000 smith      CLERK

SQL> show parameter nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string
nls_comp                             string      BINARY
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
nls_territory                        string      AMERICA
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL>

It appears possible to index a column using a case/accent-insensitive unique index, but when I go to create a unique constraint with that index, I am prevented from doing so.

Is this a bug?

CREATE TABLE TEST (TEXT VARCHAR2(20))
/
CREATE UNIQUE INDEX TEST_UK_I ON TEST (NLSSORT(TEXT, 'NLS_SORT=BINARY_AI'))
/
ALTER TABLE TEST 
ADD CONSTRAINT TEST_UK UNIQUE(TEXT) USING INDEX TEST_UK_I
/

ORA-14196: Specified index cannot be used to enforce the constraint.

After a few hours reflection, a good meal and a cup of tea, I've realised that a unique FBI is not the same as a unique column constraint, so naturally it cannot be used to enforce column uniqueness.

In fact, the unique FBI will not be enforcing column uniqueness as I had first assumed, but uniqueness of the function results, which because the function was being used to ignore case & accented characters, it would have prevented these differences from ever being entered into the column.

Does this mean that there must be a 2nd index (genuinely unique) in order to achieve this constraint?

I guess it does...

Tom,

We have a HP application that does not support case insensitive lookups, to enable this functionality they recommended that we set the nls_sort and nls_comp values to binary_ci and linguistic, By doing this the application work as intended but the database performance suffers. We have created several FBI's, they work well however the below query baffles me, it does not use the index any ideas or tip on how I can make it use the index. Does Oracle not support linguistic lookups with bind variables and the like clause ?

This query is used like a zillion times in each users session, the amount of FTS's is killing us. Please help.

SM921@proddb SQL>select   *
  2  from     nls_database_parameters
  3  where    parameter in( 'NLS_SORT', 'NLS_COMP' );

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_SORT                       BINARY_CI
NLS_COMP                       LINGUISTIC

SM921@proddb SQL>select   *
  2  from     nls_instance_parameters
  3  where    parameter in( 'NLS_SORT', 'NLS_COMP' );

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_SORT                       BINARY_CI
NLS_COMP                       LINGUISTIC

SM921@proddb SQL>select   *
  2  from     nls_session_parameters
  3  where    parameter in( 'NLS_SORT', 'NLS_COMP' );

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_SORT                       BINARY_CI
NLS_COMP                       LINGUISTIC

SM921@proddb SQL>set autotrace trace;
SM921@proddb SQL>variable l_open varchar2(32);
SM921@proddb SQL>exec :l_open := 'Open%';

PL/SQL procedure successfully completed.

SM921@proddb SQL>variable l_approval_status varchar2(32);
SM921@proddb SQL>exec :l_approval_status := 'denied%'

PL/SQL procedure successfully completed.

SM921@proddb SQL>select   m1."INCIDENT_ID"
  2  from     incidentsm1 m1
  3  where (( m1."OPEN" like :l_open
  4  and      m1."APPROVAL_STATUS" like :l_approval_status
  5        ))
  6  order by m1."INCIDENT_ID" asc;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2163109052

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  1086 | 31494 |  5966   (9)| 00:00:44 |
|   1 |  SORT ORDER BY     |             |  1086 | 31494 |  5966   (9)| 00:00:44 |
|*  2 |   TABLE ACCESS FULL| INCIDENTSM1 |  1086 | 31494 |  5965   (9)| 00:00:44 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("M1"."OPEN" LIKE :L_OPEN AND "M1"."APPROVAL_STATUS" LIKE
              :L_APPROVAL_STATUS)


Statistics
----------------------------------------------------------
        120  recursive calls
          0  db block gets
      53981  consistent gets
      53796  physical reads
          0  redo size
        167  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed


SM921@proddb SQL>select   m1."INCIDENT_ID"
  2  from     incidentsm1 m1
  3  where (( m1."OPEN" like 'Open%'
  4  and      m1."APPROVAL_STATUS" like 'denied%'
  5        ))
  6  order by m1."INCIDENT_ID" asc;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2428347282

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    40 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY               |                     |     1 |    40 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| INCIDENTSM1         |     1 |    40 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INCIDENTSM1_IDX03_1 |     1 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(NLSSORT("OPEN",'nls_sort=''BINARY_CI''')>=HEXTORAW('6F70656E00')  AND
              NLSSORT("APPROVAL_STATUS",'nls_sort=''BINARY_CI''')>=HEXTORAW('64656E69656400')  AND
              NLSSORT("OPEN",'nls_sort=''BINARY_CI''')<HEXTORAW('6F70656F00')  AND
              NLSSORT("APPROVAL_STATUS",'nls_sort=''BINARY_CI''')<HEXTORAW('64656E69656500') )
       filter(NLSSORT("APPROVAL_STATUS",'nls_sort=''BINARY_CI''')>=HEXTORAW('64656E69656400'
              )  AND NLSSORT("APPROVAL_STATUS",'nls_sort=''BINARY_CI''')<HEXTORAW('64656E69656500') )


Statistics
----------------------------------------------------------
        120  recursive calls
          0  db block gets
         41  consistent gets
          1  physical reads
          0  redo size
        176  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Tom,

Looks like the clustering factor of some of the indexes are very high. Most of the indexes are function based indexes they contain the "nls_sort( column_name, 'nls_sort = binary_ci')" keywords. Any recommendations or tips on how to reduce the clustering factor of these indexes. We can't change the queries and support.

This is the problem with Database Agnostic software, I don't understand why people keep implementing such applications.

sm921@proddb SQL>select   table_name,
  2           blocks,
  3           num_rows
  4  from     dba_tables
  5  where    table_name = 'INCIDENTSM1'
  6  and      owner      = 'SM921';

TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
INCIDENTSM1                         54128     402265


sm921@proddb SQL>select   table_name,
  2           index_name,
  3           clustering_factor
  4  from     dba_indexes
  5  where    table_name = 'INCIDENTSM1'
  6  and      table_owner = 'SM921';

TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
INCIDENTSM1                    INCIDENTSM1_IDX03_1                       59,659
INCIDENTSM1                    INCIDENTSM1A52E0B5B                       55,003
INCIDENTSM1                    INCIDENTSM122DEDF93                       56,143
INCIDENTSM1                    INCIDENTSM1BCDE1A6A                      368,541
INCIDENTSM1                    INCIDENTSM1DDD20FFB                      210,559
INCIDENTSM1                    INCIDENTSM12F0DC887                      345,052
INCIDENTSM1                    INCIDENTSM13E7ABCE8                      380,941
INCIDENTSM1                    INCIDENTSM17F375084                      377,198
INCIDENTSM1                    INCIDENTSM180E63CA9                       73,836
INCIDENTSM1                    INCIDENTSM1E2940916                       99,883
INCIDENTSM1                    INCIDENTSM153CE2372                        1,812
INCIDENTSM1                    INCIDENTSM1FDDFE58E                       69,285
INCIDENTSM1                    INCIDENTSM1E5E02419                      160,513
INCIDENTSM1                    INCIDENTSM1_IDX01                        102,234
INCIDENTSM1                    INCIDENTSM1_IDX02                        381,086
INCIDENTSM1                    INCIDENTSMAGIC                           368,541
INCIDENTSM1                    INCIDENTSM1_IDX03                         59,656
INCIDENTSM1                    SYS_IL0000146322C00009$$
INCIDENTSM1                    SYS_IL0000146322C00016$$
INCIDENTSM1                    SYS_IL0000146322C00043$$
INCIDENTSM1                    SYS_IL0000146322C00097$$
INCIDENTSM1                    SYS_IL0000146322C00098$$
INCIDENTSM1                    SYS_IL0000146322C00111$$

23 rows selected.

We can't change the queries and support from the Vendor is, let just say We have a better chance of setting up a colony on Mars in the very near future .... lol

Tom,

Yes, We can most certainly sql profile the query in production, I will hop on it right away.

I will also try the first_rows and cardinality hint.

Thank you so much

BC

Tom,

I can't thank you enough for your help, you are simply the best .....

sm921@proddb SQL>alter session set optimizer_mode = first_rows_100;

Session altered.

sm921@proddb SQL>set autotrace trace;
sm921@proddb SQL>variable l_open varchar2(32);
sm921@proddb SQL>exec :l_open := 'Open%';
PL/SQL procedure successfully completed.
sm921@proddb SQL>variable l_approval_status varchar2(32);
sm921@proddb SQL>exec :l_approval_status := 'denied%'
PL/SQL procedure successfully completed.
sm921@proddb SQL>set autotrace trace
sm921@proddb SQL>select   m1."INCIDENT_ID"
  2  from     incidentsm1 m1
  3  where (( m1."OPEN" like :l_open
  4  and      m1."APPROVAL_STATUS" like :l_approval_status
  5        ))
  6  order by m1."INCIDENT_ID" asc;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1518124975

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  1086 | 20634 |   355   (4)| 00:00:03 |
|   1 |  SORT ORDER BY               |                   |  1086 | 20634 |   355   (4)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| INCIDENTSM1       |  1086 | 20634 |   354   (4)| 00:00:03 |
|*  3 |    INDEX RANGE SCAN          | INCIDENTSM1_IDX03 |  1086 |       |   191   (6)| 00:00:02 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("M1"."OPEN" LIKE :L_OPEN AND "M1"."APPROVAL_STATUS" LIKE :L_APPROVAL_STATUS)
       filter("M1"."OPEN" LIKE :L_OPEN AND "M1"."APPROVAL_STATUS" LIKE :L_APPROVAL_STATUS)

Statistics
----------------------------------------------------------
        120  recursive calls
          0  db block gets
         41  consistent gets
          2  physical reads
          0  redo size
        176  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed


Thank you

BC

Tom:

I am not sure why cardinality deviates when using LIKE operator. ( I have 2 rows for object_name='emp' in table 'T') How can I make LIKE query to make use of index ?

create table t 
nologging as
select * from all_objects;
create index t_idx on t( nlssort(object_name, 'nls_sort=binary_ci')) ;
begin
 dbms_stats.gather_table_stats
 (ownname=>user,
  tabname=>'T',
  method_opt=>'for all indexed columns size 254');
end;
/
alter session set nls_sort=binary_ci;
alter session set nls_comp=linguistic;

variable x varchar2(30);
exec :x :='emp';

rajesh@ORA11G> select * from t where object_name = :x;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   244 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   244 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("OBJECT_NAME",'nls_sort=''BINARY_CI''')=NLSSORT(:X,'nls
              _sort=''BINARY_CI'''))

rajesh@ORA11G>
rajesh@ORA11G> select * from t where object_name like :x;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3562 |   424K|   286   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  3562 |   424K|   286   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE :X)

rajesh@ORA11G>

ops$tkyte%ORA11GR2> select count(*), count(*) * 0.05 from t;

  COUNT(*) COUNT(*)*0.05
---------- -------------
     72956        3647.8

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x varchar2(30);
ops$tkyte%ORA11GR2> exec :x :='emp'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t where object_name = :x;


ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------
SQL_ID  aqcwsuqst4f9c, child number 0
-------------------------------------
select * from t where object_name = :x

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   244 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00016$"=NLSSORT(:X,'nls_sort=''BINARY_CI'''))


19 rows selected.

ops$tkyte%ORA11GR2> select * from t where object_name like :x;
...

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  dusy9vva55s0a, child number 0
-------------------------------------
select * from t where object_name like :x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   291 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |  3648 |   434K|   291   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE :X)


18 rows selected.

Select /*+ index( 't', 't_idx' ) */ * from t where object_name like :x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   291 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |  3648 |   434K|   291   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE :X)


18 rows selected.

Select /*+ opt_param( '_like_with_bind_as_equality', 'true' ) */ * from
t where object_name like :x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   291 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |    33 |  4026 |   291   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME" LIKE :X)


19 rows selected.

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx2 on t( upper(object_name) );

Index created.

ops$tkyte%ORA11GR2> Select * from t where upper(object_name) like upper(:x);


ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID  4d7143j85v3gk, child number 0
-------------------------------------
Select * from t where upper(object_name) like upper(:x)

Plan hash value: 3371054274

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |   289 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |  3648 |   434K|   289   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T_IDX2 |   657 |       |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00017$" LIKE UPPER(:X))
       filter("T"."SYS_NC00017$" LIKE UPPER(:X))


20 rows selected.

Hi tom,

just a basic question when we use like operator we need to use % to say which part of column to search i.e. leading, trailing or in-between
('abc%' or '%abc' or '%abc%') but when using bind varaiable as in above example like :x (where x=abc) which is equivalent to col like 'abc' which in turn equal to
col=abc right? or I miss something.

Thank you,

look at the predicates there - note how one of them changed the column being accessed to a hidden, virtual column and the other did not?

What kind of script you used Tom? you got predicate information like this. when I tried the same in 11.2.0.2 on Linux 64bit I got different.

Your predicate Information

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00016$"=NLSSORT(:X,'nls_sort=''BINARY_CI'''))

My predicate Information

rajesh@ORA11G> select * from t where object_name = :x;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   248 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   248 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("OBJECT_NAME",'nls_sort=''BINARY_CI''')=NLSSORT(:X,'nls
              _sort=''BINARY_CI'''))

ops$tkyte%ORA11GR2> Select * from t where upper(object_name) like upper(:x);


ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

now my predicate got changed into a hidden/ virtual column in 11.2.0.1 with display_cursor()

rajesh@ORA11G> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  aqcwsuqst4f9c, child number 0
-------------------------------------
select * from t where object_name = :x

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   244 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00016$"=NLSSORT(:X,'nls_sort=''BINARY_CI'''))

In the question posed above, is it not possible to declare a unique constraint using index TEST_UK_I ?

I just thought adding a UNIQUE CONSTRAINT was good way to document the data model and also help the optimizer ?

In our system, we've got declarative UNIQUE CONSTRAINTS on many tables which ARE case-sensitive, but we have 1 or 2 tables where are case-insensitive and I guess for those, we can only create unique case-insensitive indexes and not declarative UNIQUE constraints?

ops$tkyte%ORA11GR2> create table t
  2  ( name varchar2(20),
  3    upper_name varchar2(20) generated always as (upper(name)),
  4    constraint upper_name_unique unique(upper_name)
  5     );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (name) values ( 'tom' );

1 row created.

ops$tkyte%ORA11GR2> insert into t (name) values ( 'Tom' );
insert into t (name) values ( 'Tom' )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.UPPER_NAME_UNIQUE) violated

Thank you.

I'm not using UPPER because it only works with a limited characterset. So I've modified your solution to this;

create table t 
  (name varchar2(20 char),
   name_ci generated always as (nlssort(name,'NLS_SORT=GENERIC_M_CI')) virtual,
   constraint t_uk unique (name_ci)
  );

insert into t(name) values ('tom');
insert into t(name) values ('Tom');
ORA-00001: unique constraint T_UK violated

insert into t(name) values ('Grosse');
insert into t(name) values ('große');
ORA-00001: unique constraint T_UK violated

I now have 2 issues;

How to perform a case-insensitive select on T which will make use of the index T_UK ?

exec dbms_stats.set_table_stats(user,'t',numrows=>2000000);

alter session set nls_comp=linguistic;
alter session set nls_sort=generic_m_ci;

select * from t where name_ci = :name_ci;

Full table scan with select above.

So I reverted back to the original solution with a case-insensitive unique index and dropped the virtual column and unique constraint;

create unique index t_uk on t (nlssort(name, 'NLS_SORT=GENERIC_M_CI'));

Now the optimizer is performing a INDEX UNIQUE SCAN on T_UK as expected;

alter session set nls_comp=linguistic;
alter session set nls_sort=generic_m_ci;
select * from t where name = :name;

However, this solution leads me to the second issue which is I have a another table with a foreign key to t;

create table t2 (data varchar2(1), name references t(name_ci));

The FK works for the VIRTUAL column + UNIQUE constraint solution, but I get ORA-02270 with the original UNIQUE index solution;

create table t2 (data varchar2(1), name references t(name));
ORA-02270: no matching unique or primary key for this column-list

I have thought about using a surrogate primary key on T with a sequence and having a FK on T2 using the surrogate, but I just wonder if I can solve it without?

ops$tkyte%ORA11GR2> create table t
  2    (name varchar2(20 char),
  3     name_ci varchar2(340 char) generated always as (nlssort(name,'NLS_SORT=GENERIC_M_CI')) virtual,
  4     constraint t_uk unique (name_ci)
  5    );
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t(name) values ('tom');
ops$tkyte%ORA11GR2> insert into t(name) values ('Tom');
insert into t(name) values ('Tom')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_UK) violated


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats(user,'t',numrows=>2000000);
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter session set nls_comp=linguistic;
ops$tkyte%ORA11GR2> alter session set nls_sort=generic_m_ci;
ops$tkyte%ORA11GR2> variable name_ci varchar2(30)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select /*+ first_rows(1) index(t t_uk) */ * from t where name_ci = :name_ci;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   100 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("NAME_CI",'nls_sort=''GENERIC_M_CI''')=NLSSORT(:
              NAME_CI,'nls_sort=''GENERIC_M_CI'''))

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t drop column name_ci;
ops$tkyte%ORA11GR2> create unique index t_uk on t (nlssort(name, 'NLS_SORT=GENERIC_M_CI'));
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where name = :name_ci;

Execution Plan
----------------------------------------------------------
Plan hash value: 3043225440

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    99 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    99 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_UK |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("NAME",'nls_sort=''GENERIC_M_CI''')=NLSSORT(:NAME_CI,'
              nls_sort=''GENERIC_M_CI'''))

ops$tkyte%ORA11GR2> set autotrace off

I think I've come up with a solution to the 2 issues.

alter table t add constraint t_pk primary key
(name);

create unique index t_uk on t (nlssort(name,'NLS_SORT=GENERIC_M_CI'));

The primary key constraint will enable foreign key references to T.

The index will enforce uniqueness and enable case-insensitive searches using index scan.

Having 2 indexes seems to bit of a hack to get round the issues, is there a better way?

Thank you.

I just spotted something else, if you changed;

select /*+ first_rows(1) index(t t_uk) */ * from t where name_ci = :name_ci;

to

select /*+ first_rows(1) index(t t_uk) */ * from t where name = :name_ci;

It will now do a UNIQUE index scan on T_UK ! I guess it sorts of makes sense...

We're on 11.2.0.3

create table t (word varchar2(100 char)
insert into t(name) values ('GROSSE');
insert into t(name) values ('Grosse');
insert into t(name) values ('große');
create index ti1 on t(nlssort(word,'NLS_SORT=BINARY_CI'));
create index ti2 on t(nlssort(word,'NLS_SORT=GENERIC_M_CI'));
exec dbms_stats.gather_table_stats(user,'T',casecade=>true);

alter session set nls_comp=linguistic;
alter session set nls_sort=binary_ci;

select * from t where word = :word; -- where :word=grosse
returns - GROSSE and Grosse
index used = t1 according to autotrace

select * from t where word = 'grosse'
returns - GROSSE and Grosse
index used = t1 according to autotrace

select * from t where word like :word; -- where :word=grosse
returns - GROSSE, Grosse and große
FULL table scan according to autotrace

select * from t where word like 'grosse';
returns - GROSSE and Grosse
index used = t1 according to autotrace

A number of things wrong here; 

1) BINARY_CI should not return große 
2) Why does LIKE with BIND variable return große and EQUAL to doesn't ?
3) Why likes LIKE with BIND and without BIND work differently ?
4) Maybe attributed to 1) to 3) above, but index usage is inconsistent

Now change nls setting;

alter session set nls_sort=generic_m_ci;

select * from t where word = :word; -- where :word=grosse
returns - GROSSE, Grosse and große
index used = t2 according to autotrace

select * from t where word = 'grosse'
returns - GROSSE, Grosse and große
index used = t2 according to autotrace

select * from t where word like :word; -- where :word=grosse
returns - GROSSE, Grosse and große
FULL table scan according to autotrace

select * from t where word like 'grosse';
returns - GROSSE, Grosse and große
index used = t2 according to autotrace

Now the problem here is 
1) Why doesn't it use an index with the BIND variable?

ops$tkyte%ORA11GR2> select * from scott.emp where ename like 'x';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

<b>   1 - filter("ENAME"='x')</b>


ops$tkyte%ORA11GR2> variable x varchar2(20)
ops$tkyte%ORA11GR2> select * from scott.emp where ename like :x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    76 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    76 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
</b>
   1 - filter("ENAME" LIKE :X)
</b>

Thanks, that explains why

select * from t where word like 'grosse';
is treated the same as
select * from t where word = 'grosse';

by the optimizer.

You say that "it does not use the index with a bind because of the like" - is that because the BIND value could contain something like '%rosse' ? What I find strange is that if I didn't have case-insensitive requirement, then;

alter session set nls_comp=binary;
alter session set nls_sort=binary;
create index t_normal on t (word);
select * from t where word like :word; -- :word = gro%e

would do a index range scan on t_normal. Whereas;

alter session set nls_comp=lingusitic;
alter session set nls_sort=generic_m_ci;
create index t_idx1 on t (nlssort(word,'NLS_SORT=GENERIC_M_CI'));
select * from t where word like :word; -- :word = gro%e

would perform a full table scan.

So apart from getting wrong answers, I'm also getting poor performance. It seems case-insensitive queries just hasn't been implemented as one would expect in Oracle.

ok, thank you.

The wrong result is a bug, but would you say not using the INDEX is also a bug ? Or am I doing something wrong to cause the optimizer to ignore the index?

ops$tkyte%ORA11GR2> create table t ( data varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 'Hello' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'HeLlO' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'HELLO' );

1 row created.

ops$tkyte%ORA11GR2> variable x varchar2(20)
ops$tkyte%ORA11GR2> exec :x := 'hello';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> alter session set nls_comp=ansi;

Session altered.

ops$tkyte%ORA11GR2> alter session set nls_sort=binary_ci;

Session altered.

ops$tkyte%ORA11GR2> select * from t where data = :x;

DATA
--------------------
Hello
HeLlO
HELLO

ops$tkyte%ORA11GR2> select * from t where data like :x||'%';

no rows selected

You say like is not case sensitive. the index you have would not be appropriate. - you're suppose to be able to perform case insensitive "like" according to the section Case Sensitivity in the docs;

http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions007.htm#i1034153

...and a index along the lines of - create index tx on t (nlssort(word,'NLS_SORT=GENERIC_M_CI')); could be used. I guess I'm finding out that it ONLY works for "=" and not "like".

In response to user input, whether they enter "grosse", "gros%","GROSSE" or "große", if we issue this query;

alter session set nls_comp=linguistic;
alter session set nls_sort=generic_m_ci;
select * from t where word like :b1;

We get the right answer, just not the performance. I just thought maybe it's to with statistics on that column.

I understand that Oracle Text can have case-sensitive index created for a text column. And for a text column with Oracle Text case-sensitive index, the searching on that column using Oracle Text CONTAINS operator will be case sensitive.
We have an application of full text searching on documents based on Oracle Text. There is a requirement that the case sensitivity of a search can be chosen by the user at run time. That is, use can raise 2 queries on the same text column of documents, one is case sensitive and the other is case insensitive. My questions are:

(1) Can the case sensitivity of a search with Oracle Text CONTAINS operator be changed at run time, and how?

(2) Can the "alter session set NLS_SORT=BINARY_CI;alter session set NLS_COMP=LINGUISTIC;" be used to turn off the case sensitivity in a search that use Oracle Text CONTAINS operator and with a case-sensitive index? And will these 2 alter session settings cause the search to use full table scan to achieve case-insensitive in the search?

Thanks for your help.

Hello Tom,

First and foremost, thank you for sharing your knowledge and for the guidance you do work .
Recently in my current project I observed that irrespective of usage of indexed column we are seeing SQL is performing poorly.

======================================================
Original SQL:
============
select count(*) from (select 1 as rowCount from XYZ where type = :1 and status = :2 and keyword like lower(:3));

Predicate: filter "keyword" like lower(:3)
Cost: 6137
Time: 00:01:14

Modified SQL:
============
select count(*) from (select /*+ opt_param( '_like_with_bind_as_equality', 'true' ) */ 1 as rowCount from XYZ where type = :1 and status = :2 and keyword like lower(:3));

Predicate: filter "keyword" like lower(:3)
Cost: 5
Time: 00:00:01
========================================================

Please suggest whether or not we can use that hidden parameter as a hint. As this is an undocumented parameter we cant risk it to add in init.ora file.

Thanks in advance.