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

在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

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

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

從查詢語句上看，似乎只是對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