ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 大小写敏感问题

oracle 大小写敏感问题

原创 Linux操作系统 作者:pennycheung 时间:2009-05-22 13:57:20 0 删除 编辑

最近在应用上出现了一个情况。就是大小写问题,客户那边想在查询的时候,无论是大写的A还是小写的a,一律相同,即A=a,在网上找了些资料,大致上就是修改NLS_SORT和NLS_COMP这两个参数,但相对来说会对性能有一定的影响,因为在修改了这两个参数以后,Oracle将忽略索引,执行Full Table Scan,除非创建Linguistic  index,有一点不明白,所谓创建Linguistic index是把库中的所有索引的修改为Linguistic index,还是有针对性的把所需要Case-Insensitive 的索引改为Linguistic index。

  另外,Oracle 10g 提供一个新的特性,提供了一些正则表达式。

REGEXP_LIKE(匹配)
REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)

Anchoring Characters
^ Anchoring Characters
$ Anchor the expression to the end of a line

Equivalence Classes
= =
Oracle supports the equivalence classes through the POSIX '[==]' syntax. A base letter and all of its accented versions constitute an equivalence class. For example, the equivalence class '[=a=]' matches ?and ? The equivalence classes are valid only inside the bracketed expression
Match Options
c Case sensitive matching
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character
Posix Characters

[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form. feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
Quantifier Characters

* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters

The Globalization of Language in Oracle - And Case-Insensitivity http://www.dbasupport.com/oracle/ora10g/globalization0501.shtml

究竟使用哪个方式最好呢?对数据库的性能、稳定性影响最小,同时又不需要对代码进行太多的修改?

继续研究中。。。。。

1、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。

为了不影响sort的性能,而牺牲insert与update的性能?

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34504/viewspace-600836/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-22

  • 博文量
    9
  • 访问量
    18675