Oracle中使用大小写不敏感的查询和排序

Oracle中使⽤⼤⼩写不敏感的查询和排序
前⼏天同事问到如何能使oracle中的查询⼤⼩写不敏感,为此我特意查阅了⼀下oracle的官⽅⽂档.总结如下:
⾸先,oracle中默认排序和对⽐列值时是区分⼤⼩写的，这点与mysql可不⼀样
⼤⼩写不敏感的查询
在数据库中可以通过语句:ALTER SESSION SET NLS_COMP = LINGUISTIC; 来使查询时,列的对⽐基于语义对⽐,会忽略⼤⼩写.但是,从执⾏计划中可以看到这样的信息"filter(NLSSORT(要查询的列,'nls_sort=''BINARY_CI''')=HEXTORAW(某数值)".这说明,更改了对⽐⽅式后，oracle会对这个列进⾏函数操作.意味着这个列上的索引会失效.当然解决⽅法也很直观,就对这个列建⽴函数索引即可:CREATE INDEX 索引名 ON T(NLSSORT(要索引的列名, 'NLS_SORT=BINARY_CI')); 索引建⽴后，再看执⾏计划，上述信息就变成
了"access(NLSSORT(要查询的列,'nls_sort=''BINARY_CI''')=HEXTORAW(某数值)".这点要注意哦，不然可是全表扫描，很可能会死⼈的....
我们在项⽬中当然不会向上⾯那样操作咯.我们是使⽤JDBC.这时可以这样操作: connection.createStatement().execute("alter session set nls_comp=LINGUISTIC"); 这时你需要能修改session的权限.在ibatis⾥,可以直接调⽤execute执⾏alter语句.使⽤spring的
JdbcTemplate时,除了直接执⾏这个语句外，还可以实现StatementCallback . 但是要注意，如果使⽤的是连接池,请注意使⽤完后修改回来，否则,这个session会被重复使⽤，后续的使⽤者将受此影响.
⽽在hibernate中，⼀般可以通过将查询条件全部转换为⼤写或者⼩写来变相的实现这⼀⽬的.
如果是HQL,使⽤oracle的LOWER()或UPPER()函数.例如: select ...... where LOWER(name) = key.toLowerCase();
如果是使⽤Criteria,则可以直接使⽤ignoreCase()或ilike()⽅法(注意，这个⽅法在like前⾯多了个i).例如:
Restrictions.eq('colname','value′).ignoreCase()
Restrictions.ilike('colname','value′)
Restrictions.like('colname','value′).ignoreCase().
这⾥要注意索引的问题了，有必要的话，需要建⽴函数索引.
⼤⼩写不敏感的排序
在数据库中可以通过语句:ALTER SESSION SET NLS_SORT = BINARY_CI;索引⽅⾯同上.
在项⽬中使⽤同上.
以上alter session的⽅式都可以改为alter system的⽅式对全部session⽣效.但是务必要注意性能问题.尤其是不敏感⼤⼩写后，oracle实际增加了函数操作,导致索引⽆效的问题。