oracle 大小写敏感问题

最近在应用上出现了一个情况。就是大小写问题，客户那边想在查询的时候，无论是大写的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/34504/viewspace-600836/