case insensitive searching, indexes
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.