Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

case insensitive searching, indexes

jan.vavraSep 27 2011 — edited Oct 12 2011
As I have read I should make my queries case insesitive following way:

alter session set NLS_SORT=BINARY_CI;
alter session set NLS_COMP=LINGUISTIC;

select name from T where name = 'sOmeBody' makes an insesitive search...

But the INDEX i1 ON T(name) will not be used.
I have to create INDEX i2 ON T(NLSSORT(NAME,'nls_sort=''BINARY_CI'''))

Is there any global database option for creating implicitly insensitive indexes?

We write application also for Microsoft SQL Server where such global option is.
Now in the Oracle for accomplying effective insesitivity I have to search all indexes on strings and rebuild them to functional indexes like i2. I'd like if Oracle does it implicitly when creating normal indexes like i1.

Yes I know for database with filled data I should make some type of conversion if it is available.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 9 2011
Added on Sep 27 2011
5 comments
810 views