为什么NLSSORT索引不用于此查询? [英] Why is the NLSSORT index not used for this query?

问题描述

在我们的应用程序中，我们在会话级别配置了不区分大小写的语义:

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;

但是随后我想要一个带有带有二进制语义的NAME列的表，因此我相应地定义了一个基于函数的索引:

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

但不是.查询计划显示表扫描.在进行实验时，我发现在NAME上使用一个简单的索引即可达到目的:

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

再次运行查询时，已成功使用RSCR_IDX2索引.

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

现在，这并不奇怪，但是我不明白为什么优化器没有使用第一个基于函数的索引.索引表达式与WHERE条件中使用的表达式完全匹配.您知道为什么不使用它吗?

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?

注意:这是在Oracle 10.2上运行的

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

推荐答案

表达式在DML中转换为NLS会话设置，但在DDL中不转换.

Expressions are converted to NLS session settings in DML, but not in DDL.

这可以说是具有NLSSORT(char, 'NLS_SORT=BINARY')行为的bug.
从手册:如果您指定BINARY ，那么此函数将返回char." 但这对索引不是正确.通常，索引表达式不进行任何转换是非常方便的.如果取决于会话设置 而不是DBMS_METADATA.GET_DDL之类的工具将必须返回许多alter session语句.但是在这种情况下，这意味着您可以创建一个永远不会 被使用.

This is arguably a bug with the behavior of NLSSORT(char, 'NLS_SORT=BINARY').
From the manual: "If you specify BINARY, then this function returns char." But that is not true for the index. Normally it is very convenient that the index expression does not undergo any transformation; if it depended on session settings than tools like DBMS_METADATA.GET_DDL would have to return many alter session statements. But in this case it means that you can create an index that will never be used.

解释计划显示 real 表达式.下面是甲骨文在会话中如何使用nlssort而不明确使用它的方法:

The explain plan shows the real expression. Here's how Oracle uses nlssort in a session without it being explicitly used:

alter session set nls_comp=linguistic;
alter session set nls_sort=binary_ai;
drop table raw_screen;
create table raw_screen (
   id   number(10)     constraint rscr_pk primary key,
   name nvarchar2(256) not null
);
create unique index idx_binary_ai
      on raw_screen (nlssort(name, 'nls_sort=binary_ai'));
explain plan for select * from raw_screen where name = n'raw_screen1000';
select * from table(dbms_xplan.display(format=>'basic predicate'));

Plan hash value: 2639454581

-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| RAW_SCREEN    |
|*  2 |   INDEX UNIQUE SCAN         | IDX_BINARY_AI |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("NAME",'nls_sort=''BINARY_AI''')=HEXTORAW('0072006
              10077005F00730063007200650065006E003100300030003000'))

此示例显示nlssort(char, 'nls_sort=binary')被DML丢弃:

This example shows that nlssort(char, 'nls_sort=binary') is dropped by the DML:

alter session set nls_comp=linguistic;
alter session set nls_sort=binary_ai;
drop table raw_screen;
create table raw_screen (
   id   number(10)     constraint rscr_pk primary key,
   name nvarchar2(256) not null
);
create unique index idx_binary_ai on
      raw_screen (nlssort(name, 'nls_sort=binary_ai'));
explain plan for select * from raw_screen where
  nlssort(name,'nls_sort=binary') = nlssort(N'raw_screen1000','nls_sort=binary');
select * from table(dbms_xplan.display(format=>'basic predicate'));

Plan hash value: 237065300

----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|*  1 |  TABLE ACCESS FULL| RAW_SCREEN |
----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"=U'raw_screen1000')

总而言之-索引DDL需要完全匹配经过转换的 表达式，这可能取决于会话设置和binary的异常行为.

In summary - index DDL needs to exactly match the transformed expressions, which can depend on session settings and the unusual behavior of binary.

这篇关于为什么NLSSORT索引不用于此查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！