玉面飞龙的BLOG

订阅Tom的Blog，今天讲了Explain Plan不准的陷阱，涉及到了”隐式变量转换”

In the predicate information, we see that when you compare a number to a string, Oracle will TO_NUMBER(the string).  That of course makes using the index on the string not possible!

当开发人员绑定变量给游标时候，要注意绑定变量的类型是否和数据库字段类型一致，否则可能无法选择正确的索引。

最常见的是，索引建立在字符串类型的列(str)上，当绑定了一个数字类型(num_val)的时候，发生隐式转换, to_number(str)=num_val. 这时候虽然str列上有索引，但=左边的表达式作了计算to_number,导致该索引无法被使用。

SQL> create table t (idnum number,idstr varchar2(32));
Table created.
SQL> insert into t select rownum,rownum from dba_objects;
8429 rows created.
SQL> commit;
Commit complete.
SQL> create index tidxon_num on t(idnum);
Index created.
SQL> create index tidxon_str on t(idstr);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> alter session set optimizer_mode=choose;
Session altered.

SQL>  explain plan for select * from t where idnum=’1000′;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
—————————————————————————
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
—————————————————————————
|   0 | SELECT STATEMENT            |             |     1 |     7 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |     7 |     2 |
|*  2 |   INDEX RANGE SCAN          | TIDXON_NUM  |     1 |       |     1 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 - access(”T”.”IDNUM”=1000)
Note: cpu costing is off
15 rows selected.

SQL>  explain plan for select * from t where idstr=1000;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
——————————————————————–
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
——————————————————————–
|   0 | SELECT STATEMENT     |             |     1 |     7 |     4 |
|*  1 |  TABLE ACCESS FULL   | T           |     1 |     7 |     4 |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————
   1 - filter(TO_NUMBER(”T”.”IDSTR”)=1000)
Note: cpu costing is off
14 rows selected.
在最后的例子中，IDSTR为字符类型，当绑定数字类型的数值时候，Oracle默认作to_number转换，导致无法使用到该索引。

 implicit conversions 和冲动一样都是魔鬼。