ORACLE 11g默认情况下不区分大小写 [英] ORACLE 11g case insensitive by default
问题描述
我在这篇文章中发现因为ORACLE 10g,有一种方法使特定的连接会话比较字符串不区分大小写,不需要任何疯狂的SQL函数,使用 ALTER SESSION
。
I found in this article, that since ORACLE 10g, there is a way to make a particular connection-session compare strings case-insensitive, without needing any crazy SQL functions, using an ALTER SESSION
.
有没有人知道,如果在11g中,可能有一种方法使数据库始终在所有新连接会话中以此模式运行,从而消除每次连接时需要运行 ALTER SESSION
?
Does anyone know if, in 11g, there might be a way to make the database to always operate in this mode by default for all new connection-sessions, thereby eliminating the need for running ALTER SESSION
s every time you connect?
或者,你可以在你的连接字符串上指定一个额外的参数,这个参数将会改变?
Or perhaps, an additional parameter you could specify on your connection string that would turn the same on?
推荐答案
您可以设置文章中提到的 NLS_SORT
, NLS_COMP
参数Oracle init文件中的值使用 alter system set< parameter> =< value> ;;
子句。
You could just set the NLS_SORT
, NLS_COMP
parameters mentioned in the article as the values in the the Oracle init file using the alter system set <parameter> = <value>;
clause.
使用alter system命令的信息可以在此处。
Info on using the alter system commands can be found here.
这是一个很好的链接正确使用 NLS _ *
参数。请注意,NLS_SORT参数的某些设置可能/可能会导致性能问题,即当它未设置为BINARY时。 Oracle文档状态:
Here is a good link on the correct usage of the NLS_*
parameters. Note that some settings of of the NLS_SORT parameter can/could cause performance issues, namely when it is not set to BINARY. The Oracle docs state:
将NLS_SORT设置为除BINARY之外的任何其他
会导致排序使用
全表扫描,而不考虑优化程序选择的
路径。 BINARY
是异常,因为索引是根据
键的二进制顺序构建的
。因此,当NLS_SORT设置为BINARY时,优化器可以使用
索引来满足ORDER BY子句
。如果
NLS_SORT设置为任何语言
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.
这篇关于ORACLE 11g默认情况下不区分大小写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!