Oracle教程

Oracle 参数 NLS_COMP 官方解释，作用，如何配置最优化建议

本站中文解释

=

NLS_COMP参数是Oracle数据库用来控制隐式类型转换的参数，它的可设置的参数值只有两个LINGUISTIC和BINARY。

LINGUISTIC:
既是Oracle默认值，它以一种比较自然的方式进行比较，比如按字符串中字符的排序顺序，而不仅仅是按字符串元素的ascii码。比如：’C’和”c”相等，在这种情况下，Oracle会进行自动大小写转换，同时可以支持全文检索等功能。

BINARY:
该参数表示Oracle的类型转换操作会使用字节数组，只比较字符串中每个元素的ascii码，也就是说，在该模式下，’C’和”c”是不相等的，两者只有ascii码的不同。

正确设置:
对于NLS_COMP参数，具体要根据应用程序对字符串的比较结果进行定制，而不能一波固定设定，通常情况下，可以根据业务场景，灵活进行设置，通常我们默认设置为LINGUISTIC.

官方英文解释

NLS_COMP specifies the collation behavior of the database session.

Property Description

Parameter type

String

Syntax

NLS_COMP = { BINARY | LINGUISTIC | ANSI }

Default value

BINARY

Modifiable

ALTER SESSION

Modifiable in a PDB

Yes

Basic

No

Values

  • BINARY

    Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

  • LINGUISTIC

    Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

  • ANSI

    A setting of ANSI is for backward compatibility; in general, you should set NLS_COMP to LINGUISTIC

Note:

Unless you explicitly set the value for NLS_COMP in your initialization parameter file, a default value of NULL is shown in the following views: V$PARAMETER, V$SYSTEM_PARAMETER, V$PARAMETER2, V$SYSTEM_PARAMETER2, and NLS_INSTANCE_PARAMETERS. However, the actual default value, and behavior, is BINARY. Note that you cannot change the default to NULL, because NULL is not among the valid values.

Examples

See Oracle Database
Globalization Support Guide for examples of using this parameter.

Note:

The value of this initialization parameter NLS_COMP is used to initialize the session value of this parameter, which is the actual value referenced by the SQL query processing. This initial value is overridden by a client-side value if the client uses the Oracle JDBC driver or if the client is OCI-based and the NLS_LANG client setting (environment variable) is defined. The initialization parameter value is, therefore, usually ignored.


数据运维技术 » Oracle 参数 NLS_COMP 官方解释，作用，如何配置最优化建议
分享到：

相关推荐