Oracle的隱式轉換，Oracle隱式轉換

Oracle的隱式轉換，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';no rows selectedExecution 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轉換為NUMBER類型進行比較，此處仍可使用索引範圍掃描，說明VARCHAR2->NUMBER的隱式轉換，未對索引產生影響。

3. 查看NUMBER->VARCHAR2的隱式轉換
SQL> select * from tn where name = '123';no rows selectedExecution 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 selectedExecution 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會將數值類型的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的掃描方式。