都說
Oracle存在NUMBER和VARCHAR2類型的隱式轉(zhuǎn)換，嚴(yán)格意義上需要避免，但為什么需要避免，從下面的實驗進行驗證。
1. 創(chuàng)建測試表和索引
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字段創(chuàng)建索引。
2. 查看VARCHAR2->NUMBER的隱式轉(zhuǎn)換
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';
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 = '123'"，Oracle會將字符類型的123轉(zhuǎn)換為NUMBER類型進行比較，此處仍可以使用索引范圍掃描，說明VARCHAR2->NUMBER的隱式轉(zhuǎn)換，未對索引產(chǎn)生影響。
3. 查看NUMBER->VARCHAR2的隱式轉(zhuǎn)換
SQL> select * from tn where name = '123';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 479240418
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 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;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2655062619
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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會將數(shù)值類型的123轉(zhuǎn)換為VARCHAR2字符類型，和name進行比較，此處用了全表掃描，說明name的列索引失效。
總結(jié)：
1. NAME和VARCHAR2之間可以進行隱式轉(zhuǎn)換，其中VARCHAR2->NUMBER不會致使索引失效，NUMBER->VARCHAR2會讓索引失效，因此這類隱式轉(zhuǎn)換，是需要注意避免。
2. 之所以VARCHAR2->NUMBER不會讓索引失效，我猜想是轉(zhuǎn)換為where id = to_number('123')。NUMBER->VARCHAR2會讓索引失效，我猜想是轉(zhuǎn)換為where to_number(name) = 123。
3. 引伸知識點，之所以上面id和name使用的是索引范圍掃描，是由于建立的是非唯1B樹索引，如果是unique索引，則會使用UNIQUE INDEX SCAN的掃描方式。
