[oracle]当查询条件两边类型不同时，导致sql不走索引，及相关总结

有一张表connectivity，其中有字段id [number]、head_node [varchar2(20)]。并对id、head_node分别建立一个索引。

然后奇怪的事情发生了，下面两条sql及其解释计划结果:

1、select * from connectivity where head_node = '123'

Plan Hash Value : 381095144

| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 3 | 288 | 7 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | CONNECTIVITY | 3 | 288 | 7 | 00:00:01 |

| * 2 | INDEX RANGE SCAN | CONNECTIVITY_INDEX_HEAD_NODE | 3 | | 4 | 00:00:01 |
Predicate Information (identified by operation id):

2 - access("HEAD_NODE"='123')

2、select * from connectivity where head_node = 123

Plan Hash Value : 2615394970

| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 3 | 288 | 253389 | 00:50:41 |

| * 1 | TABLE ACCESS FULL | CONNECTIVITY | 3 | 288 | 253389 | 00:50:41 |
Predicate Information (identified by operation id):

1 - filter(TO_NUMBER("HEAD_NODE")=123)

第二条sql没有加引号，然后就没有走索引了，TABLE ACCESS FULL 的效率自然是超级差了。。

什么原因的，观察解释计划我们发现，oracle自动将条件head_node = 123转为了TO_NUMBER("HEAD_NODE")=123

显然，当列名被丢到了一个函数中，oracle就不会去走索引了，因为函数的值和索引本身没有联系嘛。

另一方面，对于表中的另一字段id [number],即使我们如下这样写sql，依然会走索引：

select * from dwyg_sc.connectivity where id = '123'

Plan Hash Value : 1159157538

| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 1 | 96 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | CONNECTIVITY | 1 | 96 | 2 | 00:00:01 |

| * 2 | INDEX UNIQUE SCAN | CONNECTIVITY_INDEX_ID | 1 | | 1 | 00:00:01 |
Predicate Information (identified by operation id):

2 - access("ID"=123)

select * from dwyg_sc.connectivity where id = 'abc'

Plan Hash Value : 1159157538

| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 1 | 96 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | CONNECTIVITY | 1 | 96 | 2 | 00:00:01 |

| * 2 | INDEX UNIQUE SCAN | CONNECTIVITY_INDEX_ID | 1 | | 1 | 00:00:01 |
Predicate Information (identified by operation id):

2 - access("ID"=TO_NUMBER('abc'))

好玩吧，oracle直接将字符串'123'转成了数字123，而无法直接转的'abc',就将条件变成了"ID"=TO_NUMBER('abc')。

同时，列名没有被放在函数中，所以上面两条sql都走索引了。

总结一下:

1、当条件两边类型不匹配时，oracle会自动地、隐式地添加一个转换函数。比如等号两边分别是字符串和数值时，会把字符串转数值；

2、放在函数中的列名不会触发索引。

（本文发布于http://www.wowtools.org/blog，转载请注明出处/）