FIRST_ROWS優化模式語言排序模糊匹配問題

FIRST_ROWS優化模式語言排序模糊匹配問題  軟件測試工程師

標題比較長，不過只有這樣才能把問題描述清楚。

問題詳細描述為，在FIRST_ROWS優化模式下，將會話排序和比較方式設置為語義模式，即忽略大小寫模式，對字段進行LIKE模糊查詢，可能導致錯誤的結果。

下面直接看問題的現象：

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已創建。

SQL> CREATE INDEX IND_T1_NAME ON T1(NAME);

索引已創建。

SQL> INSERT INTO T1 SELECT ROWNUM, CHR(64 + ROWNUM) 
2 FROM ALL_OBJECTS WHERE ROWNUM <= 26;

已創建26行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

會話已更改。

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

會話已更改。

SQL> SELECT * FROM T1 WHERE NAME LIKE ’a%’;

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE ’a%’;

未選定行

只要修改上面提到的關鍵點中的任意一個，就不會產生這個錯誤的現象：

SQL> SELECT /*+ ALL_ROWS */ * FROM T1 WHERE NAME LIKE ’a%’;

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME = ’a’;

ID NAME
---------- ------------------------------
1 A

SQL> ALTER SESSION SET NLS_SORT = BINARY;

會話已更改。

SQL> ALTER SESSION SET NLS_COMP = BINARY;

會話已更改。

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE ’A%’;

ID NAME
---------- ------------------------------
1 A

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

會話已更改。

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

會話已更改。

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE ’A%’;

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE ’a’;

未選定行

通過上面的幾個查詢可以看到，問題和FIRST_ROWS，LIKE操作以及基于語義的排序直接相關，下面看看Oracle在異常情況下采用了何種執行計劃：

SQL> SET AUTOT ON EXP
SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE ’a’;

未選定行

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

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 | [Page]
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - filter(\"NAME\" LIKE ’a’)
2 - aclearcase/" target="_blank" >ccess(ROWID=ROWID)
3 - access(\"NAME\" LIKE ’a’)

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

由于索引中并不包含語義查詢的結果，因此Oracle這里必須訪問表才能得到最終的結果，因此這個執行計劃是錯誤的：

SQL> SELECT * FROM T1 WHERE NAME = ’a’;

ID NAME
---------- ------------------------------
1 A

執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 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> SELECT /*+ INDEX(T1) */ * FROM T1 WHERE NAME = ’a’;

ID NAME
---------- ------------------------------
1 A

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

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 827 (1)| 00:00:10 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 827 (1)| 00:00:10 |
| 2 | INDEX FULL SCAN | SYS_C006622 | 26 | | 26 (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對于語義的排序無法通過索引獲取，必須要訪問表或者相應的函數索引，詳細描述可以參考文章開頭部分給出的鏈接。

而采用了FIRST_ROWS優化模式后，當操作為LIKE時，Oracle優化器選擇了錯誤的執行計劃進行了優化，采用索引的范圍掃描代替了表，從而引發了錯誤： [Page]

SQL> SELECT /*+ INDEX_JOIN(T1 IND_T1_NAME SYS_C006622) */ *
2 FROM T1
3 WHERE NAME LIKE ’a’;

未選定行

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

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - filter(\"NAME\" LIKE ’a’)
2 - access(ROWID=ROWID)
3 - access(\"NAME\" LIKE ’a’)

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

現在沒有使用FIRST_ROWS，而采用HINT也達到了相同的效果。而解決這個問題的方法就是通過HINT來避免索引范圍掃描的發生。

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

會話已更改。

SQL> SELECT * FROM T1 WHERE NAME LIKE ’a’;

未選定行

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

-------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - filter(\"NAME\" LIKE ’a’)
2 - access(ROWID=ROWID)
3 - access(\"NAME\" LIKE ’a’)

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

SQL> SELECT /*+ FULL(T1) */ * FROM T1 WHERE NAME LIKE ’a’; [Page]

ID NAME
---------- ------------------------------
1 A

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

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

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

1 - filter(\"NAME\" LIKE ’a’)

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

SQL> SELECT /*+ NO_INDEX(T1) */ * FROM T1 WHERE NAME LIKE ’a’;

ID NAME
---------- ------------------------------
1 A

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

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

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

1 - filter(\"NAME\" LIKE ’a’)

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

查詢metalink，Oracle在Doc ID: Note:5252496.8明確說明了這個bug，這個bug會在Oracle10.2.0.4和11.1.0.6中被Fixed。

原文轉自：http://www.anti-gravitydesign.com