[Oracle SQL] NLS_SORT, NLS_COMP, NLSSORT, etc

于 2010-04-14 17:13:00 发布
阅读量70 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/fangwenyu/archive/2010/04/14/1711969.html
版权

假设我有如下一张表 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 >   set  autotrace  on
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是个普通索引(不是函数索引），因此会采用全表扫描的方式，如下，

 

SQL >   select   *   from  test_sort_comp  where   upper (name)  =   ' FRANK ' ;

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作用于排序，

SQL >   alter  session  set  NLS_SORT  =  BINARY_CI;

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的值，

 

SQL >   alter  session  set   nls_comp  =  ansi;

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( ' 6672616E6B0 0 ' ) )

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_S ORT=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( ' 6672616E6B0 0 ' ) )

 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 
   
 
  
 
 
 
 
 
 
 
 
 

 

转载于:https://www.cnblogs.com/fangwenyu/archive/2010/04/14/1711969.html

                

        

        




    




    

      

        

            

              
                
                  weixin_30394669
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










              

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值