ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查询正常作为条件报错的问题

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

原创 Linux操作系统 作者:wuxidba 时间:2011-02-15 13:00:45 0 删除 编辑

今天在论坛上看到一个有趣的问题: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

上面三种方法其实大同小异,都是利用Oraclehintrownum的特性,导致Oracle先将视图的结果计算处理,然后在进行条件查询。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23895263/viewspace-687253/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库建表原则
请登录后发表评论 登录
全部评论

注册时间:2010-05-12

  • 博文量
    64
  • 访问量
    64710