Oracle Db 11.2 NLS_SORT Производительность сканирования диапазона индекса

Question

Мы используем функциональные индексы nls_sort для столбцов varchar2. Когда мы пытаемся получить уникальное значение, производительность индекса так же хороша, как обычный (нефункциональный) индекс. Но когда мы пытаемся написать запрос с планом "сканирования диапазона" по функциональному индексу "nls_sort", мы испытываем серьезную потерю производительности. Я подготовил минимальный тестовый пример, чтобы точно определить проблему.

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';

В нашей системе следующий запрос выполняется в 5.8 с

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

когда мы переключаемся на двоичный

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

этот же запрос выполняется в 0.078 с на этот раз. Есть ли известная проблема с функциональными индексами 'nls_sort', которую мы пропускаем? Как вы думаете, это нормальное поведение?

Answer 1

После поиска с администратором базы данных через платные документы поддержки Oracle, мы нашли документ с заголовком:

Лингвистические индексы, не используемые для LIKE: предикаты BIND (ID документа 1451804.1)

Я не уверен, что совместное использование контента является законным, поэтому я напишу, что я понимаю из него:

Чтобы использовать лингвистический индекс с "похожим" запросом, нужно либо определить свойство сеанса:

alter session set cursor_sharing = 'EXACT'

или используйте подсказку в своем запросе:

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

таким образом, потеря производительности минимальна, но опять же не так быстро, как при использовании обычного индекса. В документе также говорится, что эта подсказка не будет работать с запросом, использующим предикаты bind:

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

будет выполняться с той же производительностью, что и:

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

другими словами, подсказка будет игнорироваться.