oracle sql性能优化

1、from 表位置顺序不正确。

规则：SQL解析是从右向左，数据量小的表放在右边。
例子：
(1)错误
select ac01.aac001 from ac01,ac02
where ac01.aac001=ac02.aac001;
(2)正确
select ac01.aac001 from ac02,ac01
where ac02.aac001=ac01.aac001;

2、where 条件没有正确使用索引。

规则1：where条件语句顺序应符合索引顺序
例子
ac02表索引aac001,aab001,aae140,aae041
(1)错误
select aac001 from ac02
where aae140='31'
  and aac001='1000001'
  and aab001='2000'
  and aae041=sysdate;
(2)正确
select aac001 from ac02
where aac001='1000001'
  and aab001='2000'
  and aae140='31'
  and aae041=sysdate;

规则2：对于复合索引where条件语句必须包含复合索引的第一个列
例子
ac02表索引aac001,aab001,aae140,aae041
(1)错误
select aac001 from ac02
where aab001='2000'
  and aae140='31'
  and aae041=sysdate;
(2)正确
select aac001 from ac02
where aac001='1000001'
  and aab001='2000'
  and aae140='31';

规则3：对于复合索引字段超过3个以上，只使用前1个或2个可能
不会使用索引。尽可能使用复合索引的字段才可能使用索引。
例子
ac02表索引aac001,aab001,aae140,aae041
(1)错误
select aac001 from ac02
where aac001='1000001';
(2)正确
select aac001 from ac02
where aac001='1000001'
  and aab001='2000'
  and aae140='31';

3、尽量不要使用order by
规则：没有必要排序的select查询语句，不用使用order by

4、尽量不要使用group by
规则：没有必要进行分组查询的语句，不用使用group by

5、不要使用 *
规则：使用*，oracle解释sql时，需要进行解析，浪费时间。

例子1
(1)错误
select * from ac02
where aac001='1000001';
(2)正确
select aab001,aac001,aae140,aae041,
       aae042,aac031,aac037,aac130,
       bac014,aae011,aae036,aae051
  from ac02
where aac001='1000001';

例子2
(1)错误
select count(*) from ac02
where aac001='1000001';
(2)正确
select count(1) from ac02
where aac001='1000001';

6、不能使用not in和<>
规则：用NOT EXISTS替代NOT IN
例子
查询没有参加医疗的人员基本信息
(1)错误
select aac001,aac003 from ac01
where aac001 not in (select aac001 from ac02
                     where aae140='31');
(2)正确
select aac001,aac003 from ac01
where not exists (select 1 from ac02
                     where aac001=ac01.aac001 and aae140='31');

7、访问数据尽量不要多次访问数据库
规则：尽可能一次访问数据库
例子
处理在职和退休人员业务
(1)错误
cursor cur_ac01_zz is
select aac001,aac008,aab001 from ac01 where aab001='1111' and aac008='1';
cursor cur_ac01_tx is
select aac001,aac008,aab001 from ac01 where aab001='1111' and aac008='2';

.......

for rec_ac01_zz in cur_ac01_zz loop
    在职业务处理
end loop;
for rec_ac01_tx in cur_ac01_tx loop
    退休业务处理
end loop;
(2)正确
cursor cur_ac01 is
select aac001,aac008,aab001 from ac01 where aab001='1111' and aac008 in ('1','2');

.......

for rec_ac01 in cur_ac01 loop
    if rec_ac01.aac008=在职 then
       在职业务处理
    end if;
    if rec_ac01.aac008=退休 then
       退休业务处理
    end if;
end loop;

8、用EXISTS替代IN的子查询条件语句提供查询速度
规则：用EXISTS替代IN的子查询条件语句
例子：
查询参加医疗的人员基本信息
使用in
select aac001,aac002 from ac01 where aac001 in (select aac001 from ac02 where aae140='31');
使用exists代替in
select aac001,aac002 from ac01 where exists (select 1 from ac02 where aac001=ac01.aac001 and aae140='31');

9、用表连接替换EXISTS效果更好
接上个例子
使用exists
select aac001,aac002 from ac01 where exists (select 1 from ac02 where aac001=ac01.aac001 and aae140='31');
表关联
select a.aac001,a.aac002 from ac02 b,ac01 a where a.aac001=b.aac002 and b.aae140='31';

10、避免在索引列和结果列上使用计算
例子1(条件列上带计算)
查询养老视同缴费年限为16年的信息，15年是参数常量
(1)错误
select aac001 from ac01 where aic001 + 1 = 15;
(2)正确
select aac001 from ac01 where aic001 = 15 + 1;

例子2(结果列上带计算)
查询编号为'11111'的人员养老视同缴费年限（数据库数据是月数）的年数。
(1)错误
select aic001/12 into n_aic001 from ac01 where aac001='11111';
(2)正确
select aic001 into n_aic001 from ac01 where aac001='11111';
n_aic001 = n_aic001 /12

11、避免在条件中使用NOT包括（not between）
例子
(1)错误
select aac001 from ac01 where not aac008='1';
(2)正确
select aac001 from ac01 where aac008 in ('2','3');

12、索引使用的补充说明
下面情况不会用到索引
(1)索引列上使用IS NULL和IS NOT NULL
(2)索引列对应变量和字段类型不一致
(3)索引列这样使用like，例如：select 1 from ac01 where aac001 like '%0001';
复合索引的建立以及where子句的条件顺序需要符合oracle是由下往上进行分析规则,
可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
例如:

(低效,执行时间156.3秒)

SELECT …

FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER'

AND 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO);

(高效,执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL > 50000

AND JOB = ‘MANAGER';

 

13、禁止游标和循环内有select和update语句，或游标嵌游标（循环）。
例子
处理工资信息
(1)错误
cursor cur_ac01 is
select aac001 from ac01 where aab001='1111';

for rec_ac01 in cur_ac01 loop
    select aac040 into n_aac040 from ac04 where aac001=rec_ac01.aac001;
    工资业务处理
end loop;
(2)正确
cursor cur_ac04 is
select a.aac001,b.aac040 from ac04 a,ac01 b where a.aab001=b.aab001 and b.aab001='1111';

for rec_ac04 in cur_ac04 loop
    工资业务处理
end loop;

14、sql语句字段大小写要统一
规则：一个sql语句字段不能大小写混用，我们基本统一用小写。

15、union和union all
规则：不用使用union，要使用union all，效率差很多。