有索引，却不走索引的情况

今天时间不多，主要在写开发规范，一些业务sql也不能贴上来，只能简略的写一下
编写SQL时，在查询列上使用函数、在查询列上做运算、传入类型不一致的参数等都有可能导致隐式转换的发生。发生隐式转换可能会导致索引无法被使用，还会消耗不必要的资源，使得SQL效率低下。

因此，为了避免隐式转换，在编写SQL时应注意以下几点：

1.1      不能在查询列使用函数

在查询列使用函数，会使索引无法被使用。

SQL> select empno from emp where to_char(hiredate,'yyyymmdd')>'20160101';

Execution Plan

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

Plan hash value: 3956160932

 

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

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

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

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

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

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

可以发现，在查询列hiredate上嵌入了to_char函数，使得索引无法被使用。

在编写SQL时，要把需要转换的函数放到查询列右边，如下：

SQL> select empno from emp where hiredate>to_date('20160101','yyyymmdd');

Execution Plan

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

Plan hash value: 4079916893

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

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

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |     1 |    12 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

1.2      不能在查询列嵌入表达式

在查询列做运算，会使索引无法被使用。

SQL>  select empno from emp where hiredate+1>sysdate;

Execution Plan

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

Plan hash value: 3956160932

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

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

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

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

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

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

如上，在查询列上做运算会导致索引无法被使用，所以，在编写SQL时，不能在查询列上做运算，该SQL如下编写即可：

SQL> select empno from emp where hiredate>sysdate-1;

Execution Plan

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

Plan hash value: 4079916893

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

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

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |     1 |    12 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

1.3      传入类型一致的参数

传入类型不一致的参数会发生隐式转换，可能会导致索引无法被使用。

如下：

        WHERE B.ITM_NO LIKE '%%'

             AND B.ITM_CNM LIKE '%%'

             AND A.AC_DT BETWEEN 20160226 AND 20160305

ac_dt列上是存在索引的，该字段为varcahr2类型，但是在编写SQL的时候没有在参数两遍加上引号，导致了隐式转换的发生，使得索引没有被用到。

应该传入与字段类型一致的参数，对该sql进行如下修改：

AND A.AC_DT BETWEEN '20160226' AND '20160305'

1.4      合理设计字段类型

1.4.1       根据业务以及开发规则编写SQL

  AND SUBSTR(MERC_JRN_NO, 1, 1) = '-'

  AND ORDER_TYPE = '1'

merc_jrn_no列为number类型，按照SQL中的条件来看是想查询负数，但是这样编写SQL，不仅导致了隐式转换的发生，还有可能会查询不到想要的结果。如下：

SQL> select dump(-100) from dual;

DUMP(-100)

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

Typ=2 Len=3: 61,100,102

Number类型在Oracle中的存储如上所示，负号在前只是负数的显示方式之一。

SQL> select to_char(-100,'99999MI') from dual;

TO_CHA

------

  100-

当负号在后面时，SUBSTR(MERC_JRN_NO, 1, 1) = '-'就会查询不到想要的结果。

如果要查询负数只需如下编写即可：

and MERC_JRN_NO<0

1.4.2       合理设计字段类型

SELECT P.*

FROM URMTFUNID P, URMTMODID M

WHERE P.MODULE_ID = M.MODULE_ID

    AND P.STATUS = 0

    AND M.MODULE_ID = :1

    AND (P.IS_COMP_FUN = '0'

        OR P.IS_COMP_FUN = :2)

    AND P.FUNCTION_TYPE = '0'

    AND FUNCTION_ID LIKE '3%'

    AND P.FUN_TYP = '00'

ORDER BY P.FUN_SEQ_NUM ASC

function_id为number类型，但是编写SQL时用了like，这会导致隐式转换的发生，使得索引无法被使用，如果要以某个数字或者某几个数字开头去代表一些业务含义，那么这个字段应当设计为varchar2类型。

同时，外键列字段类型应与主表保持一致，需要进行关联的表，相关的关联字段也应设计为统一类型。

5，统计信息问题
6，返回行数过多，cost问题
7，like ‘％％’
8,使用整个索引时，索引中存在null，因为null是不进入索引等

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2054994/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2054994/