溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Oracle因數(shù)據(jù)不一致而導(dǎo)致的隱式轉(zhuǎn)換錯(cuò)誤一例

發(fā)布時(shí)間：2020-08-10 20:16:25 來源：ITPUB博客 閱讀：125 作者：yangsir1 欄目：關(guān)系型數(shù)據(jù)庫
   今天，開發(fā)同事說他在測試庫執(zhí)行一條SQL的時(shí)候，報(bào)ORA-01722: invalid number錯(cuò)誤，但是在生產(chǎn)庫和灰度庫執(zhí)行同一條SQL卻能夠正常執(zhí)行，SQL如下：
  
  select ItemStatus from BUS_CONTRACT where SerialNo=2016033100000047;

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

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

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，最好還是帶上引號(hào)，如下是SQL語句select ItemStatus from BUSI_CONTRACT where SerialNo='2016033100000047';的執(zhí)行計(jì)劃：

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)于不帶引號(hào)的，因?yàn)榇藭r(shí)沒有經(jīng)歷隱式轉(zhuǎn)換，SQL執(zhí)行走索引掃描了。


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

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手