ORACLE 11g 默认不区分大小写 [英] ORACLE 11g case insensitive by default

问题描述

我在这篇文章中发现，自从在 ORACLE 10g 中，有一种方法可以使用 ALTER SESSION 使特定的连接会话比较字符串不区分大小写，而无需任何疯狂的 SQL 函数.

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 SESSIONs 每次连接?

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?

或者，您可以在连接字符串上指定一个额外的参数来打开相同的功能?

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= ; 子句.

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 导致排序使用全表扫描，不管优化器选择的路径.二进制是例外，因为索引是根据二进制顺序构建键.因此优化器可以使用满足 ORDER BY 子句的索引当 NLS_SORT 设置为 BINARY 时.如果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.

这篇关于ORACLE 11g 默认不区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！