Oracle 优化

1.1. 尽量少用 SELECT *
尽量必免SELECT * 动态列语句，使用 SELECT COL1，COL2，COL3

1.2. 把DELETE改为TRUNCATE
进行全表或分区删除时，使用TRUNCATE。
因使用delete删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)。

1.3. 能用union all的地方，就不要使用union
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.下面的SQL可以用来查询排序的消耗量：
低效：
SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ‘31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ‘31-DEC-95’
高效:
SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ‘31-DEC-95’
UNION ALL
SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE =’31-DEC-95’

1.4. 避免对列的操作
任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
例：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢：
select * from record where substrb(CardNo,1,4)=’5378’(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where to_char(ActionTime,’yyyymmdd’)=’19991201’（10秒）
由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：
select * from record where CardNo like ‘5378%’（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where ActionTime= to_date (‘19991201’ ,’yyyymmdd’)（< 1秒）
差别是很明显的！

1.5. 避免不必要的类型转换
需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。
例：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：
select col1,col2 from tab1 where col1>10，
应该写为：
select col1,col2 from tab1 where col1>’10’。

1.6. 增加查询的范围限制
增加查询的范围限制，避免全范围的搜索。
例：以下查询表record 中时间ActionTime小于2001年3月1日的数据：
select * from record where ActionTime < to_date (‘20010301’ ,’yyyymm’)
查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。
修改如下：
select * from record where ActionTime < to_date (‘20010301’ ,’yyyymm’)
and ActionTime > to_date (‘20010101’ ,’yyyymm’)
后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把’20010301’换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

1.7. 尽量去掉”IN”、”OR”
含有”IN”、”OR”的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
例： select * from stuff where id_=’0’ or id=’1’
可以考虑将or子句分开：
select * from stuff where id_no=’0’
union all
select * from stuff where id_no=’1’
与原来的SQL语句相比，查询速度更快。

1.8. 尽量去掉 “<>”
尽量去掉 “<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为”OR”方式。
例:UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;
以上语句由于其中包含了”<>”，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉”<>”，利用索引来提高效率。
修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第8种方法。
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’

1.9. 避免在索引列上使用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 DEPT_CODE >=0;

1.10. 多张表进行关联时，适当使用/+ORDERED/的提示
4张以上的表（含4张）进行关联时，为了避免因为统计信息不准确造成ORACLE CBO执行计划错误，导致SQL卡壳，建议采用加 /+ORDERED/ 的HINT，并调整FROM后表的出现顺序，将小表放前面，大表放后面。

1.11. 用Case语句合并多重扫描
我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：
例：
1）select count(*) from emp where sal<1000;
2）select count(*) from emp where sal between 1000 and 5000;
3）select count(*) from emp where sal>5000;
这样我们需要进行三次全表查询，但是如果我们使用case语句：
Select count (case when sal <1000 then 1 else null end) as count_poor,
count (case when sal between 1000 and 5000 then 1 else null end) as count_blue_collar,
count (case when sal >5000 then 1 else null end) as count_poor
from emp;
这样查询的结果一样，但是执行计划只进行了一次全表查询。

1.12. LIKE操作符
LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。
例：用T_KHXX表中营业编号后面的户标识号可来查询营业编号 YYB ‘%5400%’ 这个条件会产生全表扫描，
优化方案1：如果改成YYB LIKE ’X5400%’ OR YYB ’B5400%’ 则会利用YYB的索引进行两个范围的查询，性能肯定大大提高。
优化方案2：可使用instr替换like
说明：SQLSERVER可用charindex代替

1.13. 用EXISTS替代IN，NOT EXISTS替代NOT IN
在一些基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率 。
说明：表数据量少时，可用IN、NOT IN

1.14. 对历史表的查询或报表，一定要用上日期索引或有按时间分区字段的条件
历史表的数据量往往非常巨大，如果对历史表的查询或报表，没有历史表没有索引或者有索引但是WHERE语句里没有包含索引字段的条件，将造成历史表的全表扫描，性能非常低下。因此，对于没有分区的历史表，应该建包含日期的索引，并在查询条件里指定日期范围。对于有分区的历史表，分区键应该是日期字段，并在查询条件里指定日期范围。

1.15. 避免使用count(*),可采用count（1）或count(id)
统计数据时应避免使用count(*)，可采用count(1)或count(id)等

1.16. Insert into…select id,name from…效率高于循环insert
Insert into…select id,name from…的效率高于循环insert

1.17. 选择最有效率的表名顺序
注：只在基于规则的优化器中有效
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

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

1.19. 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

1.20. 整合简单,无关联的数据库访问
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。

1.21. 删除重复记录
最高效的删除重复记录方法 ( 使用了ROWID)，例：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

1.22. 用Where子句替换HAVING子句
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。 如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。例：
低效：
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’
高效:
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’AND REGION != ‘PERTH’
GROUP BY REGION
（译者按： HAVING 中的条件一般用于对一些集合函数的比较，如COUNT（） 等等。 除此而外，一般的条件应该写在WHERE子句中）

1.23. 减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.例：
SELECT TAB_NAME FROM TABLES
WHERE (TAB_NAME,DB_VER) =
(SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

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

1.25. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例：
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E 　
WHERE D.DEPT_NO = E.DEPT_NO 　　
高效:
SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);　

1.26. 用>=替代>
高效：SELECT * FROM EMP WHERE DEPTNO >=4
低效：SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录

1.27. 用WHERE替代ORDER BY
ORDER BY 子句只在两种严格的条件下使用索引：
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空. 　　
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.　　
例：表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
非唯一性的索引(DEPT_TYPE)
低效：(索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效：(使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

1.28. 避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
低效：SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效：SELECT … FROM DEPT WHERE SAL > 25000/12;

1.29. 避免改变索引列的类型
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT … FROM EMP WHERE EMPNO =‘123’
实际上,经过ORACLE类型转换, 语句转化为:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123’)
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来.
注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

1.30. 需要当心的WHERE子句
某些SELECT 语句中的WHERE子句不使用索引，例：
(1)‘!=’ 将不使用索引。
记住：索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。
(2)‘||’是字符连接函数. 就象其他函数那样, 停用了索引。
(3)‘+’是数学函数. 就象其他数学函数那样, 停用了索引。
(4)相同的索引列不能互相比较，这将会启用全表扫描。

1.31. 优化GROUP BY
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。
低效：
SELECT JOB,AVG(SAL) FROM EMP
GROUP JOB HAVING JOB = ’ PRESIDENT ’ OR JOB = ’ MANAGER ’
高效：
SELECT JOB , AVG(SAL) FROM EMP
WHERE JOB = ‘PRESIDENT ’ OR JOB = ‘MANAGER ’ GROUP JOB

1.32. INSERT 加 /+APPEND/ 提示
对数据进行大批量写入且重做时有TRUNCATE机制，INSERT 都要加 /+APPEND/ 提示，能大幅度提高性能。
注意事项：
INSERT A表加 /+APPEND/ 提示后，INSERT之前必须COMMIT。INSERT之后如果要对A表进行增删改查操作前，也要COMMIT。
由于INSERT是在表的尾部进行追加记录，如果过程中没有TRUNCATE机制，那要尽量避免使用，防止表产生高水位。

1.33. 有些不经常变化的视图，可落地成实体表或物化视图
可将一些较复杂且时间较久的查询，落地成实体表，或都物化视图。

1.34. 分解复杂查询，用常量代替变量
对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。