在Oracle中進行大小寫不敏感的查詢[zt]

xsb發表於2008-04-28
ALTER SESSION SET NLS_SORT = BINARY_CI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
[@more@]

Ref: http://yangtingkun.itpub.net/post/468/460324

Oracle中,命令和物件名稱都是大小寫不敏感的,因為Oracle在處理語句時,將所有的名稱和命令全部轉化為大寫。

但是對於字串中的字元,無論是比較還是排序,都是大小寫敏感的。這在Oracle是預設方式,但不是唯一的方式。

下面看一個簡單的例子:

SQL> CREATE TABLE T (NAME VARCHAR2(30));

表已建立。

SQL> INSERT INTO T VALUES ('A');

已建立 1 行。

SQL> INSERT INTO T VALUES ('a');

已建立 1 行。

SQL> INSERT INTO T VALUES ('B');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已建立。

看一下預設情況下的排序和查詢結果:

SQL> SELECT * FROM T ORDER BY NAME;

NAME
------------------------------
A
B
a

SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
------------------------------
A

這是最正常不過的結果了,下面修改會話預設的排序方式:

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

會話已更改。

SQL> SELECT * FROM T ORDER BY NAME;

NAME
------------------------------
A
a
B

SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
------------------------------
A

可以看到,透過設定排序方法為BINARY_CI,已經實現了對排序的大小寫不敏感,但是查詢語句中仍然是大小寫敏感的,下面進一步修改比較方式:

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

會話已更改。

SQL> SELECT * FROM T ORDER BY NAME;

NAME
------------------------------
A
a
B

SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
------------------------------
A
a

現在已經達到了大小寫不敏感查詢的目的了,這是由於設定比較方式是基於語義的,而不是基於二進位制的,而語言方式下Aa是沒有區別的。

雖然目的達到了,但是還是要說明一下,這裡雖然實現了對大小寫不敏感的查詢,但是這個結果的實現與表面看到的現象並不完全相同。

從查詢語句上看,似乎只是對NAME進行一下判斷就可以了,並未對列進行任何的操作,而實際上並非如此,下面看看這種情況下的執行計劃:

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
------------------------------
A
a

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')
)

Note
-----
- dynamic sampling used for this statement

Oracle居然對列進行了操作,將NAME進行了NLSSORT操作,然後判斷是否與目標值進行判斷。不過Oracle也沒有其他的好方法進行處理,對等號右邊的常量進行轉換固然代價較低,但是SQL的判斷條件就由等於變成了IN,這種轉換恐怕變化更大。而且還要找到所有其他所有可能轉換為目標值的常量,這個操作要比對列進行轉換複雜得多。

不過這種方法就存在一個問題,就是Oracle無法使用索引了,一方面是由於對列進行了操作,另一方面是由於Oracle的索引是按照BINARY方式編碼儲存的。因此這種查詢會採用全表掃描的方式。

SQL> SELECT /*+ INDEX(T IND_T_NAME) */ * FROM T WHERE NAME = 'A';

NAME
------------------------------
A
a

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')
)

Note
-----
- dynamic sampling used for this statement

這個情況,可以考慮建立一個函式索引來解決問題:

SQL> CREATE INDEX IND_T_L_NAME ON T(NLSSORT(NAME, 'NLS_SORT=BINARY_CI'));

索引已建立。

SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
------------------------------
A
a

執行計劃
----------------------------------------------------------
Plan hash value: 242883967

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_L_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )

Note
-----
- dynamic sampling used for this statement

當然使用一些非大眾化的功能就容易碰到bug,比如下面的例子:http://yangtingkun.itpub.net/post/468/460325

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12402/viewspace-1003086/,如需轉載,請註明出處,否則將追究法律責任。

相關文章