索引应用建议

1、WHERE子句中尽量不用OR

使用了OR之后，有的情况下(涉及到索引的扫描方式)会使对索引的检索变成无效，从而降低检索的性能，这这种情况下，可以考虑是否可以用UNION或IN来代替。 RBO（基于规则）下，OR就像UNION ALL文的展开一样，在有外部结合、CONNECT BY句的情况下就不能被执行。CBO（基于成本）下，OR根据各结合顺序的基础结合成本，在再估算阶段，根据成本对INLIST和OR文能不能再扩展进行判断。 错1：SELECT name FROM emp WHERE deptno = 99 OR deptno = 999; 对1：SELECT name FROM emp WHERE deptno = 99  UNION ALL  SELECT name FROM emp WHERE deptno = 999; 错2：~ FROM emp WHERE deptno = 10 OR deptno = 20 OR teptno = 30; 对2：~ FROM emp WHERE deptno IN(10,20,30);

2、WHERE子句中尽量不用LIKE ‘%c%’, LIKE ’%c’

在有索引的列上使用LIKE ‘%c%’, LIKE ’%c’之后，索引就会失效，因而性能也会下降。

错：~ WHERE name LIKE ‘%c%’; 错：~ WHERE name LIKE ’%c’;

对：~ WHERE name LIKE ’ c%’;

3、WHERE子句中尽量不用NOT

在有索引的列上使用NOT、!=、 <>之后，索引检索就会失效，因而性能也会下降。

例1：job列大部分值为NULL或’SALESMAN’，并且job列上建有B*Tree索引。

错：~ WHERE job != ‘SALESMAN’

对：~ WHERE job > ‘SALESMAN’ OR job < ‘SALESMAN’

例2：dname为建有B*Tree索引的文字列，并且基数比较少（值比较少，譬如性别列，只有男和女两个值），且不经常被更新。

错：~ WHERE dname IS NOT NULL;

对：DROP INDEX btree_dname_idx;  CREATE BITMAP INDEX bitmap_dname_idx ON emp(dname);  

SELECT ~ WHERE dname IS NOT NULL;

4、WHERE子句中尽量不用IS NULL, IS NOT NULL

在用了IS NULL、IS NOT NULL 之后，对应检索列的B*Tree索引就会失去索引功能，从而性能会大幅下降。

在使用IS NULL的时候也应该考虑一下是否真的需要NULL值。IS NOT NULL在CBO下统计情报取得的情况下，索引的检索也有可能被使用（参照IS NOT NULL使用的补充）。

即使使用IS NULL、IS NOT NULL，BITMAP索引还是照样能被使用。

错：Hiredate列为日期型，在hiredate列上建有B*Tree索引，并且’9999-12-31’是系统中不可能出现的日期，执行下面的SQL SELECT ~ WHERE hiredate IS NULL;

对：CREATE INDEX function_hiredate_idx ON emp(NVL(hiredate, TO_DATE(‘9999-12-31’))); SELECT ~ WHERE NVL(hiredate, TO_DATE(‘9999-12-31’)) = TO_DATE(‘9999-12-31’, ‘YYYYMMDD’);

5、在WHERE中慎用<>,!=

记住索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。 在下面的例子中‘!=' 将不使用索引。

不使用索引：SELECT account_name FROM transaction WHERE amout != 0;

使用索引：SELECT account_name FROM transaction WHERE amout > 0;

6、WHERE子句中IS NOT NULL使用的补充

在NULL值比率较低的列上使用IS NOT NULL，会使对B*Tree索引变得无效，从而降低检索性能，相反，在NULL值比率相当高的列上使用IS NOT NULL，B*Tree索引会使用全表扫描(full scan)从而地高性能。（这是在CBO中统计情报已经取得的条件下）

例：Comm为建有B*Tree索引的数值型列，且NULL值比率相当高（CBO并且统计情报已经取得）

错：~ WHERE comm >= 0 OR comm < 0;

对：~ WHERE comm IS NOT NULL;

尽量用EXISTS代替 DISTINCT

DEPT表和EMP表是一对多的关系，根据EMP表从DEPT表中取出纪录，这种情况要避免使用DISTINCT，因为用DISTINCT后满足条件的所有纪录将都会被检索、排序、重复行删除，从而影响了性能。取而代之的可以用EXSITS子查询，EXSITS子查询当有一件符合条件的纪录存在时马上就返回不处理剩下的纪录，因而速度很快。

错：SELECT DISTINCT d.dept_code, d.dept_name FROM dept d, emp e WHERE e.dept_code = d.dept_code;

对：SELECT d.dept_code, d.dept_name FROM dept d WHERE EXSITS(SELECT ‘X’ FROM emp e WHERE e.dept_code = d.dept_code);

7、尽量用EXISTS代替DISTINCT

DEPT表和EMP表是一对多的关系，根据EMP表从DEPT表中取出纪录，这种情况要避免使用DISTINCT，因为用DISTINCT后满足条件的所有纪录将都会被检索、排序、重复行删除，从而影响了性能。取而代之的可以用EXSITS子查询，EXSITS子查询当有一件符合条件的纪录存在时马上就返回不处理剩下的纪录，因而速度很快。

错：SELECT DISTINCT d.dept_code, d.dept_name FROM dept d, emp e WHERE e.dept_code = d.dept_code;

对：SELECT d.dept_code, d.dept_name FROM dept d WHERE EXSITS(SELECT ‘X’ FROM emp e WHERE e.dept_code = d.dept_code);

8、ORDER BY使用上的注意点

如果想要ORDER BY句的排序用到索引，就必须满足下面的两个条件

1）、ORDER BY句中的列顺序要完全包含在同一顺序的一个复合索引中

2）、ORDER BY句中的列要全部在表中定义为NOT NULL列

9、Index列尽量不参加计算

检索条件中索引列被参与计算，或被用作函数的参数，那么就会失去该列的索引功能，从而导致性能急剧下降。 可以通过建函数索引的方法，计算结果或函数值事前计算好作为所引来用。

错：~ WHERE sal*1.1 > 950;

对：~ WHERE sal > 950/1.1;

错：~ WHERE name || type =’XXXY’ ;

对：~ WHERE name = ‘XXX’ AND type = ’Y’ ;

错：~ WHERE TO_CHAR(hiredate, ‘YYYYMMDD’) = ‘20100722’;

对：~ WHERE hiredate = TO_DATE( ‘20100722’ , ‘YYYYMMDD’) ;

错：~ WHERE SUBSTR(name, 1, 7) = ‘CAPTIAL’;

对：~ WHERE name LIKE ‘CAPTIAL%’;

错：~ WHERE TRUNC(trans_date) = TRUNC(SYSDATE);

对：~ WHERE trans_date BETWEEN TRUNC(SYSDATE) AND TURNC(SYSDATE) + .99999 注：在日期上加上超过5位小数的数是，日期就自动变为下一日的日期。

TO_DATE(‘2010-7-22’)+.99999 &#61664; ‘2010-7-22 23:59:59’

TO_DATE(‘2010-7-22’)+.999999 &#61664; ‘2010-7-23 00:00:00’

10、复合Index中前面的列应在条件中指定

在使用复合索引时，复合索引中前面的列要在条件中指定。

如果前面列在条件中没指定的情况下，虽然通过索引的SKIP SCAN机能有可能会使用索引，但是一定要确认索引是否真的被使用。 (depno, job)列顺序的复合索引

对：~ WHERE depno = 20 AN job = ‘MANAGER’;

对：~ WHERE job = ‘MANAGER’ AND depno = 20;

对：~ WHERE depno = 20; //索引前方部分使用

对：~ WHERE job = ‘MANAGER’; //这种情况下，一定要确认索引是否被使用。