ITPUB论坛－专业的IT技术社区

标题: TO_NUMBER(SUBSTR(...)) 不能在where子句中使用吗？ [打印本页]

作者: cjf107 时间: 2006-12-7 18:00
标题: TO_NUMBER(SUBSTR(...)) 不能在where子句中使用吗？
linux as3.0  + oracle9.2.0.4

SQL> select count(*) from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions) ;

  COUNT(*)
----------
   920

SQL>  select count(*) from user_tab_partitions
  2  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL>
请问这是为什么？

作者: warehouse 时间: 2006-12-7 18:09
select count(*) from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) col from user_tab_partitions) t;加上列别名和子查询的表别名看看！

作者: dba_mx 时间: 2006-12-7 19:24
[php]
SQL> select to_number('a') from dual;
select to_number('a') from dual
               *
ERROR 位于第 1 行:
ORA-01722: 无效数字

SQL> select count(*) from (select to_number('a') from dual);

  COUNT(*)
----------
      1

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0    Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

...
[/php]

作者: dba_mx 时间: 2006-12-7 19:27
[php]
SQL> select * from  (select to_number('a') from dual);
select * from  (select to_number('a') from dual)
                              *
ERROR 位于第 1 行:
ORA-01722: 无效数字

可以看出：如果外层查询只想或许一个内层查询的记录数的话，那么内层的表达式是不被计算的，直接统计内层表的记录数；
反之，如果希望获取内容，则表达式必然要求值，此时才发现错误；

..
[/php]

Oracle的语义分析功能蛮强啊。。。。

作者: shunji 时间: 2006-12-7 19:32
同意楼上兄弟的看法

作者: yangtingkun 时间: 2006-12-7 19:59
很显然，第一个TO_NUMBER根本没有执行，你可以改成TO_DATE试试

作者: space6212 时间: 2006-12-8 08:53
以前一直没注意到这个细节

作者: cjf107 时间: 2006-12-8 10:30

最初由 yangtingkun 发布
[B]很显然，第一个TO_NUMBER根本没有执行，你可以改成TO_DATE试试 [/B]

SQL>select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions;
......
                              494
                              495
                              495
                              496
                              496
                              497
                              497
                              498
                              498
                              499
                              499
                              500
                              500

920 rows selected.

SQL>
就是执行了也没有问题。我保证分区名都是由'PART_'||一个数字
组成的，可为什么TO_NUMBER(SUBSTR(PARTITION_NAME,6))放在where子句中就报错呢？
SQL> select count(*) from user_tab_partitions
2 where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
*
ERROR at line 2:
ORA-01722: invalid number

SQL>

作者: cjf107 时间: 2006-12-8 10:33

最初由 dba_mx 发布
[B][php]
SQL> select * from  (select to_number('a') from dual);
select * from  (select to_number('a') from dual)
                              *
ERROR 位于第 1 行:
ORA-01722: 无效数字

可以看出：如果外层查询只想或许一个内层查询的记录数的话，那么内层的表达式是不被计算的，直接统计内层表的记录数；
反之，如果希望获取内容，则表达式必然要求值，此时才发现错误；

..
[/php]
Oracle的语义分析功能蛮强啊。。。。 [/B]

SQL> select count(*) from (select *  from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions)) ;

  COUNT(*)
----------
   920

SQL>

作者: yangtingkun 时间: 2006-12-8 11:03

最初由 cjf107 发布
[B]

SQL>select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions;
......
                              494
                              495
                              495
                              496
                              496
                              497
                              497
                              498
                              498
                              499
                              499
                              500
                              500

920 rows selected.

SQL>
就是执行了也没有问题。我保证分区名都是由'PART_'||一个数字
组成的，可为什么TO_NUMBER(SUBSTR(PARTITION_NAME,6))放在where子句中就报错呢？
SQL> select count(*) from user_tab_partitions
2 where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
*
ERROR at line 2:
ORA-01722: invalid number

SQL> [/B]

select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;

作者: cjf107 时间: 2006-12-8 14:35

最初由 yangtingkun 发布
[B]

select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182; [/B]

SQL> select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182; 2
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
*
ERROR at line 2:
ORA-01722: invalid number

SQL>

作者: freezr 时间: 2006-12-8 14:51
ORACLE在语义分析的时候，就报这个错误了，根本没有执行，所以就算TO_NUMBER(SUBSTR(PARTITION_NAME,6))都是数字，也没用。
我测试了普通的表，同样的语法就没有问题。

作者: yangtingkun 时间: 2006-12-8 14:58

最初由 cjf107 发布
[B]

SQL> select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182; 2
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
*
ERROR at line 2:
ORA-01722: invalid number

SQL> [/B]

数据库版本？

作者: cjf107 时间: 2006-12-8 15:17

最初由 yangtingkun 发布
[B]

数据库版本？ [/B]

linux as3.0 + oracle9.2.0.4

作者: yangtingkun 时间: 2006-12-8 15:24
看看这个吧：http://yangtingkun.itpub.net/post/468/237197

总不会三种办法对你的环境都无效吧？

作者: freezr 时间: 2006-12-8 15:32

最初由 yangtingkun 发布
[B]看看这个吧：http://yangtingkun.itpub.net/post/468/237197

总不会三种办法对你的环境都无效吧？ [/B]

看明白了。

作者: cjf107 时间: 2006-12-8 15:37
SQL> select /*+ NO_MERGE(USER_TAB_PARTITIONS) */ count(*) from user_tab_partitions
  2  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL> select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;  2
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL> SELECT count(*) FROM
  2 (
SELECT ROWNUM, TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME,6)) PART
FROM USER_TAB_PARTITIONS
)
WHERE PART <=182;
  3 4 5 6
  COUNT(*)
----------
   120

SQL>

第三种方法可以了，是因为没有将TO_NUMBER(SUBSTR())函数放在where子句，一旦放在where子句中就还是不行。

难道TO_NUMBER(SUBSTR())函数真的不能放在where子句中吗？

作者: cjf107 时间: 2006-12-8 15:39

最初由 yangtingkun 发布
[B]看看这个吧：http://yangtingkun.itpub.net/post/468/237197

总不会三种办法对你的环境都无效吧？ [/B]

这就是高手和低手的差别：
高手遇到或看到问题马上实践和总结，
低手遇到或看到问题马上就问。

所以我这样的永远是低手。我的blog有半年没上去了。呵呵。

作者: a212481 时间: 2006-12-8 15:42
以前一直没注意到这个细节

作者: cjf107 时间: 2006-12-8 15:42

最初由 cjf107 发布
[B]SQL> select /*+ NO_MERGE(USER_TAB_PARTITIONS) */ count(*) from user_tab_partitions
  2  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL> select /*+ no_push_pred(user_tab_partitions) */ count(*) from user_tab_partitions
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;  2
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL> SELECT count(*) FROM
  2 (
SELECT ROWNUM, TABLE_NAME, PARTITION_NAME, TO_NUMBER(SUBSTR(PARTITION_NAME,6)) PART
FROM USER_TAB_PARTITIONS
)
WHERE PART <=182;
  3 4 5 6
  COUNT(*)
----------
   120

SQL>

第三种方法可以了，是因为没有将TO_NUMBER(SUBSTR())函数放在where子句，一旦放在where子句中就还是不行。

难道TO_NUMBER(SUBSTR())函数真的不能放在where子句中吗？ [/B]

我说错了，应该说是/*+ no_push_pred(user_tab_partitions) */ 和/*+ NO_MERGE(USER_TAB_PARTITIONS) */ 还是不能避免去“不让Oracle将这个查询条件推入到视图中”。

作者: cjf107 时间: 2006-12-8 15:44
的确是视图的问题：

SQL> create table cjf_test as select table_name,partition_name  from user_tab_partitions;

Table created

SQL> select count(*) from cjf_test  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;

  COUNT(*)
----------
   129

SQL>

作者: rollingpig 时间: 2006-12-8 15:50
看看这个

[php]
CREATE TABLE user_tab_partitions_2 (partition_name VARCHAR2(100));

INSERT INTO  user_tab_partitions_2
SELECT 'asdfh'||ROWNUM
FROM dba_objects
WHERE ROWNUM <20;

INSERT INTO user_tab_partitions_2 VALUES('asdsdaasdas');

INSERT INTO  user_tab_partitions_2
SELECT 'asdgh'||ROWNUM
FROM dba_objects
WHERE ROWNUM <20;

select count(*) from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6))
  from user_tab_partitions_2) ;

====
39

select count(*) from user_tab_partitions_2
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
====
ORA-01722: invalid number

DECLARE
i NUMBER DEFAULT 0;
PARTITION_NAME VARCHAR2(100);
BEGIN
FOR a IN(SELECT PARTITION_NAME FROM user_tab_partitions_2 ) LOOP
PARTITION_NAME:=a.PARTITION_NAME;
if (TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182) THEN
i:=i+1;
END IF;
END LOOP;
Dbms_Output.put_line(i);
EXCEPTION WHEN OTHERS  then
Dbms_Output.put_line('Error in row '|| i||',****' || PARTITION_NAME||'***'||SUBSTR(PARTITION_NAME,6)||'****');
END;
/

=====
Error in row 19,****asdsdaasdas***aasdas****

...

...
[/php]

楼主运行那个procedure看看？

作者: yangtingkun 时间: 2006-12-8 15:58
看来是两个HINTS都没有生效
换个别名试试
比如
/*+ no_merge(a) */ count(*) from user_tab_partitions a

作者: cjf107 时间: 2006-12-8 16:06

最初由 yangtingkun 发布
[B]看来是两个HINTS都没有生效
换个别名试试
比如
/*+ no_merge(a) */ count(*) from user_tab_partitions a [/B]

还是不行：
SQL> SELECT /*+ NO_MERGE(a) */ TABLE_NAME, PARTITION_NAME
  2 FROM USER_TAB_PARTITIONS a
WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006;
  3 WHERE TO_NUMBER(SUBSTR(PARTITION_NAME, 2)) < 2006
               *
ERROR at line 3:
ORA-01722: invalid number

SQL> select /*+ NO_MERGE(a) */ count(*) from user_tab_partitions a
  2  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL>

作者: cjf107 时间: 2006-12-8 16:09

最初由 rollingpig 发布
[B]看看这个
...........
[php]
DECLARE
i NUMBER DEFAULT 0;
PARTITION_NAME VARCHAR2(100);
BEGIN
FOR a IN(SELECT PARTITION_NAME FROM user_tab_partitions_2 ) LOOP
PARTITION_NAME:=a.PARTITION_NAME;
if (TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182) THEN
i:=i+1;
END IF;
END LOOP;
Dbms_Output.put_line(i);
EXCEPTION WHEN OTHERS then
Dbms_Output.put_line('Error in row '|| i||',****' || PARTITION_NAME||'***'||SUBSTR(PARTITION_NAME,6)||'****');
END;
/

=====
Error in row 19,****asdsdaasdas***aasdas****

...

...
[/php]

楼主运行那个procedure看看？ [/B]

report@SHOWDEV.MEETEXPO>DECLARE
  2  i NUMBER DEFAULT 0;
  3 PARTITION_NAME VARCHAR2(100);
  4  BEGIN
  5  FOR a IN(SELECT PARTITION_NAME FROM user_tab_partitions ) LOOP
  6  PARTITION_NAME:=a.PARTITION_NAME;
  7  if (TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182) THEN
  8  i:=i+1;
  9  END IF;
10  END LOOP;
11  Dbms_Output.put_line(i);
12  EXCEPTION WHEN OTHERS  then
13  Dbms_Output.put_line('Error in row '|| i||',****' || PARTITION_NAME||'***'|
|SUBSTR(PARTITION_NAME,6)||'****');
14  END;
15  /
129

PL/SQL 过程已成功完成。

report@SHOWDEV.MEETEXPO>

没有问题，是ok的。

作者: zouzhiyou 时间: 2006-12-8 16:41
关注并。。。。学习。。。。。

作者: rollingpig 时间: 2006-12-8 17:20
select /*+ NO_MERGE(a) */ count(*) from
(select * from user_tab_partitions) a
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;

作者: yangtingkun 时间: 2006-12-8 19:04
最终结论：http://yangtingkun.itpub.net/post/468/237367

作者: cjf107 时间: 2006-12-11 17:00
不好意思，我周末没上来。现在才来。

作者: cjf107 时间: 2006-12-11 17:01

最初由 rollingpig 发布
[B]select /*+ NO_MERGE(a) */ count(*) from
(select * from user_tab_partitions) a
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182; [/B]

SQL> select /*+ NO_MERGE(a) */ count(*) from
  2  (select * from user_tab_partitions) a
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;

  3  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 3:
ORA-01722: invalid number

SQL> SQL>

作者: cjf107 时间: 2006-12-11 17:08

最初由 yangtingkun 发布
[B]最终结论：http://yangtingkun.itpub.net/post/468/237367 [/B]

SQL> SELECT count(*)
  2 FROM USER_TAB_PARTITIONS A, DUAL B
WHERE TO_NUMBER(SUBSTR(DUMMY || PARTITION_NAME, 7)) < 182;
  3
  COUNT(*)
----------
   118

SQL>

这样的确是可以了。
可是查看执行计划，访问的还是I_OBJ1 这个索引啊，这是为什么呢？

SQL>explain plan for SELECT count(*)
  2 FROM USER_TAB_PARTITIONS A, DUAL B
  3 WHERE TO_NUMBER(SUBSTR(DUMMY || PARTITION_NAME, 7)) < 182;

已解释。

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

.......
|* 10 |       TABLE ACCESS BY INDEX ROWID| OBJ$                |    |
  |    |
|* 11 |       INDEX UNIQUE SCAN       | I_OBJ1             |    |
  |    |
|  12 |       TABLE ACCESS CLUSTER       | SEG$                |    |
  |    |

作者: yangtingkun 时间: 2006-12-11 17:31

最初由 cjf107 发布
[B]

SQL> SELECT count(*)
  2 FROM USER_TAB_PARTITIONS A, DUAL B
WHERE TO_NUMBER(SUBSTR(DUMMY || PARTITION_NAME, 7)) < 182;
  3
  COUNT(*)
----------
   118

SQL>

这样的确是可以了。
可是查看执行计划，访问的还是I_OBJ1 这个索引啊，这是为什么呢？

SQL>explain plan for SELECT count(*)
  2 FROM USER_TAB_PARTITIONS A, DUAL B
  3 WHERE TO_NUMBER(SUBSTR(DUMMY || PARTITION_NAME, 7)) < 182;

已解释。

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

.......
|* 10 |       TABLE ACCESS BY INDEX ROWID| OBJ$                |    |
  |    |
|* 11 |       INDEX UNIQUE SCAN       | I_OBJ1             |    |
  |    |
|  12 |       TABLE ACCESS CLUSTER       | SEG$                |    |
  |    | [/B]

看来我的表述有问题，自己觉得说清楚了，别人还是看不懂

你看看执行计划中，在那一步进行的过滤操作：TO_NUMBER(SUBSTR(DUMMY || PARTITION_NAME, 7)) < 182

在最后这个方法中，利用将PARTITION_NAME和DUMMY列进行了结合操作，从而使得整个USER_TAB_PARTITIONS视图称为一个整体，也就是无法MERGE，由于包含了对DUMMY列的访问，使得查询条件无法推入到视图里面，只能在视图结果获得后进行过滤。

所以，这个时候和视图里面的执行计划已经没有任何的关系了

作者: cjf107 时间: 2006-12-12 10:31
谢谢。有点理解了。

不过不要用“衰”这个表情啊，要不我该难为情了。

欢迎光临 ITPUB论坛－专业的IT技术社区 (http://www.itpub.net/)