高效的SQL （隐式转换导致不走索引）

1 、创建实验表doudou,索引创建在varchar2类型的id列上，索引名 DOUDOU_INDEX

doudou@TEST> select count(*) from doudou;

  COUNT(*)

----------

      1000

doudou@TEST> select index_name,table_owner,table_name,TABLE_TYPE, PCT_FREE,BUFFER_POOL,TEMPORARY from user_indexes where table_name='DOUDOU';

INDEX_NAME           TABLE_OWNE TABLE_NAME TABLE_TYPE                PCT_FREE BUFFER_POOL     TE

-------------------- ---------- ---------- ---------------------- ---------- -------------- --

DOUDOU_INDEX        DOUDOU      DOUDOU      TABLE                           10 DEFAULT         N

2 、开启执行计划，并执行2不同的SQL，并观察执行计划

doudou@TEST> set timing on

doudou@TEST> set autot on

doudou@TEST> select id from doudou where id=3;

ID

----------------------------------------

3

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 845489848

----------------------------------------------------------------------------

| Id   | Operation          | Name    | Rows   | Bytes | Cost (%CPU)| Time      |

----------------------------------------------------------------------------

|    0 | SELECT STATEMENT   |         |      1 |     12 |      3    (0)| 00:00:01 |

|*   1 |   TABLE ACCESS FULL| DOUDOU |      1 |     12 |      3    (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER("ID")=3)

【这里有to_number的转换，导致没有走索引，所以，编写高效的SQL也要注意索引列的类型，不要让数据库帮你隐式转换。隐式转换之后不走索引。】

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

        314   recursive calls

          0   db block gets

         58   consistent gets

          0   physical reads

          0   redo size

        404   bytes sent via SQL*Net to client

        400   bytes received via SQL*Net from client

          2   SQL*Net roundtrips to/from client

          6   sorts (memory)

          0   sorts (disk)

          1   rows processed

doudou@TEST> select id from doudou where id='3';

ID

----------------------------------------

3

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3961861220

---------------------------------------------------------------------------------

| Id   | Operation         | Name          | Rows   | Bytes | Cost (%CPU)| Time      |

---------------------------------------------------------------------------------

|    0 | SELECT STATEMENT |               |      1 |     12 |      1    (0)| 00:00:01 |

|*   1 |   INDEX RANGE SCAN| DOUDOU_INDEX |      1 |     12 |      1    (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("ID"='3')

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

          9   recursive calls

          0   db block gets

         14   consistent gets

          0   physical reads

          0   redo size

        404   bytes sent via SQL*Net to client

        400   bytes received via SQL*Net from client

          2   SQL*Net roundtrips to/from client

          0   sorts (memory)

          0   sorts (disk)

          1   rows processed

总结：

编写SQL的时，where语句中过滤条件中请注意索引列的类型，不要让oracle做隐式转换，因为隐式转换是不走索引的。

附表：