查詢正常作為條件報錯的問題

今天在論壇上看到一個有趣的問題：http://www.itpub.net/683461.html

將函式呼叫放到SELECT列表中可以執行，如果放到WHERE條件中就會報錯。

查詢正常作為條件報錯的問題（續）：http://yangtingkun.itpub.net/post/468/237367

簡單起見，構造一個測試的小例子：

SQL> CREATE TABLE T_PART (ID NUMBER, CREATE_DATE DATE)
2 PARTITION BY RANGE (CREATE_DATE)
3 (
4 PARTITION P2004 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
5 PARTITION P2005 VALUES LESS THAN (TO_DATE('2006-1-1', 'YYYY-MM-DD')),
6 PARTITION P2006 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')),
7 PARTITION P2007 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD'))
8 );

表已建立。

SQL> SELECT TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) FROM USER_TAB_PARTITIONS;

TO_NUMBER(SUBSTR(PARTITION_NAME,2))
-----------------------------------
2004
2005
2006
2007

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS
2 WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;
WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006
*第 2 行出現錯誤:
ORA-01722: invalid number

上面通過一個小例子再現了這個問題。引申一步，即使是巢狀一層，Oracle仍然會報同樣的錯誤：

SQL> SELECT * FROM
2 (
3 SELECT TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) PART
4 FROM USER_TAB_PARTITIONS
5 )
6 WHERE PART < 2006;
SELECT TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) PART
*第 3 行出現錯誤:
ORA-01722: invalid number

其實造成錯誤的原因很好確定，看一下執行計劃就明白了：

SQL> EXPLAIN PLAN FOR
2 SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS
3 WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | USER_TAB_PARTITIONS | | | |
| 2 | UNION-ALL | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | TABLE ACCESS FULL | TABPART$ | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| OBJ$ | | | |
|* 8 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 9 | TABLE ACCESS CLUSTER | SEG$ | | | |
|* 10 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | | | |
| 11 | TABLE ACCESS CLUSTER | TS$ | | | |
|* 12 | INDEX UNIQUE SCAN | I_TS# | | | |
| 13 | NESTED LOOPS | | | | |
|* 14 | TABLE ACCESS FULL | TABPART$ | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 16 | INDEX UNIQUE SCAN | I_OBJ1 | | | |
| 17 | NESTED LOOPS | | | | |
| 18 | NESTED LOOPS | | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 20 | INDEX RANGE SCAN | I_OBJ2 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | TABCOMPART$ | | | |
|* 22 | INDEX UNIQUE SCAN | I_TABCOMPART$ | | | |
| 23 | TABLE ACCESS CLUSTER | TS$ | | | |
|* 24 | INDEX UNIQUE SCAN | I_TS# | | | |
-----------------------------------------------------------------------------------------

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

7 - filter(TO_NUMBER(SUBSTR("O"."SUBNAME",2))<2006 AND "O"."OWNER#"=:B1)
8 - access("O"."OBJ#"="TP"."OBJ#")
10 - access("TP"."TS#"="S"."TS#" AND "TP"."FILE#"="S"."FILE#" AND
"TP"."BLOCK#"="S"."BLOCK#")
12 - access("TS"."TS#"="TP"."TS#")
14 - filter("TP"."BLOCK#"=0 AND "TP"."FILE#"=0)
15 - filter(TO_NUMBER(SUBSTR("O"."SUBNAME",2))<2006 AND "O"."OWNER#"=:B1)
16 - access("O"."OBJ#"="TP"."OBJ#")
20 - access("O"."OWNER#"=:B1)
filter(TO_NUMBER(SUBSTR("O"."SUBNAME",2))<2006)
22 - access("O"."OBJ#"="TCP"."OBJ#")
24 - access("TCP"."DEFTS#"="TS"."TS#")

Note: rule based optimization

已選擇48行。

問題的根源在於USER_TAB_PARTITIONS不是一張真實的表，而是系統的檢視。從執行計劃上可以看到，在第7步和第20步，Oracle進行了函式呼叫的過濾，這個時候，Oracle訪問的還是系統表，裡面包含所有的分割槽資訊。對於USER_TAB_PARTITIONS來說，所有的分割槽都滿足TO_NUMBER的情況，但是這種情況並不滿足系統中所有的分割槽。因此，查詢報錯。

簡單的說，是Oracle應用了將約束條件推入到檢視中的方法，這種方法有利於將第一步的結果集限制到最小，是CBO優化技術之一。

在大多數的情況下，這種情況是正確的，但是對於這個例子，這種方法就會導致錯誤的產生。

其實，解決這個錯誤也是很容易的。

只有不讓Oracle將這個查詢條件推入到檢視中就可以了，可以參考的方法包括：

SQL> SELECT /*+ NO_PUSH_PRED(USER_TAB_PARTITIONS) */ TABLE_NAME, PARTITION_NAME
2 FROM USER_TAB_PARTITIONS
3 WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_PART P2004
T_PART P2005

SQL> SELECT /*+ NO_MERGE(USER_TAB_PARTITIONS) */ TABLE_NAME, PARTITION_NAME
2 FROM USER_TAB_PARTITIONS
3 WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_PART P2004
T_PART P2005

SQL> SELECT * FROM
2 (
3 SELECT ROWNUM, TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) PART
4 FROM USER_TAB_PARTITIONS
5 )
6 WHERE PART < 2006;

ROWNUM TABLE_NAME PARTITION_NAME PART
---------- ------------------------------ --------------- ----------
1 T_PART P2004 2004
2 T_PART P2005 2005

上面三種方法其實大同小異，都是利用Oracle的hint或rownum的特性，導致Oracle先將檢視的結果計算處理，然後在進行條件查詢。

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