本案例来自西区某客户，应用反应某条sql好几年都运行正常，最近经常会出现ORA-01722错误。
|
1 2 3 4 |
[oracle@rac1 ~]$ oerr ora 1722 01722, 00000, "invalid number" // *Cause: The specified number was invalid. // *Action: Specify a valid number. |
该错误通常发生在类型转换时遇到非法数字，如：
|
1 2 3 4 5 |
SQL> select to_number('a') from dual; select to_number('a') from dual * ERROR at line 1: ORA-01722: invalid number |
按道理该错误应该非常容易诊断，肯定是表中字符类型的字段上存在非法数字导致的，但是应用非常坚持的认为该sql一直运行正常，那么我们就来看看吧。
|
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
SQL> SELECT DISTINCT A.REAL_PAY_CASH, A.REAL_PAY_DATE, 2 3 C.PK_INITIAL, A.PK_LOAN_PLAN, 4 5 C.CONT_LOAN_BATCH, D.PK_LEASE_LOAN_PLAN, 6 7 B.CHARGE_OFF_STATUS, D.LOAN_BATCH 8 9 FROM nc63.yls_LOAN_PLAN A INNER JOIN nc63.yls_LOAN_DEAL LD 10 11 ON LD.PK_LOAN_DEAL = A.PK_LOAN_DEAL INNER JOIN (SELECT yi.* 12 13 FROM nc63.yls_INOUT_PLAN_C YI left join nc63.yls_event_type et 14 15 on YI.trans_type = et.pk_event_type WHERE et.EVENT_CODE IN ('10101', '10102')) B 16 17 ON A.PK_INOUT_PLAN = B.PK_INOUT_PLAN INNER JOIN nc63.yls_CONTRACT_C C 18 19 ON C.PK_CONTRACT = b.PK_CONTRACT INNER JOIN (SELECT PK_INITIAL, 20 21 TRANS_TYPE, CH.CONT_LOAN_BATCH, 22 23 LEASE_TIME, PK_INOUT_PLAN, 24 25 CH.OPERATE_DATE FROM nc63.yls_INOUT_PLAN_H IPH 26 27 inner join nc63.yls_event_type et on et.pk_event_type = iph.trans_type 28 29 INNER JOIN nc63.yls_RENT_CALCULATOR RC ON RC.PK_RENT_CALCULATOR = IPH.SOURCE_BILL 30 31 INNER JOIN nc63.yls_CONTRACT_H CH ON CH.PK_CONTRACT = rc.PK_CONTRACT 32 33 where et.EVENT_CODE in ('10101', '10102')) HD ON C.PK_INITIAL = HD.PK_INITIAL 34 35 AND C.CONT_LOAN_BATCH = HD.CONT_LOAN_BATCH AND B.TRANS_TYPE = HD.TRANS_TYPE 36 37 AND B.LEASE_TIME = HD.LEASE_TIME INNER JOIN nc63.yls_LEASE_LOAN_PLAN_C D 38 39 ON D.LOAN_BATCH = NVL(REPLACE(B.LEASE_TIME, '投放日', ''), 1) - 1 AND D.SOURCE_BILL = B.SOURCE_BILL 40 41 WHERE EXISTS (SELECT 1 FROM nc63.yls_EVENT_TYPE Y 42 43 WHERE Y.EVENT_CODE IN ('10101', '10102') AND Y.PK_EVENT_TYPE = B.TRANS_TYPE) 44 45 AND A.IF_CANCEL = 1 AND A.IF_APPROVE_CANCEL = 1 46 47 AND HD.OPERATE_DATE >= NVL(LD.CHECK_DATE, LD.REAL_PAY_DATE) 48 ; ON D.LOAN_BATCH = NVL(REPLACE(B.LEASE_TIME, '投放日', ''), 1) - 1 * ERROR at line 39: ORA-01722: invalid number |
报错很明显，问题出在ON D.LOAN_BATCH = NVL(REPLACE(B.LEASE_TIME, ‘投放日’, ”), 1) – 1上，其中LOAN_BATCH为number类型，LEASE_TIME为varchar2类型，熟悉隐式转换规则的话，肯定知道该连接条件会被CBO改写D.LOAN_BATCH=TO_NUMBER(NVL(REPLACE(B.LEASE_TIME,’投放日’,”),’1′))-1，那么问题肯定出在LEASE_TIME上。B是一个inline view的别名，该字段来自于表YLS_INOUT_PLAN_C
|
1 2 3 4 5 6 |
SELECT NVL(REPLACE("YI"."LEASE_TIME",'投放日',''),'1')-1,count(*) FROM NC63.YLS_INOUT_PLAN_C YI left join NC63.yls_event_type et on YI.trans_type = et.pk_event_type WHERE et.EVENT_CODE IN ('10101', '10102') group by NVL(REPLACE("YI"."LEASE_TIME",'投放日',''),'1')-1; |
通过该sql检测结果集输出确实是正常的，但是YLS_INOUT_PLAN_C的LEASE_TIME除了”投放日“之外还存在其他中文字符，之所以上面的结果集能正常是因为业务逻辑上yls_event_type的谓词EVENT_CODE IN (‘10101’, ‘10102’)过滤后再关联YLS_INOUT_PLAN_C可以保证过滤掉除了”投放日“之外的其他中文字符。
那么就存在一种可能，当inline view发生view merge时，会得到更多的表驱动顺序。这也是查询转换的意义所在，其执行计划如果选择是YLS_INOUT_PLAN_C直接与yls_LEASE_LOAN_PLAN_C关联时，就会报出ORA-01722。
|
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Plan hash value: 1090028100 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 502 | 382 (1)| 00:00:05 | | 1 | HASH UNIQUE | | 1 | 502 | 382 (1)| 00:00:05 | | 2 | NESTED LOOPS | | 1 | 502 | 381 (1)| 00:00:05 | | 3 | NESTED LOOPS | | 1 | 502 | 381 (1)| 00:00:05 | | 4 | NESTED LOOPS | | 1 | 475 | 380 (1)| 00:00:05 | |* 5 | HASH JOIN | | 1 | 430 | 379 (1)| 00:00:05 | |* 6 | HASH JOIN | | 3 | 1164 | 353 (1)| 00:00:05 | | 7 | NESTED LOOPS | | 17 | 5644 | 225 (1)| 00:00:03 | | 8 | NESTED LOOPS | | 17 | 5644 | 225 (1)| 00:00:03 | | 9 | NESTED LOOPS | | 17 | 4913 | 220 (1)| 00:00:03 | |* 10 | HASH JOIN | | 17 | 4148 | 215 (1)| 00:00:03 | |* 11 | HASH JOIN | | 331 | 53622 | 198 (1)| 00:00:03 | |* 12 | TABLE ACCESS FULL | YLS_EVENT_TYPE | 2 | 54 | 9 (0)| 00:00:01 | |* 13 | HASH JOIN | | 1821 | 240K| 189 (1)| 00:00:03 | | 14 | TABLE ACCESS FULL | YLS_LEASE_LOAN_PLAN_C | 787 | 35415 | 9 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | YLS_INOUT_PLAN_C | 13792 | 1212K| 180 (1)| 00:00:03 | |* 16 | TABLE ACCESS FULL | YLS_LOAN_PLAN | 698 | 57236 | 17 (0)| 00:00:01 | | 17 | TABLE ACCESS BY INDEX ROWID| YLS_CONTRACT_C | 1 | 45 | 1 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | CONS_PK_YLS_CONTRACT_C | 1 | | 1 (0)| 00:00:01 | |* 19 | INDEX UNIQUE SCAN | CONS_PK_LOAN_DEAL | 1 | | 1 (0)| 00:00:01 | | 20 | TABLE ACCESS BY INDEX ROWID | YLS_LOAN_DEAL | 1 | 43 | 1 (0)| 00:00:01 | | 21 | TABLE ACCESS FULL | YLS_CONTRACT_H | 2912 | 159K| 128 (0)| 00:00:02 | | 22 | TABLE ACCESS FULL | YLS_RENT_CALCULATOR | 1231 | 51702 | 25 (0)| 00:00:01 | |* 23 | TABLE ACCESS BY INDEX ROWID | YLS_INOUT_PLAN_H | 1 | 45 | 1 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | I_INOUT_PLAN_H_SRCBILL | 19 | | 1 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | CONS_PK_EVENT_TYPE | 1 | | 1 (0)| 00:00:01 | |* 26 | TABLE ACCESS BY INDEX ROWID | YLS_EVENT_TYPE | 1 | 27 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CH"."PK_CONTRACT"="RC"."PK_CONTRACT") 6 - access("C"."PK_INITIAL"="CH"."PK_INITIAL" AND "C"."CONT_LOAN_BATCH"="CH"."CONT_LOAN_BATCH") filter("CH"."OPERATE_DATE">=NVL("LD"."CHECK_DATE","LD"."REAL_PAY_DATE")) 10 - access("A"."PK_INOUT_PLAN"="YI"."PK_INOUT_PLAN") 11 - access("YI"."TRANS_TYPE"="ET"."PK_EVENT_TYPE") 12 - filter("ET"."EVENT_CODE"='10101' OR "ET"."EVENT_CODE"='10102') 13 - access("D"."LOAN_BATCH"=TO_NUMBER(NVL(REPLACE("YI"."LEASE_TIME",'投放日',''),'1'))-1 AND "D"."SOURCE_BILL"="YI"."SOURCE_BILL") 16 - filter("A"."PK_LOAN_DEAL" IS NOT NULL AND "A"."PK_INOUT_PLAN" IS NOT NULL AND "A"."IF_APPROVE_CANCEL"=1 AND "A"."IF_CANCEL"=1) 18 - access("C"."PK_CONTRACT"="YI"."PK_CONTRACT") 19 - access("LD"."PK_LOAN_DEAL"="A"."PK_LOAN_DEAL") 23 - filter("YI"."TRANS_TYPE"="IPH"."TRANS_TYPE" AND "YI"."LEASE_TIME"="IPH"."LEASE_TIME") 24 - access("RC"."PK_RENT_CALCULATOR"="IPH"."SOURCE_BILL") 25 - access("ET"."PK_EVENT_TYPE"="IPH"."TRANS_TYPE") 26 - filter("ET"."EVENT_CODE"='10101' OR "ET"."EVENT_CODE"='10102') |
从执行计划中可以看到，inline view发生了view merge，并且还发生了hash join的SWAP_JOIN_INPUTS，使得执行计划为：
|
1 2 3 4 5 |
|* 11 | HASH JOIN | | 331 | 53622 | 198 (1)| 00:00:03 | |* 12 | TABLE ACCESS FULL | YLS_EVENT_TYPE | 2 | 54 | 9 (0)| 00:00:01 | |* 13 | HASH JOIN | | 1821 | 240K| 189 (1)| 00:00:03 | | 14 | TABLE ACCESS FULL | YLS_LEASE_LOAN_PLAN_C | 787 | 35415 | 9 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | YLS_INOUT_PLAN_C | 13792 | 1212K| 180 (1)| 00:00:03 | |
这样在谓词13 – access(“D”.”LOAN_BATCH”=TO_NUMBER(NVL(REPLACE(“YI”.”LEASE_TIME”,’投放日’,”),’1′))-1连接时就会出现ORA-01722，这种现象mos有一篇文档说明ORA-01722, ORA-01839, ORA-01841, ORA-01847 or ORA-01858 from Queries with Dependent Predicates (Doc ID 232243.1)
|
1 2 3 4 5 6 |
Explanation: If an application has been designed so that it is possible to compare columns with data of a different type and type conversion is not enforced, then Oracle will do any necessary type conversion itself according to the rules documented in the Oracle SQL Reference Manual. When there is a situation where 'loose typing' is combined with a variable predicate order then it is possible to get type conversion errors (like the above) when column values that cause type errors are not eliminated before type conversion occurs. This is not a bug. |
解决方法也很简单，使用no_merge不让inline view发生view merge即可，或者在inline view中加入rownum伪列，当inline view中出现rownum也是不能做view merge的，该sql的inline view改写如下：
|
1 2 3 4 5 6 7 8 9 10 11 |
(SELECT rownum rn, yi.* FROM NC63.YLS_INOUT_PLAN_C YI left join NC63.yls_event_type et on YI.trans_type = et.pk_event_type WHERE et.EVENT_CODE IN ('10101', '10102')) B or (SELECT /*+no_merge*/ yi.* FROM NC63.YLS_INOUT_PLAN_C YI left join NC63.yls_event_type et on YI.trans_type = et.pk_event_type WHERE et.EVENT_CODE IN ('10101', '10102')) B |
当然这种sql写法本身也是不严谨的，虽然业务逻辑是没有问题的，但是太依赖于执行计划的表驱动顺序，所以在sql开发中特别是遇到类型转换时，需要注意这种情况。
模拟该案例也非常简单，test demo如下：
|
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 |
create table test.t1 (A varchar2(100), B varchar2(100)); create table test.t2 (A varchar2(100),B varchar2(100)); create table test.t3 (A number); insert into test.t1 values ('数字',1); insert into test.t1 values ('字母','a'); insert into test.t2 values ('数字','shuzi'); insert into test.t2 values ('字母','zimu'); insert into test.t3 values (1); commit; SQL> select count(*) from test.t3,test.t2,test.t1 2 where t2.A=t1.A and t2.B='shuzi' and t3.A=t1.B; where t2.A=t1.A and t2.B='shuzi' and t3.A=t1.B * ERROR at line 2: ORA-01722: invalid number SQL> select /*+LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1" "T3"@"SEL$1")*/count(*) from test.t3,test.t2,test.t1 2 where t2.A=t1.A and t2.B='shuzi' and t3.A=t1.B; COUNT(*) ---------- 1 |