----------------------------------------------
?| Id? | Operation? ? ? ? ? ? ? ? ? | Name? ? | Rows? | Bytes | Cost (%CPU)| Time? ? |
?---------------------------------------------------------------------------------------
?|? 0 | SELECT STATEMENT? ? ? ? ? ? |? ? ? ? |? ? 1 |? ? 7 |? ? 2? (0)| 00:00:01 |
?|? 1 |? TABLE ACCESS BY INDEX ROWID| TEST? ? |? ? 1 |? ? 7 |? ? 2? (0)| 00:00:01 |
?|*? 2 |? INDEX RANGE SCAN? ? ? ? ? | IDX_ID1 |? ? 1 |? ? ? |? ? 1? (0)| 00:00:01 |
?---------------------------------------------------------------------------------------
?Predicate Information (identified by operation id):
?PLAN_TABLE_OUTPUT
? ? 2 - access("ID1"=2)
隐式转换，由字符转换为数字的时候，直接走了全表扫描
SQL> explain plan for select *from test where id2=3;
?SQL> select *from table(dbms_xplan.display);
?PLAN_TABLE_OUTPUT
?--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
?Plan hash value: 1357081020
?--------------------------------------------------------------------------
?| Id? | Operation? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? |
?--------------------------------------------------------------------------
?|? 0 | SELECT STATEMENT? |? ? ? |? ? 1 |? ? 7 |? ? 3? (0)| 00:00:01 |
?|*? 1 |? TABLE ACCESS FULL| TEST |? ? 1 |? ? 7 |? ? 3? (0)| 00:00:01 |
?--------------------------------------------------------------------------
?Predicate Information (identified by operation id):
?---------------------------------------------------
?PLAN_TABLE_OUTPUT
?--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
? ? 1 - filter(TO_NUMBER("ID2")=3)
可见在这个方面MySQL和Oracle中的表现是一致的，对于这种隐式转换还是要多加注意。