ORACLE 11g 默认不区分大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2001165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE 11g case insensitive by default
提问by eidylon
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.
我在这篇文章中发现，从 ORACLE 10g 开始，有一种方法可以使特定的连接会话比较字符串不区分大小写，而无需任何疯狂的 SQL 函数，使用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 SESSIONs every time you connect?
有谁知道，在 11g 中，是否有一种方法可以让数据库在所有新连接会话的默认情况下始终以这种模式运行，从而消除ALTER SESSION每次连接时都运行s的需要？
Or perhaps, an additional parameter you could specify on your connection string that would turn the same on?
或者，您可以在连接字符串上指定一个额外的参数来打开它？
回答by RC.
You could just set the NLS_SORT, NLS_COMPparameters mentioned in the article as the values in the the Oracle init file using the alter system set <parameter> = <value>;clause.
您可以使用子句将文章中提到的NLS_SORT,NLS_COMP参数设置为 Oracle init 文件中的值alter system set <parameter> = <value>;。
Info on using the alter system commands can be found here.
可以在此处找到有关使用 alter system 命令的信息。
Here is a good linkon 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_*。请注意，NLS_SORT 参数的某些设置可能/可能会导致性能问题，即未设置为 BINARY 时。Oracle 文档指出：
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 设置为除 BINARY 以外的任何值会导致排序使用全表扫描，而不管优化器选择的路径如何。BINARY 是个例外，因为索引是根据键的二进制顺序构建的。因此，当 NLS_SORT 设置为 BINARY 时，优化器可以使用索引来满足 ORDER BY 子句。如果 NLS_SORT 设置为任何语言排序，则优化器必须在执行计划中包括全表扫描和全排序。
回答by geekzspot
Sure you can!
你当然可以！
Get your friendly DBA to set these parameters:
让您友好的 DBA 设置这些参数：
ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE=SPFILE;
ALTER SYSTEM SET NLS_SORT=BINARY_AI SCOPE=SPFILE;
This is taken from my short article on How to make Oracle Case Insensitive
这摘自我关于如何使 Oracle 不区分大小写的短文
回答by Joey Gibson
I tried using a logon triggerto issue these commands to get case-insensitive queries:
我尝试使用 alogon trigger发出这些命令来获取不区分大小写的查询：
execute immediate 'alter session set NLS_SORT=BINARY_CI';
execute immediate 'alter session set NLS_COMP=LINGUISTIC';
And while that did give me CI, it also gave me unbelievably bad performance issues. We have one table in particular that, without those settings, inserts take 2 milliseconds. With those settings in place, inserts took 3 seconds. I have confirmed this by creating and dropping the trigger multiple times.
虽然这确实给了我 CI，但它也给了我令人难以置信的糟糕性能问题。我们特别有一张表，如果没有这些设置，插入需要 2 毫秒。有了这些设置，插入需要 3秒。我已经通过多次创建和删除触发器来确认这一点。
I don't know if doing it at the system level, as opposed to the session level with a trigger, makes a difference or not.
我不知道在系统级别执行此操作是否与使用触发器的会话级别相反，是否有所作为。
回答by ojock
I found the same performance issue with inserts and nls in 11g r2! Luckily for me the performance hit was not significant enough requiring an app change.
我在 11g r2 中发现了与插入和 nls 相同的性能问题！对我来说幸运的是，性能影响不够大，需要更改应用程序。
If you can do without binary_ci for the INSERT, then I would do an alter session just before the insert and afterwards, so you don't have to drop the trigger
如果你可以不用 binary_ci 来插入，那么我会在插入之前和之后做一个改变会话，所以你不必删除触发器
