oracle functional index - ghdrako/doc_snipets GitHub Wiki

select .... where name = lower(:search_string)

create index plch_user_name_i on plch_users(lower(name));

By changing the NLS_COMP parameter to ANSI and the NLS_SORT parameter to BINARY_CI for a session, Oracle will automatically place the NLSSORT function to strings in your query however! In this case, you don't have to change your query, as Oracle does this for you behind the scenes.

select .. where name = :search_string

create index plch_user_name_i on 
  plch_users(nlssort(name, 'NLS_SORT=BINARY_CI'));

Finally, execute the following statements are in your session before running the query:

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