今天，開發(fā)同事說他在測試庫執(zhí)行一條SQL的時候，報ORA-01722: invalid number錯誤，但是在生產(chǎn)庫和灰度庫執(zhí)行同一條SQL卻能夠正常執(zhí)行，SQL如下：
  
  select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047;

  問了一下開發(fā)同事基本信息，得知SerialNo的字段類型為varchar2類型，此時未加引號，肯定是進行了隱式轉(zhuǎn)換，但是為什么在生產(chǎn)庫和灰度庫卻能夠執(zhí)行成功呢？帶著如此疑問，進行了以下慢慢的摸索……
  
    最初猜測是不是其他數(shù)據(jù)行的SerialNo字段存在帶有字符的數(shù)據(jù)呢，但是查看了一下BUS_CONTRACT表的表結(jié)構(gòu)，發(fā)現(xiàn)BUS_CONTRACT表的主鍵就是SerialNo字段，此時的查詢，應(yīng)該是可以走主鍵索引而不會全表掃描的，即便是其他數(shù)據(jù)行有帶字符的數(shù)據(jù)，也不會被掃描到才是，可為什么會報錯呢？

  后來通過搜索網(wǎng)絡(luò)上的文章，得知oracle在隱式轉(zhuǎn)換時，如果是VARCHAR2->NUMBER轉(zhuǎn)換，則不會導(dǎo)致索引失效，而如果是NUMBER->VARCHAR2的轉(zhuǎn)換，此時則會讓索引失效，很明顯本次查詢是NUMBER->VARCHAR2的轉(zhuǎn)換，此時即便是有索引，oracle也不會走索引掃描而只會走全表掃描，查看其執(zhí)行計劃，果然如此：

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>


  然后，通知開發(fā)同事，讓他通過如下SQL看一下SerialNo字段是不是存在臟數(shù)據(jù)：

  select ItemStatus，SerialNo from BUSI_CONTRACT；

  開發(fā)人員反饋，果然是有一條記錄不是純數(shù)字而帶有一些字符，讓其刪除該數(shù)據(jù)之后，查詢正常。

  建議跟隱式轉(zhuǎn)換有關(guān)的SQL，最好還是帶上引號，如下是SQL語句select ItemStatus from BUSI_CONTRACT where SerialNo='2016033100000047';的執(zhí)行計劃：

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


  性能明顯優(yōu)于不帶引號的，因為此時沒有經(jīng)歷隱式轉(zhuǎn)換，SQL執(zhí)行走索引掃描了。


  結(jié)論：1.涉及到隱式轉(zhuǎn)換到字段最好加上引號，否則不會走索引；
             2.隱式轉(zhuǎn)換如果是VARCHAR2->NUMBER轉(zhuǎn)換，則不會導(dǎo)致索引失效，而如果是NUMBER->VARCHAR2的轉(zhuǎn)換，此時則會讓索引失效；
             3.之所以NUMBER->VARCHAR2會讓索引失效，應(yīng)該是轉(zhuǎn)換為where to_number(name) = 123。

本文名稱：Oracle因數(shù)據(jù)不一致而導(dǎo)致的隱式轉(zhuǎn)換錯誤一例
URL地址：http://jinyejixie.com/article14/pgeide.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、App開發(fā)、外貿(mào)建站、定制開發(fā)、云服務(wù)器、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)