[Oracle SQL] NLS_SORT, NLS_COMP, NLSSORT, etc
假设我有如下一张表 test_sort_comp, 注意列NAME上有一个索引。
SQL> select * from test_sort_comp;
NAME
--------------------------------------------------
Frank
frank
frAnk
Bob
BOB
Candy
candy
7 rows selected.
SQL> select index_name, table_name,column_name from user_ind_columns where table_name = 'TEST_SORT_COMP';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
INDX_TEST_SORT_COMP TEST_SORT_COMP NAME
SQL>
现在如果我需要查找Name为frank的记录,很简单,如下 (注意,设置了autotrace,方便查看执行计划)
SQL> select * from test_sort_comp where name = 'frank';
NAME
--------------------------------------------------
frank
Execution Plan
----------------------------------------------------------
Plan hash value: 3593911111
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INDX_TEST_SORT_COMP | 1 | 27 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"='frank')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
很显然,查询结果返回了一条记录,而且采用了index scan的方式。
如果现在我想得到name为frank的记录,但是不管字母的大小写,只要是f,r,a,n,k这几个字母按照这个顺序拼出来就可以,很显然表test_sort_comp中有3条记录满足条件 -- frank, Frank, frAnk.
很容易想到一种方法,通过upper或lower函数,把name转成要么是大写要么是小写,但是由于在name上的index是个普通索引(不是函数索引),因此会采用全表扫描的方式,如下,
NAME
--------------------------------------------------
Frank
frank
frAnk
Execution Plan
----------------------------------------------------------
Plan hash value: 796376470
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 81 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_SORT_COMP | 3 | 81 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("NAME")='FRANK')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
那么可不可以通过改变参数NLS_SORT, NLS_COMP来做呢? 可以尝试一下....
但是如果单单设置NLS_SORT或NLS_COMP的话,是不起作用的,(NLS_COMP依赖于NLS_SORT, NLS_SORT作用于排序,
Session altered.
SQL> select * from test_sort_comp where name = 'frank';
NAME
--------------------------------------------------
frank
Execution Plan
----------------------------------------------------------
Plan hash value: 3593911111
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INDX_TEST_SORT_COMP | 1 | 27 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"='frank')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
接着设置下参数NLS_COMP为ANSI,表示在比较的时候参照NLS_SORT的值,
Session altered.
SQL> select * from test_sort_comp where name = 'frank';
NAME
--------------------------------------------------
Frank
frank
frAnk
Execution Plan
----------------------------------------------------------
Plan hash value: 796376470
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_SORT_COMP | 1 | 27 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6672616E6B00') )
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
虽然结果返回了三条记录,但是这条SQL语句的执行计划显示进行的是Full Table Scan. 虽然没有改变SQL语句的写法,但是ORACLE在执行SQL语句的时候,根据刚才设置的参数NLS_SORT, NLS_COMP对SQL语句进行了改写,
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6672616E6B00') )
其实相当于我们自己显示地用NLSSORT函数来查询,
首先清楚当前session的参数设置,只需要从新connect一下就OK了,
SQL> conn hr/hr;
Connected.
SQL> set autotrace on;
SQL> select * from test_sort_comp where NLSSORT(NAME,'NLS_SORT=BINARY_CI') = NLSSORT('frank', 'NLS_SORT=BINARY_CI');
NAME
--------------------------------------------------
Frank
frank
frAnk
Execution Plan
----------------------------------------------------------
Plan hash value: 796376470
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_SORT_COMP | 1 | 27 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6672616E6B00') )
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
因此通过设置NLS_SORT, NLS_COMP只是可以使得我们在不改变原有SQL的基础上来得到我们想要的结果,但是要注意SQL的执行计划会发生变化,有可能会对性能产生影响。
[Update on 2011-1-5]
本来打算写一篇关于NLS_SORT, NLS_COMP参数的设置的文章,结果发现之前已经写过了,记性好差:(
关于上面讲到的是用NLS_SORT/NLS_COMP对查询性能的影响,如果情况允许,可以通过创建一个FBI来避免full table scan的发生,比如
create index fbi_tab on tab_name(nlssort(column_name, 'nls_sort=''binary_ci'''));
关于NLS_SORT 和 NLS_COMP这两个参数的介绍,可以参见oracle的官方文档,如下
NLS_SORT: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams130.htm#REFRN10127
NLS_COMP:http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams120.htm#REFRN10117
需要注意的是,对NLS_SORT的介绍中有如下一段注释,
Setting NLS_SORT
to anything other than BINARY
causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY
is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY
clause when NLS_SORT
is set to BINARY
. If NLS_SORT
is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
另外注意NLS_SORT和NLS_COMP 可以起修改的scope都是局限于session的,也就是只可以通过ALTER SESSION来改变这两个参数的设置。
SQL> alter system set nls_sort=binary;
alter system set nls_sort=binary
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
SQL> alter system set nls_comp=ansi;
alter system set nls_comp=ansi
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
--------------------------------------
Regards,
FangwenYu