查询正常作为条件报错的问题

今天在论坛上看到一个有趣的问题：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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-687253/