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