Oracle的隐式轉換

都說Oracle存在NUMBER和VARCHAR2類型的隐式轉換，嚴格意義上需要避免，但為何需要避免，從下面的實驗進行驗證。

1. 建立測試表和索引

create table tn (id number, name varchar2(1));

create index idx_tn on tn (id);

create index idx_tn on tn (name);

分别對NUMBER類型的id字段，VARCHAR2類型的name字段建立索引。

2. 檢視VARCHAR2->NUMBER的隐式轉換

SQL> select * from tn where id = 1;

no rows selected

Execution Plan

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

Plan hash value: 3532270966

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_TN | 1 | 13 | 1 (0)| 00:00:01 |

"where id = 1"用的是列索引範圍掃描。

SQL> select * from tn where id = '123';

"where id = '123'"，Oracle會将字元類型的123轉換為NUMBER類型進行比較，此處仍可使用索引範圍掃描，說明VARCHAR2->NUMBER的隐式轉換，未對索引産生影響。

3. 檢視NUMBER->VARCHAR2的隐式轉換

SQL> select * from tn where name = '123';

Plan hash value: 479240418

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

| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TN | 1 | 15 | 1 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TN_NAME | 1 | | 1 (0)| 00:00:01 |

"where name = '123'"使用的是索引範圍掃描。

SQL> select * from tn where name = 123;

Plan hash value: 2655062619

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

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

|* 1 | TABLE ACCESS FULL| TN | 1 | 15 | 2 (0)| 00:00:01 |

"where name = 123"，Oracle會将數值類型的123轉換為VARCHAR2字元類型，和name進行比較，此處用了全表掃描，說明name的列索引失效。

總結：

1. NAME和VARCHAR2之間可以進行隐式轉換，其中VARCHAR2->NUMBER不會導緻索引失效，NUMBER->VARCHAR2會讓索引失效，是以這種隐式轉換，是需要注意避免。

2. 之是以VARCHAR2->NUMBER不會讓索引失效，我猜測是轉換為where id = to_number('123')。NUMBER->VARCHAR2會讓索引失效，我猜測是轉換為where to_number(name) = 123。

3. 引申知識點，之是以上面id和name使用的是索引範圍掃描，是因為建立的是非唯一B樹索引，如果是unique索引，則會使用UNIQUE INDEX SCAN的掃描方式。

補充：

經lhrbest的指正，從謂詞條件即可看出端倪。

​附：

​SQL> select * from tn where id = '123';

​Predicate Information (identified by operation id):

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

   2 - access("ID"=123)

​

​SQL> select * from tn where name = 123;

Predicate Information (identified by operation id):

   1 - filter(TO_NUMBER("NAME")=123)

​可以看出此處對NAME做了TO_NUMBER轉換，導緻索引失效。