oracle sql优化

来源：互联网发布：多玩歪歪软件编辑：程序博客网时间：2024/06/05 07:30

只选择需要的字段,不要无条件选择所有字段。
这样的话在一个内存页面中可以装入尽可能多的记录，减少和数据库的交互。

Select * from ffhz where …
Select zh,hm,ye from ffhz where …
快了大概25秒很给力

二、where条件中对于索引字段尽量不要使用函数。因为这样不会使用到索引。

Select * from ffhz where zh=‘12345’
Select * from ffhz where substr(zh,1,5)=‘12345’
Select * from ffhz where a||b=‘12345’
0.28秒比较正常，再看看下面的同样的语句添加了trunc
从0.28秒变成1分12秒了这效率。。。。。。就是因为opton字段添加了trunc函数失去索引了。

三、尽可能条件相同，写程序的时候尽量使用参数，而不要使用写死的动态sql。尽可能使用到数据库的缓存

Select * from ffhz where zh=?
Select * from ffhz where zh=‘123’
Select * from ffhz where zh=‘345’

四、用exists语法，而少用in的语法。In要遍历所有记录，而exists 只找到一条满足条件的记录就会中止。

Select * from fhqfh where zh in (select zhkh from flsmxz)
Select * from fhqfh where exists(select zhkh from flsmxz where fhqfh.zh=flsmxz.zhkh)

五、如果不需要取全部的记录，最好只取前多少条记录。

select * from aaa where rownum<100

六、建立索引在种类最多的字段，这样才能尽可能使用到索引

如对ffhz的hbh建立一个索引
对zh建一个索引
注意：对于种类较少的字段尽量不需要建立索引，一般来说应该建立位图索引但是oracle数据库会自动为种类较少的字段建立位图索引

七、不要过多的表联合查询，最好不要超过三个。越少越好，可以适当使用临时表。尽可能用唯一条件进行关联。

Select * from a,b,c,d,e where a.x=b.x …
Select * from a,b,c where … into temp temp1
Select *from d,e where … into temp temp2
Select * from temp1,temp2 where …

八、使用between and 语法，而不是like ,这样可以尽可能使用索引。

Select * from ffhz where zh like ‘9%’
Select * from ffhz where zh between ‘9000000000000000’ and ‘9999999999999999’

九、少使用!=、<>（全表扫描，不会利用索引）,可以使用>,<,>=,<=等操作。

a是整型，是索引字段。

Select * from aaa where a!=1;
Select * from aaa where a<=0 or a>=2

可以看到同样的语句只是"="和“<>”的区别效率竟然相差了2位数。
十、当需要使用union或者union all的时候，如果结果集不会出现重复或者不需要去掉重复的时候建议使用union all这样少了一步去重复的步骤比union效率更高。

十一、关于谓词的理解

可能网上或者有些书上告诉我们谓词在sql语句中出现的顺序会影响sql语句的执行效率，但是经过实际调研发现oracle在执行我们的sql语句之前有自己的sql语句重构分析器，会将

我们的sql语句重构筛选出能最大程度缩小结果集的谓词首先执行，所以我们不论如何排序谓词oracle都会重新分析。实例如下图：

从上述例子中可以看出不论是将opton的筛选条件放在where条件的什么位置，oracle分析重构时都会将它放在第一个进行筛选的条件上。

当然oracle还有一些对谓词筛选重构的规则，首选oracle会选取带索引的谓词放在前面（当然也可能出现实际的表中分析发现使用索引的谓词并不

如某个不使用索引的谓词筛选条件强而将非索引谓词放在前面的情况），对于非索引谓词一般的条件如下排序：

1. 等于谓词

2. 范围谓词

3. 列表（比如in）和相似谓词（比如like）

十二、尽量少用in如果可能的话使用exists代替，is null、is not null、in 、not in 都不会使用到索引。

十三、merge语句的使用

MERGE INTO TQ_SCHEDULE DE USING(SELECT 'pp' AS SCHID FROM DUAL ) TQ
ON (TQ.SCHID=DE.SCHID) WHEN NOT MATCHED THEN INSERT (SCHID, NAME, SCTYPE, EXPRESSION, RUNNINGFLAG, LASTRUNON, MAXRUNTIME, ISINUSE)
VALUES('pp', '每月自动上报票据', 2, '0 0 2 1 * ?', 'N', to_date('19-02-2001 08:49:56', 'dd-mm-yyyy hh24:mi:ss'), 200000, 'Y');

MERGE INTO TR_REFUNDOPT RE USING (SELECT SYS_GUID() ID, T.CONSUMEID, T.PAYMNY FROM TR_TRANSOUT T
WHERE T.LISTID = ? )XF
ON (XF.CONSUMEID=RE.Consumeid ) WHEN NOT MATCHED THEN INSERT
(OPTID, CONSUMEID, REFUNDTYPE, REFUNDMNY, STATE, OPTBY, OPTON) VALUES (
XF.ID,XF.CONSUMEID , 1 ,XF.PAYMNY, 2 , 'AUTO' , SYSDATE )

十四、最重要的一点

多做测试
对于使用比较频繁或估计以后会处理较多数据的地方，应构造尽可能多的数据进行测试，以便发现效率问题。

0 0