Oracle因数据不一致而导致的隐式转换错误一例

发布时间：2020-08-10 20:16:25 来源：ITPUB博客阅读：122 作者：yangsir1 栏目：关系型数据库

今天，开发同事说他在测试库执行一条SQL的时候，报ORA-01722: invalid number错误，但是在生产库和灰度库执行同一条SQL却能够正常执行，SQL如下：

  select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047;

问了一下开发同事基本信息，得知SerialNo的字段类型为varchar2类型，此时未加引号，肯定是进行了隐式转换，但是为什么在生产库和灰度库却能够执行成功呢？带着如此疑问，进行了以下慢慢的摸索……

最初猜测是不是其他数据行的SerialNo字段存在带有字符的数据呢，但是查看了一下BUS_CONTRACT表的表结构，发现BUS_CONTRACT表的主键就是SerialNo字段，此时的查询，应该是可以走主键索引而不会全表扫描的，即便是其他数据行有带字符的数据，也不会被扫描到才是，可为什么会报错呢？

后来通过搜索网络上的文章，得知oracle在隐式转换时，如果是VARCHAR2->NUMBER转换，则不会导致索引失效，而如果是NUMBER->VARCHAR2的转换，此时则会让索引失效，很明显本次查询是NUMBER->VARCHAR2的转换，此时即便是有索引，oracle也不会走索引扫描而只会走全表扫描，查看其执行计划，果然如此：

SQL> set autotrace traceonly
SQL>
SQL> select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047;
ERROR:
ORA-01722: invalid number

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 809618537

---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 661 (1)| 00:00:08 |

|* 1 | TABLE ACCESS FULL| BUS_CONTRACT | 1 | 21 | 661 (1)| 00:00:08 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("SERIALNO")=2016033100000047)

Statistics
----------------------------------------------------------
0 recursive calls
1    db block gets
2341 consistent gets
2392 physical reads
0    redo size
529   bytes sent via SQL*Net to client
519   bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0    sorts (disk)
0    rows processed

SQL>
SQL>

然后，通知开发同事，让他通过如下SQL看一下SerialNo字段是不是存在脏数据：

  select ItemStatus，SerialNo from BUSI_CONTRACT；

开发人员反馈，果然是有一条记录不是纯数字而带有一些字符，让其删除该数据之后，查询正常。

  建议跟隐式转换有关的SQL，最好还是带上引号，如下是SQL语句select ItemStatus from BUSI_CONTRACT where SerialNo='2016033100000047';的执行计划：

SQL>
SQL> select ItemStatus from BUS_CONTRACT where SerialNo='2016033100000047';

Execution Plan
----------------------------------------------------------
Plan hash value: 338903438

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| BUS_CONTRACT | 1 | 21 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_BUS_CONTRACT | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("SERIALNO"='2016033100000047')

Statistics
----------------------------------------------------------
1   recursive calls
0   db block gets
4   consistent gets
0   physical reads
0   redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2   SQL*Net roundtrips to/from client
0   sorts (memory)
0   sorts (disk)
1   rows processed

性能明显优于不带引号的，因为此时没有经历隐式转换，SQL执行走索引扫描了。

结论：1.涉及到隐式转换到字段最好加上引号，否则不会走索引；
2.隐式转换如果是VARCHAR2->NUMBER转换，则不会导致索引失效，而如果是NUMBER->VARCHAR2的转换，此时则会让索引失效；
3.之所以NUMBER->VARCHAR2会让索引失效，应该是转换为where to_number(name) = 123。

向AI问一下细节

Oracle因数据不一致而导致的隐式转换错误一例

猜你喜欢

最新资讯

相关推荐

相关标签