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

高效的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做隐式转换，因为隐式转换是不走索引的。

 

附表:

实验环境 sys@TEST>  select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE    10.2.0.4.0      Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 – Production sys@TEST> show parameter optimizer_mode NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- -------------------------- optimizer_mode                       string                 ALL_ROWS