ITPub博客

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

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

原创 Oracle 作者:yangsir1 时间:2017-09-27 18:40:19 0 删除 编辑
   今天,开发同事说他在测试库执行一条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。

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

请登录后发表评论 登录
全部评论

注册时间:2010-06-28

  • 博文量
    52
  • 访问量
    80548