ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引失效系列——隐式类型转换

索引失效系列——隐式类型转换

原创 Linux操作系统 作者:realkid4 时间:2011-04-24 23:51:33 0 删除 编辑

 

索引是我们进行优化的一种重要方式。实际工作中,一个简单的索引,可能就会大大提升提高关键业务作业效率,最终提升用户满意度。在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 where status=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语句的细节准备好。无论是前端代码开发,还是后台大作业编写,都要把握好类型匹配的情况。避免出现潜在的性能风险。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-693587/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753666