ORACLE - 如何创建在 NLS_COMP=Linguistic 和 NLS_Sort=Binary_CI 时使用的索引

ORACLE - How do I create indexes that will be used when NLS_COMP=Linguistic and NLS_Sort=Binary_CI

提问by

By default Oracle uses indexes created.

默认情况下，Oracle 使用创建的索引。

When I change to NLS_COMP=Linguistic and NLS_Sort=Binary_CI, I get full table scans.

当我更改为 NLS_COMP=Linguistic 和 NLS_Sort=Binary_CI 时，我会得到全表扫描。

I'd read somewhere that creating an index using (nlssort(name, 'NLS_SORT=BINARY_CI')); Would work.

我读过使用 (nlssort(name, 'NLS_SORT=BINARY_CI')) 创建索引的地方；会工作。

As my attempt below shows, not so much. Even if I force it, the performance does not seem to be what I would expect. This is a trivial example I like to solve this for a table with many millions of rows, so full table scans would be bad.

正如我在下面的尝试所示，不是那么多。即使我强迫它，性能似乎也不是我所期望的。这是一个简单的例子，我喜欢为一个有数百万行的表解决这个问题，所以全表扫描会很糟糕。

So the question is how to I build indexes so they will be used.

所以问题是我如何构建索引以便使用它们。

Thanks

谢谢

-- Setup X

-- 设置 X

create table x ( name varchar2(30)) ;
insert into x select table_name from all_tables;
create index x_ix on x (name);
create index x_ic on x (nlssort(name, 'NLS_SORT=BINARY_CI'));
/   

-- Default Settings

- 默认设置

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY; 
/
set autotrace on 
/
select * from X where NAME like 'x%';

--0 rows selected
--
---------------------------------------------------------------------------
--| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
--|   0 | SELECT STATEMENT |      |     1 |    17 |     1   (0)| 00:00:01 |
--|*  1 |  INDEX RANGE SCAN| X_IX |     1 |    17 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
/
set autotrace off
/

-- Linguistic

-- 语言学

ALTER SESSION SET NLS_COMP=LINGUISTIC; 
ALTER SESSION SET NLS_SORT=BINARY_CI; 
/
set autotrace on
/
select * from X where NAME like 'x%';
--13 rows selected
--
----------------------------------------------------------------------------
--| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
--|   0 | SELECT STATEMENT  |      |     1 |    17 |     3   (0)| 00:00:01 |
--|*  1 |  TABLE ACCESS FULL| X    |     1 |    17 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

select /*+ INDEX( X  X_IX           ) */ * from X where   NAME like 'x%';
--13 rows selected
--
---------------------------------------------------------------------------
--| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
--|   0 | SELECT STATEMENT |      |     1 |    17 |     9   (0)| 00:00:01 |
--|*  1 |  INDEX FULL SCAN | X_IX |     1 |    17 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

select /*+ INDEX( X  X_IC           ) */ * from X where   NAME like 'x%'; 
--13 rows selected
--
--------------------------------------------------------------------------------------
--| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT            |      |     1 |    17 |   448   (1)| 00:00:06 |
--|*  1 |  TABLE ACCESS BY INDEX ROWID| X    |     1 |    17 |   448   (1)| 00:00:06 |
--|   2 |   INDEX FULL SCAN           | X_IC |  1629 |       |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
/

set autotrace off
/

回答by Mor

Since Oracle 11g - LIKE CANuse linguistic indexes. The documentation was modified to:

由于的Oracle 11g -像CAN使用语言索引。文档修改为：

The SQL functions MAX( ) and MIN( ) cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC

Notice they removed the "and also the LIKE operator" part.

请注意，他们删除了“以及 LIKE 运算符”部分。

回答by Vincent Malgrat

I have reproduced your finding on my test DB (10.2.0.3). Upon investigation, it appears the LIKEoperator cannot use the linguistic index -- from the 10gR2 Documentation:

我已经在我的测试数据库 (10.2.0.3) 上复制了您的发现。经调查，LIKE操作员似乎无法使用语言索引——来自10gR2 文档：

The SQL functions MAX( ) and MIN( ), and also the LIKE operator, cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC.

当 NLS_COMP 设置为 LINGUISTIC 时，SQL 函数 MAX( ) 和 MIN( ) 以及 LIKE 运算符不能使用语言索引。

It seems the main purpose of linguistic indexes is to improve the SORT operation.

语言索引的主要目的似乎是改进 SORT 操作。

If your goal is to search on this column in a case-insensitive way, I suggest you create an index on UPPER(name)and build your query with UPPER(name) LIKE UPPER('x%')instead.

如果您的目标是以不区分大小写的方式搜索此列，我建议您在其上创建索引UPPER(name)并使用它UPPER(name) LIKE UPPER('x%')来构建查询。

If you want to use another (more complex) linguistic setting, you might want to look at the Oracle Text indexes.

如果您想使用另一种（更复杂的）语言设置，您可能需要查看Oracle Text 索引。

Edit:There is another workaround: you can replace the LIKE 'ABC%'with:

编辑：还有另一种解决方法：您可以将其替换为LIKE 'ABC%'：

SQL> select * from x where name >= 'ABC' and name < 'ABD';

Execution Plan
----------------------------------------------------------
Plan hash value: 708878862

--------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time  
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    24 |     4   (0)| 00:00:
|   1 |  TABLE ACCESS BY INDEX ROWID| X    |     1 |    24 |     4   (0)| 00:00:
|*  2 |   INDEX RANGE SCAN          | X_IC |     1 |       |     3   (0)| 00:00:
--------------------------------------------------------------------------------

As you can see if you can translate the LIKE expression to an expression with the comparison operators (>and <) the linguistic index might be used.

如您所见，如果您可以将 LIKE 表达式转换为带有比较运算符 (>和<) 的表达式，则可能会使用语言索引。