隐式类型转换导致索引列不走索引

请记住：Oracle一般都会根据统计信息生成相对高效的执行计划，如果没有，那肯定是有理由的。不要慌乱，需要仔细分析原因。

测试数据：
SQL> select count(*) from t2;

 COUNT(*)
----------
    106688

SQL> create index i_t2_objectid on t2(object_id);

索引已创建。
SQL> analyze table t2 compute statistics for table for all indexes for all indexed columns;

表已分析。

SQL> desc t2;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          VARCHAR2(100)
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> set autot trace

varchar没有number的优先级高，需要先将varchar隐式类型转换成number，再和20进行比较。
隐式类型转换导致object_id字段不走索引。
SQL> select * from t2 where object_id=20;

已选择8行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   744 |   411   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T2   |     8 |   744 |   411   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("OBJECT_ID")=20)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1465  consistent gets
          0  physical reads
          0  redo size
       1536  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL> /

已选择8行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   744 |   411   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T2   |     8 |   744 |   411   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("OBJECT_ID")=20)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1465  consistent gets
          0  physical reads
          0  redo size
       1536  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

将20显示转换成字符类型，object_id字段无需类型转换，用到索引。逻辑读为11，比全表扫描时1465下降的比例很大。
SQL> select * from t2 where object_id='20';

已选择8行。

执行计划
----------------------------------------------------------
Plan hash value: 1119120461

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     8 |   744 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2            |     8 |   744 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T2_OBJECTID |     8 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"='20')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1980  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL> /

已选择8行。

执行计划
----------------------------------------------------------
Plan hash value: 1119120461

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     8 |   744 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2            |     8 |   744 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T2_OBJECTID |     8 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"='20')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1980  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL>

   另外，索引列使用函数（除非建了索引函数）、表达式、绑定变量窥测（由于执行计划共享会使用第一个传入值生成的执行计划，如果第一个生成的执行计划不使用索引，则之后就算传入值使用索引会更高效，也不会使用索引）、统计信息老旧等原因也会导致索引列不会用到索引，也需要注意可能根据索引列查询出的数据量较大，Oracle判断不走索引可能会高效也会忽略索引。在优化过程中，如果碰到索引列没有走索引，不要慌乱，Oracle对索引列不走索引都是有依据的，需要按实际情况进行分析。