隐式转换导致索引失效

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