Oracle因數據不一致而導致的隱式轉換錯誤一例

發布時間：2020-08-10 20:16:25 來源：ITPUB博客閱讀：125 作者：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問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

Oracle因數據不一致而導致的隱式轉換錯誤一例

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

Oracle因數據不一致而導致的隱式轉換錯誤一例

猜你喜歡

最新資訊

相關推薦

相關標簽