Why is the NLSSORT index not used for this query?

Question

In our application we have case-insensitive semantics configured at session level:

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

but then I want to have a table with a NAME column with binary semantics, so I defined an function-based index accordingly:

create table RAW_SCREEN (
   ID   number(10)     constraint RSCR_PK primary key,
   NAME nvarchar2(256) not null
);
create unique index RSCR_IDX on RAW_SCREEN (nlssort(NAME, 'NLS_SORT=BINARY'));

I would have expected the query below to take advantage of the function-based index:

select * from RAW_SCREEN where 
    nlssort(NAME, 'NLS_SORT=BINARY') = nlssort(N'raw_screen1', 'NLS_SORT=BINARY');

but it doesn't. The query plan shows a table scan. While experimenting, I've discovered that a simple index on NAME does the trick:

create unique index RSCR_IDX2 on RAW_SCREEN (NAME);

When running the query again, the RSCR_IDX2 index was used successfully.

Now, that is not very surprising, but I can't understand why the first function-based index was not used by the optimizer. The indexed expression matches exactly the expression used in the WHERE condition. Do you have any idea why it wasn't used?

NOTE: This was run on Oracle 10.2

Here's a full test script if you want to try it out:

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

create table RAW_SCREEN (
   ID                   number(10)            constraint RSCR_PK primary key,
   NAME                 nvarchar2(256)        not null
);

create unique index RSCR_IDX on RAW_SCREEN (nlssort(NAME, 'NLS_SORT=BINARY'));
--create unique index RSCR_IDX2 on RAW_SCREEN (NAME);

begin
  for i in 1..10000
  loop
    insert into RAW_SCREEN values (i, 'raw_screen' || i);
  end loop;
end;
/
commit;

select * from RAW_SCREEN where nlssort(NAME, 'NLS_SORT=BINARY') = nlssort(N'raw_screen1000', 'NLS_SORT=BINARY');

user_4180464 · Answer

When you apply a function to a column in the where clause of a query, any corresponding indexes on this column must also include the function for Oracle to make use of them when executing the query. The NLSSORT function can be applied to strings in your where clause automatically be Oracle if you set NLS_COMP and NLS_SORT appropriately.

To enable case-insensitive searching, we must convert the strings stored in the table by a applying a function such as upper(), lower(), etc. We must then also create a function-based index on the column with the same function we use in our queries.

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.

Why is the NLSSORT index not used for this query?

2 Answers