一次简单的SQL优化给我的经验--索引不可用的情况

一次简单的SQL优化给我的经验--索引不可用的情况

有一天我遇到了一个同事的求助，他让我帮忙优化一个SQL，这个SQL执行时间很长。于是我查询了执行计划，发现这个SQL竟然要进行一次全表扫描。当时我查看了表的定义，发现在where子句中的条件列上是建了索引的，那为什么执行计划会显示全表扫描呢。这个问题困扰了我很长时间，于是后来我又看了看表的定义，发现了问题的根本所在，作为条件的字段是varchar，而SQL语句中的条件是一串数字！这样的话就会造成索引不可用，处理方法也很简单，加上一对单引号就可以了。

下面是语句和执行计划（这里做了简单化处理，只需要注意全表扫描就可以）：

select * from TEST t where t.spec_prpty_id = 3303;  

Plan hash value: 1357081020 
-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |     1 |     5 |     3   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |     3   (0)| 00:00:01 | 
-------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter(TO_NUMBER("T"."SPEC_PRPTY_ID")=3303)

   这是添加过单引号的执行计划：

Plan hash value: 1128569081 
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| IDX_TEST |     1 |     5 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - access("T"."SPEC_PRPTY_ID"='3303')

     可以看出使用了索引，如果该表数据量大的话，那么这样的效率提升是非常可观的。

     其实从执行计划中的：1 - filter(TO_NUMBER("T"."SPEC_PRPTY_ID")=3303)这一句就能看出原因，就是执行了一次隐式类型转换，这种情况是不会用到索引的，如果这张表足够大，那么这次查询可是要慢的要死了。

     这个例子很简单，很多高手也会嗤之以鼻，但是秉承着从细微处入手学习Oracle的信念的我，还是从这里得到了一个知识点：一下几种情况索引不会被使用：

     1 不等于操作不能用于索引

     2 经过普通或者函数运算的索引列不能使用索引

     3 含前向模糊

     4 索引列为空

     5 数值比较时左右类型不同，相当于做了隐式类型转换

     6 给索引查询的值是未知字段，而不是已知数

      一定要注意，慎之又慎的写SQL。