Oracle Db 11.2 NLS_SORT Index Range Scan Performance

Question

We use 'nls_sort' functional indexes on 'varchar2' columns. When we try to fetch a unique value, index performance is just as good as ordinary (non-functional) index. But when we try to write a query with a 'range scan' plan on the 'nls_sort' functional index, we experience serious performance loss. I prepared a minimal test case to pinpoint the problem.

create table scott.nls_test (nls_col varchar2(50));

begin
    for i in 1 .. 5000000
    loop
        insert into scott.nls_test values ('ABC' || dbms_random.string('a', 47));
    end loop;

    for i in 1 .. 50
    loop
        insert into scott.nls_test values ('GHI' || dbms_random.string('a', 47));
    end loop;

    for i in 1 .. 5000000
    loop
        insert into scott.nls_test values ('XYZ' || dbms_random.string('a', 47));
    end loop;
end;

create index nls_test_idx0 on scott.nls_test(nlssort(nls_col, 'nls_sort=''XTURKISH_AI'''))
create index nls_test_idx1 on scott.nls_test(nls_col);

alter session set nls_sort = 'XTURKISH_AI';
alter session set nls_comp = 'LINGUISTIC';

In our system, the following query runs in 5.8s

select count(1) from scott.nls_test where nls_col like 'GHI%';
--INDEX RANGE SCAN | NLS_TEST_IDX0 | 39751

when we switch to binary

alter session set nls_sort = 'BINARY';

select count(1) from scott.nls_test where nls_col like 'GHI%';
--INDEX RANGE SCAN| NLS_TEST_IDX1 |    50

same query runs in 0.078s this time. Is there a known issue about 'nls_sort' functional indexes that we miss? Do you think this behavior is normal?

Answer 1

After searching with a DBA through Oracle's paid online support documents, we found a document with the title:

Linguistic Indexes Not Used For LIKE :BIND Predicates (Doc ID 1451804.1)

I am not sure if sharing its content is legal so I will write down what I understand from it:

In order to use a linguistic index with a 'like' query efficienty one should either define a session property:

alter session set cursor_sharing = 'EXACT'

or use a hint in his query:

select /*+ CURSOR_SHARING_EXACT */ count(1) from scott.nls_test where nls_col like 'GHI%'

this way performance loss is minimal but again not as fast as using an ordinary index. The document also states that this hint will not work with a query using bind predicates:

declare
    a integer;
begin
    execute immediate 'select /*+ CURSOR_SHARING_EXACT */ count(1) from scott.nls_test where nls_col like :1' into a using 'GHI%';

    dbms_output.put_line(a);
end

will execute with the same performance as:

select count(1) from scott.nls_test where nls_col like 'GHI%'

in other words the hint will be ignored.