oracle - SQL优化

 优化规则：

1.CBO模式下，表从右到左查询，右边第一个为基础表
选择行数的少作为基础表，
三个及三个以上的表，需要选择交叉表(连接另外两个表的表)作为基础表，

2.数据量大的时候，使用exists 而非 in

3.where
自下而上的解析where子句，
过滤数据记录的条件写在where字句的
尾部，以便在过滤了数据之后在进行表连接处理(即 a.name='张三'的写后面，再让a表中字段name为'张三'的数据去跟b表关联)
select * from table_name a,ccc b
where a.column =b.column
and a.deptno in(20,30)

and a.name='张三'

4.使用decode

语句块使用短路径，if( aa > 100) or (bb = 'cc'),
第一个对了，就不会走进第二个，
if( 1 = 2) and ( a in(1,2,3,4,5))
如果第一个就不满足的话，也不会走进第二个
所以将开销较低的放前面

5.避免隐式类型的转换，把pls_integer复制给number时，会出现隐式转换

 比较方法：

--查看对应行数语句块的性能

1.还有一个是按F5键查看执行计划

名词解释：

Cost：   操作消耗的成本（根据uses   disk   I/O,   CPU   usage,   memory   usage   等计算出来）
cardinality   操作访问的数据行数
Bytes     操作访问的数据集的大小。

2.一个是下面的方法
declare
  -- Local variables here
  v_run_number integer;
   p_contractid contract.contractid%type;
begin
  --启动profiler
  dbms_profiler.start_profiler(run_number => v_run_number);
  --显示当前跟踪的运行行号
  dbms_output.put_line('run_number:' || v_run_number);
  --运行要跟踪的PLSQL
  for aa in (select * from contract where rownum < 100) loop
   p_contractid:=aa.contractid;
  end loop;
  --停止profiler
  dbms_profiler.stop_profiler;
end;

 

 1.子查询与连接查询比较

大量数据子查询时：

 大量数据连接查询时：

 

--子查询

 1 --有子查询
 2 select a.contractno       保单号码,
 3       (select  chnname from syscode where recordid =a.contractstatus)          状态,
 4        a.acceptdate       受理日期,
 5        a.effectivedate    生效日期,
 6        a.approvedate      承保日期,
 7        a.policyreturndate 回单日期,
 8        a.surrenderdate    退保日期,
 9        b.checkyearmonth   对账日期,
10        max(b.policyyear)       年度,
11        b.productname      险种名称,
12        b.premiumperiod    年期,
13        (select abbrname from branch where branchid =b.bizcbranchid) 督导区,
14        (select abbrname from branch where branchid=b.bizubranchid )   营业部,
15        b.applicantname    投保人,
16        b.insuredname      被保险人,
17        a.AGENTNAME        代理人,
18        b.modalprem        保费,
19        b.valueprem        价值保费
20   from contract a, premium b
21  where  a.contractid = b.contractid
22    and a.providerid in ('PRO0000000000006', 'PRO00000000000G6')
23    and a.bizbranchid = 'BRA0000000000006'
24    and a.channeltype = 'CHANNELTYPE_A'
25     group by a.contractno,
26           a.acceptdate,
27           a.effectivedate,
28           a.approvedate,
29           a.policyreturndate,
30           surrenderdate,
31           checkyearmonth,
32           productname,
33           b.premiumperiod,
34           b.applicantname,
35           b.insuredname,
36           a.AGENTNAME,
37           modalprem,
38           a.contractstatus,
39           b.bizcbranchid,
40           b.bizubranchid,
41           b.valueprem 
42    
43    --select a.providerid,a.providername from provider a where providername like '%信泰%'
44    --select * from syscode where recordid like '%CHANNEL%'

结果集都是14678条，花费的三次时间依次为 27.627 26.582 26.692

--连接查询

 1 --全部是连接查询
 2 select a.contractno       保单号码,
 3       c.chnname         状态,
 4        a.acceptdate       受理日期,
 5        a.effectivedate    生效日期,
 6        a.approvedate      承保日期,
 7        a.policyreturndate 回单日期,
 8        a.surrenderdate    退保日期,
 9        b.checkyearmonth   对账日期,
10        max(b.policyyear)       年度,
11        b.productname      险种名称,
12        b.premiumperiod    年期,
13        e.branchname 督导区,
14        f.branchname  营业部,
15        b.applicantname    投保人,
16        b.insuredname      被保险人,
17        a.AGENTNAME        代理人,
18        b.modalprem        保费,
19        b.valueprem        价值保费
20   from contract a, premium b,syscode c,branch e,branch f
21  where  a.contractid = b.contractid
22  and c.recordid = a.contractstatus
23  and b.bizcbranchid = e.branchid
24  and b.cbranchid = f.branchid
25   and a.channeltype = 'CHANNELTYPE_A'
26    and a.providerid in ('PRO0000000000006', 'PRO00000000000G6')
27    and a.bizbranchid = 'BRA0000000000006'
28     group by a.contractno,
29           a.acceptdate,
30           a.effectivedate,
31           a.approvedate,
32           a.policyreturndate,
33           surrenderdate,
34           checkyearmonth,
35           productname,
36           b.premiumperiod,
37           b.applicantname,
38           b.insuredname,
39           a.AGENTNAME,
40           modalprem,
41           c.chnname,
42           e.branchname,
43           f.branchname,
44           b.valueprem 
45    
46    --select a.providerid,a.providername from provider a where providername like '%信泰%'

结果集都是14678条，花费的三次时间依次为 25.178 23.806 25.037

 

2.索引方面

  没有建立索引之前

  建立索引之后

总结：效率提高接近100倍，那么哪些字段应该建立索引了？当你的一个不是很复杂的sql，例如上面的，花费的时间比较长，就应该从多方面调优，当其他方法无法调优，确实需要建立索引的时候，如上图，那个objectname的contract表花费了很长时间，看下你的where语句中有三个字段用到了branchid,contractstatus,effectivedate,然后，机构ID(branchid)的值不多只有几百个，保单状态(contractstatus)的值也不多，只有几十个，但是生效时间(effectivedate)却有千万，一天365天，24小时，等等，所以慢就慢在这里，加个索引 create index IDX_CONT_EFFECTIVEDATE on CONTRACT (EFFECTIVEDATE);索引这个用的好，效率提高，反之更慢，还会影响整个数据库的性能。

索引介绍：http://www.cnblogs.com/o-andy-o/archive/2012/08/16/2641974.html