Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Getachew.

Asked: March 12, 2004 - 11:57 am UTC

Answered by: Tom Kyte - Last updated: August 12, 2013 - 8:19 pm UTC

Category: Database - Version: 9.2.0.4

Viewed 50K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Navigating the world of Oracle database documentation

You Asked

Tom:
First and foremost, thank you for the great work you do for
the Oracle community.

My question is:

- We have a third party application we use for selecting specific
values from several tables. The character values stored in a field could be in any of the following formats:
"Bell", "bell" , "BELL" , "beLL" etc..
1) Is there a way to force (change) values (either to UPPER or Lower case) during a DML other than using triggers?
if not,
2) Is there a way to force a select query (other than using functions UPPER or LOWER) to return all the values regardless of the case that a user enters?

Thank you.

and we said...

1) no

2) in 10g, yes.

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

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

Index created.

ops$tkyte@ORA10G> pause

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

PL/SQL procedure successfully completed.

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

no rows selected

ops$tkyte@ORA10G> pause

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

Session altered.

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

Session altered.

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

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

ops$tkyte@ORA10G> pause

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

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


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 Bytes=12)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=12)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)


and you rated our response

  (49 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Making Oracle Case insensitive

March 13, 2004 - 9:39 pm UTC

Reviewer: Getachew G. from Minneapolis, MN USA

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.

Tom Kyte

Followup  

March 14, 2004 - 9:51 am UTC

it'll make everything case insensitive will be the impact. Not sure I'd do it system wide, but via alter sessions in the app or via an on logon trigger -- sure.

no caveat ?!

March 14, 2004 - 2:17 pm UTC

Reviewer: Gabe

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.



Tom Kyte

Followup  

March 14, 2004 - 2:45 pm UTC

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) 

indeed ...

March 14, 2004 - 3:21 pm UTC

Reviewer: Gabe

"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.

Tom Kyte

Followup  

March 14, 2004 - 7:51 pm UTC

binary_CaseInsensitive
^ ^



March 14, 2004 - 11:15 pm UTC

Reviewer: Bhagat Singh from India

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

Tom Kyte

Followup  

March 15, 2004 - 7:19 am UTC

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? 

LIKE operator for case-insensitive queries

March 15, 2004 - 5:22 pm UTC

Reviewer: Gary from Sydney, Australia

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.



Tom Kyte

Followup  

March 15, 2004 - 6:15 pm UTC

correct but an Oracle Text index can (even in 9i, 8i) do that.




non 10g solution

July 09, 2004 - 7:39 am UTC

Reviewer: Dez from Netherlands

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.



an approach for oracle 9

August 12, 2004 - 2:19 pm UTC

Reviewer: Henry Rabinowitz from New York, NY

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%'


This does not work for LIKE operators

August 13, 2004 - 12:15 pm UTC

Reviewer: Nags from Boston

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;

Tom Kyte

Followup  

August 13, 2004 - 5:52 pm UTC

they do not work with like, even in 10g --

Our application is totally case insensitive

August 13, 2004 - 12:28 pm UTC

Reviewer: Nags from Boston

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

Tom Kyte

Followup  

August 13, 2004 - 5:57 pm UTC

oracle text is totally case insensitive as well -- you might consider using the power of that - it can do tons more than "like"

Case sensitivity an issue for our application

August 14, 2004 - 12:11 am UTC

Reviewer: Nags from Boston

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.



Tom Kyte

Followup  

August 14, 2004 - 1:28 pm UTC

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

ugh.


can you tell me the name of this product, so I can avoid it, it'll work maybe OK on a single database, horribly on the other. no matter which one, it will not exploit any feature of a big piece of software companies pay money for and expect to be used.

(the best was to be database "flexible" -- use only stored procedures, outlaw insert/update/delete/select in the code outside of the database. use the database to death)



Apparently you didn't design to work with both Oracle and SQLServer. Your second to last paragraph "proves that".

Sorry, Oracle <> SQLServer (and it goes the other way as well, there are thousands of things we do that they do not). database independence, the holy grail. I lol everytime I see it. what a waste of good money. I'd rather see you pick one or the other database here and just *use it*.

Wonder how you work in a multiuser situation, given that our locking models are as orthogonal as you can get. Love to look at some of your code, I'm sure I could break every single business rule you *think* you are enforcing in the client and really mess up your data integrity :)



Case sensitivity an issue for our application

August 14, 2004 - 1:46 pm UTC

Reviewer: Nags from Boston

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 ?

Tom Kyte

Followup  

August 14, 2004 - 2:16 pm UTC

CTO in this case means "chief 'totally not clued in' officer"? What is a "C" level person doing making nitty gritty technical decisions that should be engineering level decisions. oh well. I won't get started.


Expensive? We cost less than SQLServer given the amount of functionality you are using (eg: none). Oacle Standard Edition is cheaper than SQL server. I think your CTO read a magazine maybe.

1 million would be a non-discounted 50 cpu license. Your application runs on a 50 cpu machine (nah, nix that, you are running on windows). I just went to store.oracle.com and ordered me 5 named users for $1,500 -- upgraded to $1,830 to get all software updates and complete support.

Use the shadow column trick everywhere
or
start using the database -- function based indexes for example.




Case sensitivity an issue for our application

August 14, 2004 - 2:53 pm UTC

Reviewer: Nags from Boston

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.


Tom Kyte

Followup  

August 14, 2004 - 4:19 pm UTC

we haven't changed pricing drastically in eons. We went from power units to "just by cpu" many many years ago.

Really, its been that way for a long long time. in 2001, it cost no less, no more. And definitely not the orders of magnitude you quote.

Even by cpu, you'll find it way comparable with sqlserver.

Setting case insensitive negates indexes ??

October 19, 2004 - 5:45 pm UTC

Reviewer: Anton from NZ

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

sorting on column by number + upper of that

April 13, 2005 - 12:02 pm UTC

Reviewer: Jelena from Germany

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

Tom Kyte

Followup  

April 13, 2005 - 12:05 pm UTC

it would be sorting on the upper(literal)....

sorry, but in order to sort on the UPPER of column 3, column 3 would have to be uppercased.

but what version?

you can change the nls_sort in a session via alter session without restarting.

NLS_COMP

February 02, 2006 - 2:19 pm UTC

Reviewer: Deepak Haldiya from FL, USA

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
 
   


 

Tom Kyte

Followup  

February 02, 2006 - 2:38 pm UTC

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

 

LIKE operator Works !!!

February 02, 2006 - 3:19 pm UTC

Reviewer: Deepak Haldiya from FL, USA

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

 

Tom Kyte

Followup  

February 03, 2006 - 1:31 pm UTC

binary is just that - binary, bits and bytes binary. bytes sort byte by byte.


Thanks for the followup, this is the new thing I picked up today myself.

LIKE operator Works !!!

February 08, 2006 - 7:32 am UTC

Reviewer: Madhusudan from Pune,India

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..

Tom Kyte

Followup  

February 08, 2006 - 8:16 am UTC

</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>

that is the reason the init.ora isn't working - the client has some NLS parameter set, hence all default, the trigger is the correct approach (or in the application itself)

Use regular expressions instead of 'LIKE' for case-insensitivity

February 08, 2006 - 12:16 pm UTC

Reviewer: Stewart W. Bryson from Atlanta, GA

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>  

Performance Issues

February 28, 2006 - 2:17 pm UTC

Reviewer: Yogesh from Pune, India

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?



Tom Kyte

Followup  

March 01, 2006 - 7:58 am UTC

It is just a collating character set. Anytime you use anything other than binary you are sort of doing this already.

Oracle Text and Case Sensitivity

May 10, 2007 - 7:54 pm UTC

Reviewer: A reader

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?
Tom Kyte

Followup  

May 11, 2007 - 11:32 am UTC

that is decided when you create the index, if you use the default, it is all folded to a consistent case in the supporting data structures (eg; the case is "lost")

May 11, 2007 - 1:54 pm UTC

Reviewer: A reader

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

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

and

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


Tom Kyte

Followup  

May 11, 2007 - 3:40 pm UTC

what you just did?

however, if you are using 10g, and you really have a "single word" in there, then you can use case insensitive session settings.


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


May 12, 2007 - 12:12 pm UTC

Reviewer: A reader

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 || '%';



Tom Kyte

Followup  

May 14, 2007 - 1:04 pm UTC

if you turn it on and off - sure it would work in a connection pool, you would just need to always set it to what you wanted.

if you use LINGUISTIC comparison semantics, like will work (you meant like I think...)

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



May 14, 2007 - 2:24 pm UTC

Reviewer: A reader

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')


Tom Kyte

Followup  

May 14, 2007 - 5:28 pm UTC

all but the "like" with leading '%' would be best served via conventional b*tree indexing.

at which point, a text index might be best (leading wildcard)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37336026927381#37360199308431

"not" typically will "not" use an index, most of the results are "not" like that.

Oracle 9i: - Case Insensitive Query problem

May 22, 2007 - 1:30 am UTC

Reviewer: cPiyush from IND

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.
Tom Kyte

Followup  

May 22, 2007 - 9:26 am UTC

upgrade to 10g.

or - use a view?

Oracle 9i: -CASE INSENSITIVITY

May 25, 2007 - 2:12 am UTC

Reviewer: cPiyush from IND

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.

What statistics should replace 'first_rows' hint?

May 08, 2008 - 7:46 am UTC

Reviewer: A reader from England

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                                                                                            

great thread, one can also ...

March 03, 2010 - 6:37 am UTC

Reviewer: Sokrates

alter session set nls_comp=linguistic nls_sort=binary_ci;


one "alter session" is enough

March 31, 2010 - 4:05 pm UTC

Reviewer: A reader


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>



Tom Kyte

Followup  

April 05, 2010 - 12:45 pm UTC

a test case has everything necessary to reproduce what you are wanting us to see.

You don't have a test case here at all.

from start to finish, you need to have it *all*.

Cannot add UNIQUE constraint using a case-insensitive index

November 18, 2010 - 10:42 am UTC

Reviewer: Martin Rose from Brussels

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.

Tom Kyte

Followup  

November 19, 2010 - 9:20 am UTC

no bug - that index is not appropriate for enforcing uniqueness on TEXT

it is appropriate to enforce uniqueness on the NLSSORT(text,...) - but not TEXT!


you need an index on TEXT alone to make TEXT unique.


Answering my own question.....

November 18, 2010 - 2:51 pm UTC

Reviewer: Martin Rose from Brussels

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 Kyte

Followup  

November 19, 2010 - 9:20 am UTC

yes, it does :)

Case In-Sensitive Queries

February 25, 2013 - 10:40 am UTC

Reviewer: BC from MT, MI

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 Kyte

Followup  

February 25, 2013 - 11:43 am UTC

how many rows in the table
how many blocks in the table
what is the clustering factor of the indexes


Case In-Sensitive Queries

February 25, 2013 - 12:33 pm UTC

Reviewer: BC from MT, MI

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.

Tom Kyte

Followup  

February 25, 2013 - 12:42 pm UTC

this is technically what is happening


the "where column like :x and column2 like :y" have to guess at the cardinality or use bind peeking and fix the cardinality based on first inputs.


we estimate a large number or rows (as opposed to the query with literals, which doesn't guess). couple that with the clustering factor - and the full scan costs out cheaper (but we get the cardinality wrong which means the plan is wrong).


can you sql profile the query in production?
or use first_rows(25) in the query itself? or in the session if appropriate?
or use the cardinality hint to stick in a more appropriate number?

Case In-Sensitive Queries

February 25, 2013 - 12:35 pm UTC

Reviewer: BC from MT, MI

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

Case In-Sensitive Queries

February 25, 2013 - 12:49 pm UTC

Reviewer: BC from MT, MI

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




Case In-Sensitive Queries

February 25, 2013 - 1:08 pm UTC

Reviewer: BC from MT, MI

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

Cardinality deviates in using LIKE predicates

June 07, 2013 - 2:26 pm UTC

Reviewer: Rajeshwaran

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>

Tom Kyte

Followup  

June 07, 2013 - 3:50 pm UTC

you'll probably notice that 3,562 is about 5% of the table (well, exactly 5% after rounding...)

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.




Now, that aside, 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? Even if you asked the optimizer to use an index:

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.



it cannot, even if you "fix" the estimated cardinality:

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.


it cannot - note I said "cannot", not "will not". Like is not supported for indexed accessed case insensitive access. you'll have to index upper(object_name) or lower(object_name) and use "like upper/lower(:x)"

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.


June 07, 2013 - 5:25 pm UTC

Reviewer: A reader

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,
Tom Kyte

Followup  

June 07, 2013 - 6:15 pm UTC

you do not *need* to use % or _ in like

you can use them, but it is not mandatory.


if you don't have any wildcards, like works just as equals would semantically speaking.

Cardinality on Like predicates

June 07, 2013 - 5:28 pm UTC

Reviewer: Rajeshwaran

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'''))


Tom Kyte

Followup  

June 07, 2013 - 6:18 pm UTC

my entire script is supplied.

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


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



I didn't use explain plan like you did, I used the actual plan used as retrieved ffrom v$sql_plan by dbms_xplan.


and you were in 11.2.0.2, I was in 11.2.0.3 - there could be subtle differences - but did you notice that in your "=" plan, it was an access referencing a function on the database column whereas with "like" there was a filter referencing just the database column (no function). same thing was observed in your plans basically.

Predicate changes

June 07, 2013 - 6:59 pm UTC

Reviewer: Rajeshwaran

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'''))

Tom Kyte

Followup  

June 07, 2013 - 8:49 pm UTC

just a difference between explain plan (always be suspicious of explain plan...) and the real plan actually used.

Cannot add UNIQUE constraint using a case-insensitive index

June 14, 2013 - 1:37 pm UTC

Reviewer: A reader

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?
Tom Kyte

Followup  

June 18, 2013 - 3:27 pm UTC

you can use virtual columns in 11g to accomplish this


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


Cannot add UNIQUE constraint using a case-insensitive index

June 19, 2013 - 9:27 am UTC

Reviewer: A reader

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?

Tom Kyte

Followup  

June 19, 2013 - 8:12 pm UTC

if I modify your example just a bit to give name_ci a specific type, it sure looks like it should work - but the optimizer won't even consider it:

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



that said, I think this looks like a bug - do you have access to support to file this very simple test case? if not, let me know and I'll do it.


as for the foreign key - it seems to me that something called "name" would not be immutable - it would be subject to update - if so, a surrogate would be called for (or whatever the primary key of this table is...)


Cannot add UNIQUE constraint using a case-insensitive index

June 19, 2013 - 1:38 pm UTC

Reviewer: A reader

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?
Tom Kyte

Followup  

June 19, 2013 - 8:15 pm UTC

see the last paragraph right above...

Cannot add UNIQUE constraint using a case-insensitive index

June 20, 2013 - 10:22 am UTC

Reviewer: A reader

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...

LIKE works sometimes...

August 09, 2013 - 12:14 pm UTC

Reviewer: A reader

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?



Tom Kyte

Followup  

August 09, 2013 - 4:50 pm UTC

if you have a query that returns a different answer when using a bind versus the exact literal of that bind - please contact support, that is definitely wrong.

the like without a bind is being done as equals, it sees there is no wildcard in there. the like with a bind is using like semantics - it has too because it could have a wildcard.

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>






it does not use the index with a bind because of the like - if you used a wildcard in the literal - it too would have the same plan as the bind.


LIKE works sometimes...

August 12, 2013 - 11:26 am UTC

Reviewer: A reader

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.
Tom Kyte

Followup  

August 12, 2013 - 4:04 pm UTC

again: if you have a query that returns a different answer when using a bind versus the exact literal of that bind - please contact support, that is definitely wrong.


it isn't that it isn't implemented as you expected, it would appear you found a *bug*

Like working sometimes

August 12, 2013 - 7:25 pm UTC

Reviewer: A reader

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?
Tom Kyte

Followup  

August 12, 2013 - 8:19 pm UTC

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



like is not case sensitive. the index you have would not be appropriate.


and this gets to the bottom of the bug - if they rewrite a like to be equals, they will get the "wrong answer" for a like statement.

Like works sometimes

August 14, 2013 - 4:38 am UTC

Reviewer: A reader

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.

Oracle Text - Change Case Sensitivity of Query At Runtime

September 30, 2013 - 3:27 am UTC

Reviewer: LAWRENCE CHOW from Hong Kong

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.

Usage of hidden parameter "_LIKE_WITH_BIND_AS_EQUALITY"

September 11, 2014 - 6:53 am UTC

Reviewer: Deepak from India

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.