Oracle优化二

尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: 
COMMIT所释放的资源: 
A. 回滚段上用于恢复数据的信息. 
B. 被程序语句获得的锁 
C. redo log buffer 中的空间 
D. ORACLE为管理上述3种资源中的内部花费 
(注: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.

 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 
低效: 
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION!= ‘SYDNEY' 
AND REGION != ‘PERTH' 
高效 
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY' AND 

REGION != ‘PERTH' GROUP BY REGION 
(注: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中) 

使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,

就可以减少解析的时间并减少那些由Column歧义引起的语法错误. (注: Column歧义指的是由于SQL中不同的表具有相同的Column名,

当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 
低效: 
SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC ='DALLAS') 
高效: 
SELECT * FROM EMP WHERE EMPNO > 0 AND exists (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC ='DALLAS') 

用NOT EXISTS替代NOT IN 用表连接替换EXISTS

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).

 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 
SELECT * FROM EMP WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT WHERE LOC ='DALLAS'); 

为了提高效率.改写为: 
SELECT * FROM EMP E WHERE NOT EXISTS (SELECT ‘X' FROM DEPT D WHERE D.DEPTNO = E.DEPTNO AND LOC ='DALLAS');

用EXPLAIN PLAN 分析SQL

EXPLAIN PLAN 是一个很好的分析SQL语句的工具，在PL/SQL Developer中对应的是解释计划窗口,

它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,

使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称. 你需要按照从里到外,从上到下的次序解读分析的结果.

 EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描． 
低效： 
SELECT * FROM DEPT WHERE SAL * 12 > 25000; 
高效: 
SELECT * FROM DEPT WHERE SAL > 25000/12; 
这是一个非常实用的规则，请务必牢记 

用>=替代>

如果DEPTNO上有一个索引, 
高效: 
SELECT * FROM EMP WHERE DEPTNO >=4 
低效: 
SELECT * FROM EMP WHERE DEPTNO >3 
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录. 

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，

索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中． 
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , 

ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，

ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空!

因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引. 
低效: (索引失效) 
SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 
高效: (索引有效)
SELECT * FROM DEPARTMENT WHERE nvl(DEPT_CODE,0) >0;  --增加函数索引

用UNION-ALL 替换UNION ( 如果有可能的话)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 
如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

需要当心的WHERE子句

索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 
不使用索引: 
SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 
◆使用索引: 
SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0 and amount<0; 
不使用索引: 
SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; 
◆使用索引: 
SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX' AND ACCOUNT_TYPE=' A'; 
不使用索引: 
SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 >5000; 
◆使用索引: 
SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT > 5000-3000 ;