sql performence

1)        尽可能使用%TYPE的方式声明变量和参数。虽然和性能无关，但是易于维护。

2)        尽量使用rowId访问数据表。例如：

低效	高效
select max(SEQ_NO) into vMaxSeqNo from EMP_TABLE; update EMP_TABLE set NAME = ivName where SEQ_NO = vMaxSeqNo	select max(SEQ_NO), ROW_ID into vMaxSeqNo, vRowId from EMP_TABLE; update EMP_TABLE set NAME = ivName where rowid = vRowId

 

3)        如果实现缓冲区SQL语句的共享，SQL语句必须完全的相同，包括大小写，空格和换行等，所以要求大家严格按照编码规范的一个原因。

4)        选择最有效率的表名顺序。ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为最右侧的表。那就需要使用交叉表，交叉表应该放在三个表中的最后。

例如 :       表 TAB1 16,384 条记录

表 TAB2     1 条记录

低效	高效
select count(*)   from tab1,tab2 执行时间0.96秒	select count(*) from tab2,tab1 执行时间26.09秒

5)        WHERE子句中的连接顺序。ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

低效	高效
select … from EMP E where SAL > 50000 and JOB = ‘MANAGER’ and (select COUNT(*) from EMP              where MGR=E.EMPNO) > 25;	select … from EMP E where (SELECT COUNT(*) FROM EMP              WHERE MGR=E.EMPNO) > 25 and SAL > 50000 and JOB = ‘MANAGER’;

 

6)        SELECT子句中避免使用‘*’。

7)        减少访问数据库的次数，减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

 

低效	高效
select EMP_NAME , SALARY , GRADE from EMP where EMP_NO = 342; select EMP_NAME , SALARY , GRADE from EMP where EMP_NO = 291;	select A.EMP_NAME , A.SALARY , A.GRADE,         B.EMP_NAME , B.SALARY , B.GRADE from EMP A,EMP B where A.EMP_NO = 342 and B.EMP_NO = 291;

 

8)        删除全表，用TRUNCATE替代DELETE。使用Truncate时，不记录日志。

9)        只要有可能,在程序中尽量多使用COMMIT。COMMIT所释放的资源包括：
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间

10)     使用表的别名(Alias)。当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

11)     用EXISTS替代IN，用NOT EXISTS替代NOT IN

低效	高效
select * from EMP (基础表) where EMPNO > 0 and DEPTNO IN ( select DEPTNO from DEPT where LOC = ‘MELB')	select * from EMP (基础表) where EMPNO > 0 and EXISTS ( select ‘X' from DEPT where DEPT.DEPTNO = EMP.DEPTNO)

 

低效	高效
select … from EMP where DEPT_NO NOT IN ( select DEPT_NO from DEPT where DEPT_CAT='A');	select … from EMP E where NOT EXISTS ( select ‘X' from DEPT D where D.DEPT_NO = E.DEPT_NO and DEPT_CAT = ‘A');

 

12)     用表连接替换EXISTS。通常来说 , 采用表连接的方式比EXISTS更有效率。

 

低效	高效
select ENAME from EMP E where EXISTS ( select ‘X' from DEPT where DEPT_NO = E.DEPT_NO  and DEPT_CAT = ‘A');	select ENAME from DEPT D,EMP E where E.DEPT_NO = D.DEPT_NO and DEPT_CAT = ‘A' ;

 

13)     如果需要，可以使用EXPLAIN PLAN分析SQL语句的执行效率。

14)     尽量避免在索引列上使用计算，或者在索引上使用函数，ORACLE将不使用索引而使用全表扫描．

低效	高效
select … from DEPT where SAL * 12 > 25000;	select … from DEPT where SAL > 25000/12;

 

15)     总是使用索引的第一个列。如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

16)     当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。尽量避免这种情况的发生。

17)     尽量避免使用外联接。