Oracle隐式数据类型转换对索引的影响

对于oracle数据库来说，在条件允许的情况下，oracle可以帮我们自动做数据类型的转换，但是oracle还是建议我们最好使用转换函数（to_char()、to_date()、to_number()等）做显示的转换

下面这个例子简单的测试了，由于oracle自动执行隐式转换 ，导致索引无法正确使用的例子

建表T，其中ID列是字符类型
scott@DB01> create table t(id varchar2(10),name varchar2(10),sal number);
Table created.

描述表EMP，我们注意到empno是数字类型
scott@DB01> desc emp
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)

使用子查询插入插入数据到T表，我们其实应该意识到这里就涉及到数据类型的转换，T表的ID列是字符类型，虽然查询的结果看着是数字
scott@DB01> insert into t select empno,ename,sal from emp;
14 rows created.

scott@DB01> commit;
Commit complete.

scott@DB01> select * from t;

ID         NAME              SAL
---------- ---------- ----------
7369       SMITH            1000
7499       ALLEN            1600
7521       WARD             1250
7566       JONES            2975
7654       MARTIN           1250
7698       BLAKE            2850
7782       CLARK            2450
7788       SCOTT            3000
7839       KING             5000
7844       TURNER           1500
7876       ADAMS            1100
7900       JAMES             950
7902       FORD             3000
7934       MILLER           1300
14 rows selected.

在表T上创建索引T_IDX
scott@DB01> create index t_idx on t(id);
Index created.

scott@DB01> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(10)
 NAME                                                           VARCHAR2(10)
 SAL                                                            NUMBER

很多人会想当然的认为，oracle既然可以帮我们做隐式数据类型的转换，那么下面两个语句的效果应该是一样的，如果光从结果来看确实如此。但实际上性能是有非常大的差异的
scott@DB01> select * from t where id=7369;

ID         NAME              SAL
---------- ---------- ----------
7369       SMITH            1000

scott@DB01> select * from t where id='7369';

ID         NAME              SAL
---------- ---------- ----------
7369       SMITH            1000

使用autotrace工具查看语句的执行计划，我们会发现第一条语句发生了数据类型的隐式转换，导致的结果是语句没有使用到索引T_IDX
scott@DB01> set autotrace trace exp
scott@DB01> select * from t where id=7369;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    27 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("ID")=7369)

Note
-----
   - dynamic sampling used for this statement
 
scott@DB01>
scott@DB01> select * from t where id='7369';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"='7369')

Note
-----
   - dynamic sampling used for this statement