oracle语义模式

在进行数据库查询的时候，通过打开语义模式可以在查询的时候忽略大小写。

写道

SQL> select * from ajlx where id = 1;

ID MC
---------- ----------------------------------------
1 aaBB

 通常使用like 'AABB' 时，会查不到任何数据。然后可以通过更改会话：

写道

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

会话已更改。

 这时执行like查询语句时，系统就会自动忽略掉大小写关系，将以上语句查询出来。

写道

SQL> select * from ajlx where id = 1 and mc like 'AABB';

ID MC
---------- ----------------------------------------
1 aaBB

 以上是在SQLPlus中的操作步骤，在一个会话中修改nls_comp和nls_sort来更改数据库查询和排序的方式。

如果是在JDBC中可以直接使用发送SQL语句来进行语义环境设置。

写道

session.createSQLQuery("ALTER SESSION SET NLS_COMP=LINGUISTIC")
.executeUpdate();
session.createSQLQuery("ALTER SESSION SET NLS_SORT=BINARY_CI")
.executeUpdate();

 但是设置 NLS_COMP=LINGUISTIC有着明显的性能问题，系统总是会进行全表扫描。

写道

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.

 在oracle官方文档中说，当NLS_COMP被设置为LINGUISTIC时，会引起很严重的性能消耗，建议在该列上建立语义索引。

建立linguistic index：

Using Linguistic Indexes

Linguistic sorting is language-specific and requires more data processing than binary sorting. Using a binary sort for ASCII is accurate and fast because the binary codes for ASCII characters reflect their linguistic order. When data in multiple languages is stored in the database, you may want applications to sort the data returned from a SELECT...ORDER BY statement according to different sort sequences depending on the language. You can accomplish this without sacrificing performance by using linguistic indexes. Although a linguistic index for a column slows down inserts and updates, it greatly improves the performance of linguistic sorting with the ORDER BY clause.

You can create a function-based index that uses languages other than English. The index does not change the linguistic sort order determined by NLS_SORT . The index simply improves the performance. The following statement creates an index based on a German sort:

CREATE TABLE my_table(name VARCHAR(20) NOT NULL);
CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT = German'));

/*The NOT NULL in the CREATE TABLE statement ensures that the index is used*/

After the index has been created, enter a SELECT statement similar to the following:

SELECT * FROM my_table ORDER BY name;

It returns the result much faster than the same SELECT statement without an index.