SQL优化
1. SELECT子句中避免使用 ‘ * ‘
结论：
Select * from table 会通过查询数据字典完成所有Column的转换。
2. Truncate Table & Delete Table.
结论：
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果全表删除,用TRUNCATE时, 回滚段不再存放任何可被恢复的信息，因此很少的资源被调用,执行时间也会很短
3. 多表查询时的别名
结论：
减少解析的时间并减少那些由字段歧义引起的语法错误
4. 用EXISTS替代IN，用NOT EXISTS替代NOT IN；采用表连接的方式比EXISTS更有效率
低效:
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
AND LOC = 'MELB')
5. 用表连接替换EXISTS
/* 低效 */
SELECT LN.*
FROM edi_ord_ln LN
WHERE EXISTS (SELECT rodorderno
FROM edi_ord_hd
WHERE rodorderno = LN.rodorderno);
/* 高效*/
SELECT LN.*
FROM edi_ord_ln LN, edi_ord_hd hd
WHERE LN.rodorderno = hd.rodorderno;
结论：在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP
6. 尽量多使用COMMIT
Commit可以释放用于（1）恢复回滚段上的数据信息（2）被程序语句获得的锁（3）redo log buffer中的空间
在使用COMMIT时必须要注意到事务的完整性
7. Where中数据转换函数及公式放右边
SELECT *
FROM tibcoeai.sap_so_hd saphd, edi_ord_ln LN
WHERE saphd.zzrods =;
SELECT *
FROM tibcoeai.sap_so_hd saphd, edi_ord_ln LN
WHERE TO_CHAR (LN.rodorderno) = saphd.zzrods;
select * from edi_ord_ln where orderqty*10>100;
select * from edi_ord_ln where orderqty> 100/10;
8. 等式和比较范围
select * from edi_ord_ln
where
MFGSITE='WCZ521'
and orderqty >2;
select * from edi_ord_ln
where
orderqty >2
and MFGSITE='WCZ521';
9. >=和>比较
低效：
select * from edi_ord_ln
where
orderqty >2;
高效：
select * from edi_ord_ln
where
orderqty >=3;
结论：
前者DBMS先定位到OrderQty=2记录，然后再向前扫描第一个OrderQty>2的记录。
后者，DBMS直接跑到OrderQty=3的记录。
10. 避免索引列使用 != 和|| 操作
!=会引起全表检索。
索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.
11. SQL的书写顺序（Based on Rule）
From子句是按从右到左的顺序执行，如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。从效率上讲，小表应该作外表(驱动表)，大表应该作内表，即大表查询时走索引。即驱动表(Driving table, 通常以全表扫描的方式被访问)放最右边。
注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。
Where子句是按从下到上的顺序执行，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
SELECT LN.cancelledflag, LN.unitprice
FROM edi_ord_hd hd, edi_ord_ln LN
WHERE hd.rodorderno = LN.rodorderno
AND LN.ctoitem = 'Y'
AND hd.customer = '200383'
12. 存储过程中需要用到连接多个表时尽量采用oracle语法，避免pl/sql语法
/* Formatted on 2009/11/19 08:56 (Formatter Plus v4.8.7) */
SELECT SUM (CASE
WHEN LN.cancelledflag = 'N'
THEN (LN.orderqty * LN.unitprice)
ELSE 0
END
) AS total
FROM edi_ord_hd hd LEFT JOIN edi_ord_ln LN
ON hd.rodorderno = LN.rodorderno
AND hd.customer = '200383'
AND LN.ctoitem = 'Y';
/* Formatted on 2009/11/19 09:00 (Formatter Plus v4.8.7) */
SELECT SUM (DECODE (LN.cancelledflag, 'N', LN.orderqty * LN.unitprice, 0)
) AS total
FROM edi_ord_hd hd, edi_ord_ln LN
WHERE hd.rodorderno(+) = LN.rodorderno AND hd.customer = '200383'
AND LN.ctoitem = 'Y';
Explain Tool Compare:
结论：
(1)避免oracle将left join再重新解析；
(2)使用decode比使用case的效率要高，使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表；
10. 减少对表的查询
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
低效:
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效:
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
11.通过内部函数提高SQL效率.
