SQL 优化

Sql优化是一项复杂的工作，以下的一些基本原则是本人看书时所记录下来的，很明确且没什么废话：

1．索引的使用：

（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。

（2）.避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：

低效：select * from dept where sal*12 〉2500;

高效：select * from dept where sal〉2500/12;

(3).避免在索引列上使用not和 “!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=”时，就会停止使用索引而去执行全表扫描。

(4).索引列上〉=代替〉

低效：select * from emp where deptno 〉 3

高效：select * from emp where deptno 〉=4

两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。

(5).非要对一个使用函数的列启用索引，基于函数的索引是一个较好的方案。

2. 游标的使用：

当在海量的数据表中进行数据的删除、更新、插入操作时，用游标处理的效率是最慢的，但是游标又是必不可少的，所以正确使用游标十分重要：

(1). 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。

(2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，例如：

insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

ods_customer为数据源表。dim_customer为维表。

(3). 使用显式的游标，因为隐式的游标将会执行两次操作，第一次检索记录，第二次检查too many rows这个exception,而显式游标不执行第二次操作。

3．据抽取和上载时的sql优化：

(1). Where 子句中的连接顺序：

oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：

低效：select * from emp e where sal〉5000 and job = ‘manager’ and 25〈(select count (*) from emp where mgr=e.empno);

高效：select * from emp e where 25〈(select count(*) from emp where mgr=e.empno) and sal〉5000 and job=’manager’;

(2). 删除全表时，用truncate 替代 delete,同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml。

(3). 尽量多使用commit

只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。

(4). 用exists替代in ，可以提高查询的效率。

(5). 用not exists 替代 not in

(6). 优化group by

提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。如：

低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;

高效： select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;

(7). 有条件的使用union-all 替代 union：这样做排序就不必要了，效率会提高3到5倍。

(8). 分离表和索引

总是将你的表和索引建立在不同的表空间内，决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上。