在我们的应用程序中,我们在 session 级别配置了不区分大小写的语义:
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_AI;
但是后来我想要一个带有二进制语义的 NAME 列的表,所以我相应地定义了一个基于函数的索引:
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'));
我希望下面的查询能够利用基于函数的索引:
select * from RAW_SCREEN where
nlssort(NAME, 'NLS_SORT=BINARY') = nlssort(N'raw_screen1', 'NLS_SORT=BINARY');
但事实并非如此。查询计划显示了表扫描。在试验时,我发现 NAME 上的一个简单索引可以解决问题:
create unique index RSCR_IDX2 on RAW_SCREEN (NAME);
再次运行查询时,成功使用了 RSCR_IDX2 索引。
现在,这并不奇怪,但我不明白为什么优化器没有使用第一个基于函数的索引。索引表达式与 WHERE 条件中使用的表达式完全匹配。你知道为什么它没有被使用吗?
注意:这是在 Oracle 10.2 上运行的
如果您想尝试一下,这里有一个完整的测试脚本:
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 session 设置,但不在 DDL 中。
这可以说是 NLSSORT(char, 'NLS_SORT=BINARY')
行为的一个错误。
来自 the manual :“如果您指定 BINARY,则此函数返回 char。”
但对于指数而言,情况并非如此。通常情况下,索引表达式不进行任何转换是非常方便的;如果它取决于 session 设置
比像 DBMS_METADATA.GET_DDL 这样的工具必须返回许多 alter session
语句。但在这种情况下,这意味着您可以创建一个永远不会
使用。
解释计划显示了真正的表达。以下是 Oracle 如何在 session 中使用 nlssort
而不显式使用它:
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 删除: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 需要与转换后的表达式完全匹配,这取决于 session 设置和
binary
的异常行为。