ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > ORA-01722 排错一例

ORA-01722 排错一例

原创 Linux操作系统 作者：尛样儿 时间：2010-12-01 17:13:00 0 删除 编辑

SQL> Select Count(1) as ct
  2    From (select t00.*
  3            from (select rrr.*
  4                    from (select
  5                           rr.*
  6                            from (select *
  7                                    from beproj.Be_t_Busi_Result
  8                                   where model_id = 313201011230003531) rr
  9                            left outer join (select 'TOTALSCORE' rule_id1
 10                                              from dual) t
 11                              on rr.rule_id = t.rule_id1
 12                           where t.rule_id1 is null
 13                             and rr.busi_score is not null) rrr
 14                   inner join beproj.Be_t_Rule t0
 15                      on rrr.rule_id = t0.rule_id) t00
 16            Left Join beproj.Be_t_Rule t1
 17              on t00.rule_id = t1.parent_id) r
 18    Left Join beproj.Be_t_Package_Provider Pp
 19      On R.Package_Id = Pp.Package_Id
 20     And R.Provider_Id = Pp.Provider_Id
 21   Where Pp.Package_Id = '313201011240003590'
 22     And R.Expert_Id = '313201011250004317'
 23     And R.Model_Id = '313201011230003531'
 24     and pp.tech_state = 1;
                    on rrr.rule_id = t0.rule_id) t00
                      *
第 15 行出现错误:
ORA-01722: invalid number

执行该条SQL接收到报错ORA-01722.

SQL> Select Count(1) as ct
  2    From (select t00.*
  3            from (select rrr.*
  4                    from (select /*+no_merge*/
  5                           rr.*
  6                            from (select *
  7                                    from beproj.Be_t_Busi_Result
  8                                   where model_id = 313201011230003531) rr
  9                            left outer join (select 'TOTALSCORE' rule_id1
 10                                              from dual) t
 11                              on rr.rule_id = t.rule_id1
 12                           where t.rule_id1 is null
 13                             and rr.busi_score is not null) rrr
 14                   inner join beproj.Be_t_Rule t0
 15                      on rrr.rule_id = t0.rule_id) t00
 16            Left Join beproj.Be_t_Rule t1
 17              on t00.rule_id = t1.parent_id) r
 18    Left Join beproj.Be_t_Package_Provider Pp
 19      On R.Package_Id = Pp.Package_Id
 20     And R.Provider_Id = Pp.Provider_Id
 21   Where Pp.Package_Id = '313201011240003590'
 22     And R.Expert_Id = '313201011250004317'
 23     And R.Model_Id = '313201011230003531'
 24     and pp.tech_state = 1;
       120
 
 加了/*+no_merge*/提示后运行正常。

 到底什么引起的报错呢？
1.Be_t_Busi_Result表的rule_id是一个字符类型，而Be_t_Rule的parent_id是个number。
2.Be_t_Busi_Result.rule_id除了数字外还包含有'TOTALSCORE'字符串的记录。

我们来看看这2条SQL的执行计划：
第1条SQL的执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 3969509022

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |     1 |   116 |    10  (10)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |                          |     1 |   116 |            |       |
|*  2 |   HASH JOIN ANTI                 |                          |     1 |   116 |    10  (10)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER            |                          |     1 |   104 |     7   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                          |     1 |    95 |     6   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                          |     1 |    85 |     6   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| BE_T_PACKAGE_PROVIDER    |     1 |    25 |     3   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | PK_BE_T_PACKAGE_PROVIDER |     2 |       |     2   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| BE_T_BUSI_RESULT         |     1 |    60 |     3   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | PK_BE_T_BUSI_RESULT      |     1 |       |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN           | RULE_ID                  |     1 |    10 |     0   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN             | IDX_BE_T_RULE_PARENTID   |     6 |    54 |     1   (0)| 00:00:01 |
|  12 |    VIEW                          |                          |     1 |    12 |     2   (0)| 00:00:01 |
|  13 |     FAST DUAL                    |                          |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BE_T_BUSI_RESULT"."RULE_ID"="T"."RULE_ID1")
   6 - filter("PP"."TECH_STATE"=1)
   7 - access("PP"."PACKAGE_ID"=313201011240003590)
   8 - filter("BE_T_BUSI_RESULT"."BUSI_SCORE" IS NOT NULL)
   9 - access("BE_T_BUSI_RESULT"."PACKAGE_ID"=313201011240003590 AND
              "BE_T_BUSI_RESULT"."PROVIDER_ID"="PP"."PROVIDER_ID" AND
              "BE_T_BUSI_RESULT"."EXPERT_ID"=313201011250004317 AND
              "BE_T_BUSI_RESULT"."MODEL_ID"=313201011230003531)
       filter("BE_T_BUSI_RESULT"."MODEL_ID"=313201011230003531)
  10 - access("T0"."RULE_ID"=TO_NUMBER("BE_T_BUSI_RESULT"."RULE_ID"))
  11 - access("T1"."PARENT_ID"(+)=TO_NUMBER("BE_T_BUSI_RESULT"."RULE_ID"))

第2条SQL的执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 2950397761

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     1 |   315 |    26   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                          |     1 |   315 |            |       |
|   2 |   NESTED LOOPS OUTER              |                          |     3 |   945 |    26   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                          |     1 |   306 |    25   (4)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                          |     1 |   281 |    24   (5)| 00:00:01 |
|   5 |      VIEW                         |                          |     1 |   271 |    24   (5)| 00:00:01 |
|*  6 |       HASH JOIN ANTI              |                          |     1 |    72 |    24   (5)| 00:00:01 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| BE_T_BUSI_RESULT         |     1 |    60 |    21   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | PK_BE_T_BUSI_RESULT      |     1 |       |    20   (0)| 00:00:01 |
|   9 |        VIEW                       |                          |     1 |    12 |     2   (0)| 00:00:01 |
|  10 |         FAST DUAL                 |                          |     1 |       |     2   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN            | RULE_ID                  |     1 |    10 |     0   (0)| 00:00:01 |
|* 12 |     TABLE ACCESS BY INDEX ROWID   | BE_T_PACKAGE_PROVIDER    |     1 |    25 |     1   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN            | PK_BE_T_PACKAGE_PROVIDER |     1 |       |     0   (0)| 00:00:01 |
|* 14 |    INDEX RANGE SCAN               | IDX_BE_T_RULE_PARENTID   |     6 |    54 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("BE_T_BUSI_RESULT"."RULE_ID"="T"."RULE_ID1")
   7 - filter("BE_T_BUSI_RESULT"."BUSI_SCORE" IS NOT NULL)
   8 - access("BE_T_BUSI_RESULT"."PACKAGE_ID"=313201011240003590 AND
              "BE_T_BUSI_RESULT"."EXPERT_ID"=313201011250004317 AND "MODEL_ID"=313201011230003531)
       filter("MODEL_ID"=313201011230003531 AND "BE_T_BUSI_RESULT"."EXPERT_ID"=313201011250004317)
  11 - access("T0"."RULE_ID"=TO_NUMBER("RRR"."RULE_ID"))
  12 - filter("PP"."TECH_STATE"=1)
  13 - access("PP"."PACKAGE_ID"=313201011240003590 AND "RRR"."PROVIDER_ID"="PP"."PROVIDER_ID")
  14 - access("T1"."PARENT_ID"(+)=TO_NUMBER("RRR"."RULE_ID"))

通过执行计划就可以看出问题：
select
 rr.*
  from (select *
          from beproj.Be_t_Busi_Result
         where model_id = 313201011230003531) rr
  left outer join (select 'TOTALSCORE' rule_id1 from dual) t
    on rr.rule_id = t.rule_id1
 where t.rule_id1 is null
   and rr.busi_score is not null

这段SQL的目的是要将Be_t_Busi_Result.rule_id中包含'TOTALSCORE'值的记录给排除掉，我们希望它最先执行。

select t00.*
  from (select rrr.*
          from (select /*+no_merge*/
                 rr.*
                  from (select *
                          from beproj.Be_t_Busi_Result
                         where model_id = 313201011230003531) rr
                  left outer join (select 'TOTALSCORE' rule_id1 from dual) t
                    on rr.rule_id = t.rule_id1
                 where t.rule_id1 is null
                   and rr.busi_score is not null) rrr
         inner join beproj.Be_t_Rule t0
            on rrr.rule_id = t0.rule_id) t00
  Left Join beproj.Be_t_Rule t1
    on t00.rule_id = t1.parent_id

这段SQL是将Be_t_Busi_Result.rule_id跟Be_t_Rule.parent_id进行关联，之前已经说过前者是字符串类型，后者是number类型，所以Oracle在执行时对前者强制进行了to_number操作才能对2表进行关联。

但是通过第1条SQL的执行计划可以看出CBO对SQL的执行顺序进行了调整，Be_t_Busi_Result.rule_id排除包含'TOTALSCORE'的记录在Be_t_Busi_Result.rule_id与Be_t_Rule.parent_id字段关联之后执行，这就导致了包含'TOTALSCORE'的记录也会执行to_number转换和Be_t_Rule.parent_id进行关联，对一个包含字母的字符串执行to_number就会接受到ORA-01722的错误了。

第2条SQL通过在SQL中加入/*+no_merge*/提示固定排除Be_t_Busi_Result.rule_id包含'TOTALSCORE'的记录在Be_t_Busi_Result.rule_id与Be_t_Rule.parent_id关联之前执行，这样关联时对Be_t_Busi_Result.rule_id转换的都是不包含'TOTALSCORE'的数字,所以就不会报错了。

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

0
0
上一篇： exp导出出现：ORA-00904: "POLTYP": invalid identifier
下一篇： oracle over()的使用和需要特别注意的地方
请登录后发表评论 登录
全部评论
尛样儿
Oracle数据库管理员，Oracle数据库系统构架员；2012年7月出版《构建最高可用Oracle数据库系统：Oracle 11gR2 RAC管理、维护与性能优化》一书；Oracle 10g OCM。

注册时间：2010-01-05

  • 博文量
    483
  • 访问量
    5649804

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593