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.

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值