从TO_NUMBER报ORA-01722 invalid number展开了去...

问题

前不久一位开发同事突然问我：他写的一个简单的sql语句中的TO_NUMBER报ORA-01722 invalid number错误，要to_number的字段是varchar类型，其中只有一条数据的这个字段值不能转换为数字；他将这个值排除掉，然后转换为数字进行条件查询，但是一直报题目中的错误，即无效数字。sql如下：

1
2
3
4
5
6
7
SELECT COUNT(1)
  FROM (SELECT USER_ID
          FROM T_USER_INFO UI
         WHERE UI.USER_ID <> 'SYS_000')
 WHERE USER_ID > 21361059;
ERROR:
ORA-01722: invalid number

该sql统计排除用户“SYS_000”后用户ID大于“21361059”的用户数量（用户ID自增长，只是个别用户可能来自于特殊生成方式）。sql很简单，简单从表面逻辑上理解，在排除特殊的用户后，语句不应该再报 ORA-01722 invalid number 错误。接下来我们看看这到底是怎么回事。

环境

项目 说明
操作系统 CentOS 6.5 64位
数据库版本 11.2.0.1、11.2.0.4
RAC 否

模拟环境

1
2
3
4
5
6
7
8
9
10
SQL> create table test(id int,user_id varchar2(30));
SQL> insert into test values(1,'111');
SQL> insert into test values(2,'222');
SQL> insert into test values(3,'333');
SQL> insert into test values(4,'444');
SQL> insert into test values(5,'sys01');
SQL> insert into test values(6,'666');
SQL> insert into test values(7,'777');
SQL> commit;
Commit complete.

开始测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select * 
       from (select * 
               from test t 
              where t.user_id<>'sys01') 
      where to_number(user_id)>1;

        ID USER_ID
---------- ------------------------------
         1 111
         2 222
         3 333
         4 444
         6 666
         7 777
6 rows selected.

SQL> exec dbms_stats.gather_table_stats('XWY','TEST');
PL/SQL procedure successfully completed.
SQL> select * 
       from (select * 
               from test t 
              where t.user_id<>'sys01') 
      where to_number(user_id)>1;
ERROR:
ORA-01722: invalid number

可以看出，在表未做统计分析情况下，可以查出数据，没有报ORA-01722 invalid number错误，但是有统计情况下确报错。这是为什么呢，其实看看他们的执行计划就明白了：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--没有统计表情况下的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST  |     1 |    30 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."USER_ID"<>'sys01' AND TO_NUMBER("T"."USER_ID")>1)

--有统计表情况下的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("T"."USER_ID")>1 AND "T"."USER_ID"<>'sys01')

可以看到唯一的区别就是在filter的时候顺序，没有统计表情况下，”先执行T”.”USER_ID”<>’sys01’ 然后TO_NUMBER(“T”.”USER_ID”)>1，这种情况无效数字已经排除，当然不会报错；有统计表情况下，先进行全表数据扫描，看是否满足过滤条件：TO_NUMBER(“T”.”USER_ID”)>1，因为存在无法转换为数字的字符，当然报错。

如何解决

很明显同事只是简单的从sql的写法上去表的了业务逻辑，他想让sql按照所写逻辑伪代码去执行。从sql上来说我们先放下去考虑这个语句写法是否合理，从上面的测试当中可以看出，我们只要能让sql的执行计划执行的时候能先排除无法转为数字的记录在执行后面的条件就可以了。

方法一

其实就是还原测试当中，我们在没有表统计情况下的能正常执行的情况。但是Oracle会自动执行表的统计分析（可以让数据库根据统计信息指定合理执行计划）。但是我们总不能因为这个sql而改变整个系统吧。所以此时我们可以暂时使用Hint提示Oracle按照基于Rule生成计划执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> SELECT /*+ rule*/ *
       FROM (SELECT A.* FROM TEST A WHERE A.USER_ID <> 'sys01')
      WHERE USER_ID > 1;

        ID USER_ID
---------- ------------------------------
         1 111
         2 222
         3 333
         4 444
         6 666
         7 777
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."USER_ID"<>'sys01' AND TO_NUMBER("A"."USER_ID")>1)

方法二

我们可以利用 with as 结合 materialize hint,这样在执行的时候能先使子查询的结果生成一个临时表固化下来，然后在这个结果集上面执行转换函数，当然就不会报错啦

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH T AS
     (SELECT /*+ materialize */ A.*
        FROM TEST A
       WHERE A.USER_ID <> 'sys01')
SELECT * FROM T WHERE TO_NUMBER(T.USER_ID) > 1;

        ID USER_ID
---------- ------------------------------
         1 111
         2 222
         3 333
         4 444
         6 666
         7 777
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2049816044
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     6 |   180 |     5   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660E_101A7F |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | TEST                      |     6 |    42 |     3   (0)| 00:00:01 |
|*  4 |   VIEW                     |                           |     6 |   180 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660E_101A7F |     6 |    42 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."USER_ID"<>'sys01')
   4 - filter(TO_NUMBER("T"."USER_ID")>1)

方法三

还可以利用子查询不展开 NO_UNNEST hint特性。这样子查询只能走filter进行过滤。这样做的唯一缺点就是，本来是一个表的很简单的查询，但是却扫描了两次。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT /*+ NO_UNNEST(@AA)*/ *
  FROM TEST A
 WHERE A.USER_ID <> 'sys01'
   AND EXISTS (SELECT /*+ QB_NAME(AA)*/ *
                 FROM TEST T
                 WHERE A.USER_ID = T.USER_ID
                   AND T.USER_ID > 1);

        ID USER_ID
---------- ------------------------------
         1 111
         2 222
         3 333
         4 444
         6 666
         7 777
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1194824156
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |    12   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     6 |    42 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST |     1 |     5 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("AA") */ 0 FROM
              "TEST" "T" WHERE "T"."USER_ID"=:B1 AND TO_NUMBER("T"."USER_ID")>1))
   2 - filter("A"."USER_ID"<>'sys01')
   3 - filter("T"."USER_ID"=:B1 AND TO_NUMBER("T"."USER_ID")>1)

总结

  • 数据库选择实际的执行计划是一个复杂的过程，不是简单的sql逻辑，所以我们写sql的时候千万不能想当然。
  • 当sql执行结果与我们预想的不一致（发生错误、效率不高等等）我们可以从其实际的执行计划着手，找到真正原因。
  • 就像Oracle对同一个sql可以生成不同的解决方案(执行计划)，我们解决问题的时候也要尽可能找到多种解决方案，这样才能比较，才能提高。