欢迎来到网际学院,让您的头脑满载而归!

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

发布日期:2017-08-20 10:50:43 作者:管理员 阅读:1282

对于oracle数据库来说,在条件允许的情况下,oracle可以帮我们自动做数据类型的转换,但是oracle还是建议我们最好使用转换函数(to_char()、to_date()、to_number()等)做显示的转换下面这个例子简单的测试了

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


Copyright oneie ©2014-2017 All Rights Reserved. 所有资料来源于互联网对相关版权责任概不负责。如发现侵犯了您的版权请与我们联系。 网际学院 版权所有
免责声明  商务合作及投稿请联系 QQ:86662817