Oracle SQL语句编写常用优化方法及注意事项总结

序	优化方法	Oracle解析原理
1	SQL语句尽量使用大写	oracle总是先解析SQL语句，把小写的字母转换成大写的再执行
2	使用表的别名	当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。
3	指定查询列名,不使用*	ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
4	避免使用前置通配符	select name from tb where name like ‘%1001’;
5	WHERE子句中的连接顺序	ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
6	选择最有效率的表名顺序 (只在基于规则的优化器(RBO)中有效)	ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理，在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
7	删除重复记录	DELETE FROM TEMP E  WHERE E.ROWID >     (SELECT MIN(X.ROWID) FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO);
8	在含有子查询的SQL语句中,要特别注意减少对表的查询	SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) =     (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);
9	若不考虑删除重复行，使用Union all替代Union	Union：对两个结果集进行并集操作，不包括重复行并进行默认规则的排序(所以效率低) Union All：对两个结果集进行并集操作，包括重复行，不进行排序(所以效率高)
10	当SQL包含一对多查询时,使用EXIST替换DISTINCT	因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果
		低效：SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E         WHERE D.USER_ID= E.USER_ID;
		高效：SELECT USER_ID,BILL_ID FROM USER_TAB1 D         WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);
11	尽量用Where子句替换HAVING子句	HAVING子句只会在检索出所有记录之后才对结果集进行过滤，所以最好能通过WHERE子句限制记录的数目。顺序 Where >Group >Having
12	提高GROUP BY 语句的效率,可以通过将不需要的记录在GROUP BY之前过滤掉	低效：SELECT JOB,AVG(AGE) FROM TEMP          GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';
		高效：SELECT JOB,AVG(AGE) FROM EMP WHERE JOB = 'STUDENT' OR JOB = 'MANAGER'          GROUP BY JOB;
13	删除全表时，用TRUNCATE替代DELETE	当删除表中的记录时,通常 回滚段(rollback segments)用来存放可以被恢复的信息。如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。
14	用EXISTS替代IN、用NOT EXISTS替代 NOT IN	在子查询中,NOT IN子句将执行一个内部的排序和合并(全表遍历)，比较低效
		低效：SELECT A.* FROM TEMP(基础表) A WHERE         A.ID IN(SELECT ID FROM TEMP1);
		高效：SELECT A.* FROM TEMP(基础表) A WHERE         EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID=TEMP1.ID);
15	用>=替代>	低效：SELECT * FROM TEMP WHERE ID >3;
		高效：SELECT * FROM TEMP WHERE ID >=4;
16	用UNION替换OR(只适用于索引列)	用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。
		低效：SELECT * FROM USER_TAB1 WHERE USER_ID = 10 OR AGE = 20;
		高效：SELECT * FROM USER_TAB1 WHERE USER_ID = 10   UNION SELECT * FROM USER_TAB1 WHERE AGE = 20;
17	使用DECODE减少处理事件	低效：select count(*) from 表 where 性别 ＝ 男；      select count(*) from 表 where 性别 ＝ 女；
		高效：select sum(decode(性别，男，1，0))，sum(decode(性别，女，1，0)) from 表
18	利用外部连接"+"替代效率低下的not in运算	低效：select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE')
		高效：select a.empno from emp a,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE'
19	避免在索引列上使用NOT	NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT,就会停止使用索引转而执行全表扫描。
20	避免在索引列上使用IS NULL和IS NOT NULL	避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。
21	避免在索引列上使用函数计算	WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
		低效：SELECT … FROM TEMP WHERE SAL * 12 > 25000;
		高效：SELECT … FROM TEMP WHERE SAL > 25000/12;
22	总是使用索引的第一个列	如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
23	避免改变索引列的类型 (明确根据字段类型指定数据值)	自动转换：SELECT … FROM EMP WHERE EMP_TYPE = 123
		显示转换：SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
24	尽量多使用COMMIT	尽量多使用COMMIT, 这样程序的性能得到提高,COMMIT及时释放资源、解锁等等
25	避免在order by子句中使用表达式	order by 语句都会降低查询速度,可以考虑使用的列建立一个索引