索引是我们进行优化的一种重要方式。实际工作中,一个简单的索引,可能就会大大提升提高关键业务作业效率,最终提升用户满意度。在CBO时代,DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。
问题提出
下面是一个模拟的开发场景。
//构建数据表
SQL> create table t as select * from dba_objects ;
Table created
SQL> create index idx_t_id on t(object_id);
Index created
SQL>create index idx_t_staus on t(status);
Index created
SQL> update t set status=to_char(length(owner));
51367 rows updated
SQL> commit;
Commit complete
SQL> desc t
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
(篇幅所限,有省略…)
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
STATUS VARCHAR2(7) Y
//数据分布
SQL> select status, count(*) from t group by status;
STATUS COUNT(*)
------- ----------
3 23655
6 24178
18 8
21 296
10 10
8 139
5 1358
7 787
14 381
2 554
4 1
下面我们执行一个简单的select查询,观察执行计划方案。
SQL> explain plan for select * from t wherestatus=14;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 161 (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1 | 89 | 161 (4)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("STATUS")=14)
13 rows selected
SQL> rollback;
Rollback complete
注意这个实验结果,我们在对应的status列上加入了索引,却没有执行索引路径。
此时,我们注意到实验的select语句中,where条件“status=14”,而数据表上该列的类型为varchar2(7)。那么,是不是这个原因引起的索引路径计划问题呢?
SQL> explain plan for select * from t where status='14';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1853254432
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 324 | 28836 | 11 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 324 | 28836 | 11 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_STAUS | 324 | | 2 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='14')
14 rows selected
SQL> rollback;
Rollback complete
进行简单的SQL修改之后,我们发现执行计划变化为我们希望的方式。原因是如何呢?在之前的执行计划中,存在“1 - filter(TO_NUMBER("STATUS")=14)”的部分。这说明在进行条件搜索的时候,Oracle发现类型不匹配,隐式的将数据列加入了一个to_number函数。这样,Oracle就需要一个如函数索引的索引列来支持搜索路径,于是索引idx_t_status的搜索成本就大大增加。经过试算,Oracle认为全表扫面的成本相对较低。
显然,这种情况是我们开发人员不希望看到的。我们已经付出了成本来构建维护索引,对关键用例功能不能支持,应该是我们避免的。其实,解决的方案也很容易,就是注意细节。在where条件书写的时候明确清楚属性列类型,这样就可以避免这种情况发生。
那么,是不是发生类型转换就一定不走索引呢?我们看下一个例子。
SQL> explain plan for select * from t where object_id='1000';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 89 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
14 rows selected
SQL> rollback;
Rollback complete
例外出现了。Object_id类型为number,如果根据刚才我们的理论,where条件中出现的“object_id=’1000’”就不应该出现索引路径。这个是怎么回事呢?
我们观察到搜索的访问条件“2 - access("OBJECT_ID"=1000)”,说明语句生成执行计划的时候,输入条件已经转化为数字类型1000。所以生成的执行计划是不会被隐式类型转化所困扰。
那么,笔者猜想是在Oracle接受到查询语句之后,会有一个SQL改写的过程。在其中根据一些规则条件,对SQL进行改写优化。当Oracle发现这样简单的隐式类型转化后,会自主的将字符串1000转化为类型匹配的数字类型1000。这个例子就告诉我们,一些简单的隐式类型转化也是会走索引的。
最后要说一下发生隐式类型转化的开发场景。在开发中,通常我们要避免出现隐式类型转换,要把SQL语句的细节准备好。无论是前端代码开发,还是后台大作业编写,都要把握好类型匹配的情况。避免出现潜在的性能风险。
相关推荐
C语言隐式类型转换规则。了解一下对写程序有好处,可以防止不必要的错误
类的转换,C++的内部数据类型遵循隐式类型转换规则,成员转换函数
本文将要为您介绍的是Mysql隐式类型转换原则,具体实现方法: MySQL 的隐式类型转换原则: - 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种...
JavaScript隐式类型转换_.docx
不同类型的变量比较要先转类型,叫做类型转换,类型转换也叫隐式转换。隐式转换通常发生在运算符加减乘除,等于,还有小于,大于等。。 typeof '11' //string typeof(11) //number '11' < 4 //false 本章节单独...
c编程过程中的数据类型隐式转换,帮助初学者了解数据结构的类型,减少数据类型锁带来的编码错误
主要介绍了c++隐式类型转换的二个示例,需要的朋友可以参考下
主要介绍了JS面试题大坑之隐式类型转换实例代码,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
相信大家都知道隐式类型转换有无法命中索引的风险,在高并发、大数据量的情况下,命不中索引带来的后果非常严重。将数据库拖死,继而整个系统崩溃,对于大规模系统损失惨重。所以下面通过本文来好好学习下MySQL隐式...
本文实例讲述了JavaScript强制类型转换和隐式类型转换。分享给大家供大家参考,具体如下: 在JavaScript中声明变量不需指定类型, 对变量赋值也没有类型检查,同时JavaScript允许隐式类型转换。这些特征说明...
单元十:数据类型的隐式转换-数据类型的隐式转换完整版资料.pptx
本文实例讲述了JavaScript隐式类型转换操作。分享给大家供大家参考,具体如下: JavaScript的数据类型是非常弱的(不然不会叫它做弱类型语言了)!在使用算术运算符时,运算符两边的数据类型可以是任意的,比如,一...
前几天在看到一篇文章:价值百万的 MySQL 的隐式类型转换感觉写的很不错,再加上自己之前也对MySQL的隐式转化这边并不是很清楚,所以就顺势整理了一下。希望对大家有所帮助。 当我们对不同类型的值进行比较的时候,...
此外,对索引列进行了隐式转换也可能导致索引失效。另外,索引失效还可能与查询的条件有关,例如在进行范围查询时,MySQL可能无法使用索引。 针对这些问题,可以通过优化查询语句来解决。避免对索引列进行函数操作...
如果把通过函数或方法调用,明确的将某种类型转换成另一种类型称为显示转换 ,相反则称为隐式类型转换 。google和维基百科中没有找到“显示类型转换”,“隐式类型转换”的字眼。暂且这么称呼。 一、 运算中存在的...
1 operator隐式类型转换 1.1 std::ref源码中reference_wrapper隐式类型转换 在std::ref的实现中有如下一段代码: template class reference_wrapper : public _Reference_wrapper_base<typename>::type> { _Tp*...