测试环境模拟：
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 levelcommit;
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