sql - Oracle Db 11.2 NLS_SORT 索引范围扫描性能

Question

我们在“varchar2”列上使用“nls_sort”功能索引。当我们尝试获取唯一值时，索引性能与普通(非功能性)索引一样好。但是，当我们尝试在“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 的付费在线支持文档与 DBA 一起搜索后，我们找到了一个标题为:

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

我不确定分享它的内容是否合法，所以我会写下我从中了解到的内容:

为了使用具有“like”查询效率的语言索引，应该定义一个 session 属性:

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

换句话说，提示将被忽略。