类型转换导致执行计划不走索引测试案例，执行计划索引
测试环境模拟：
SQL> drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonlydrop table t_col_type purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t_col_type where id=6;
Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T_COL_TYPE | 1 | 36 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=6)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这里好像有点奇怪啊，明明建了index [create index idx_id on t_col_type(id);]但是为啥没有用到呢？
---查看表上列是否有索引
SQL> select index_name , table_name,column_name from all_ind_columns where table_name ='T_COL_TYPE';
INDEX_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
IDX_ID
T_COL_TYPE
ID
----查看表结构
SQL> desc scott.T_COL_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(20)----------注意这里的字符类型
COL2 VARCHAR2(20)
COL3 VARCHAR2(20)
再次关注下 执行计划中的谓语信息：
1 - filter(TO_NUMBER("ID")=6) ----------这里发生了类型转换
所以在执行计划中就无法用已有的索引，那么如何才能让他正确走索引呢？
select * from t_col_type where id='6';------注意下这里的区别加了单引号，表明这是个字符，
Execution Plan
----------------------------------------------------------
Plan hash value: 3998173245
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='6')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
39 consistent gets
1 physical reads
0 redo size
640 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
额，我对你上面的话的理解是：每次进行筛选的时候，一定要避免隐式转换。在索引字段上使用函数，或者其他的转换都会导致索引不可用，而你说的优先转换字符类型到数值类型，假如你进行筛选的字段是字符类型，那Oracle绝对不会给你转成数值类型，你的那句话应该是相对于其他类型来说的吧我认为，比如date类型之类的。所以我认为两个应该都是对的，只是说的是两个不同方面的规则吧。
我做过一个项目，用两张有3千万的表进行join，结果发现速度慢得受不了，该加的索引都加了，用执行计划看了，发现索引用不到，问题也就出在隐式转换身上。所以，第一句是对的，而第二句其实我也不是很确定哪个优先，从表面上看是看不出来的，要了解到Oracle内部的转换机制。。。小小意见，一起探讨探讨。。。
1. 首先你要明白索引的存储结构, 这些都是使用索引的一些技巧, 这些技巧对于任何索引都是通用的，索引是占用物理存储的与基表具有逻辑影射的一组内容，索引的存储一般都是有序的，如果你要比较同一个表的两个索引(a,b)，执行计划就会选择a[1]和b[1..n] 所有的记录比较, 然后接着a[2]和b[1...n]比较的次数为笛卡儿积, 相当于 C中的两次FOR循环
for(i=0;i<=n;i++)
for(j=0;j<=n;j++)
{ if(a[i]>b[j])
then "this record should be put into memory"
}
你知道, 索引和表的记录在oracle中是根据他的二元高度确定cost的, 二元高度越高就证明要经过的I/O次数越多, 执行计划就越差, 如果索引记录和基表记录也不在同一个块中，那么更会增加需要query的时间, 所以一般这种情况oracle的执行计划会选择全表扫描来的更快一点。
2. 你说的对, 但是在这里，oracle的内部转换不会根据优先级进行，
比如说 WHERE Ename = 123
但是Ename 是varchar类型, 这个时候就会内部转换为
WHERE TO_NUMBER(ENAME) = 123
记住，在oracle 中,所有字符均被转换为大写, 一般oracle 认为你要检索的条件是"神圣的", 所以一般只转换索引的列进行(本人见解, 不同意见者, 欢迎拍砖)