优化SQL语句

来源：互联网发布：linux socket编程实例编辑：程序博客网时间：2024/06/02 04:54

建议不用 * 来代替所有列名
用truncate代替delete
多用commit以释放事务所占用的资源
用exists代替in，in子句将执行一次全表遍历和合并，是最低效的
WHERE子句中，表与表之间的连接要写在其他条件之前
WHERE子句中，那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾
SELECT PERIOD_NAME,SUM(QUARTER_TO_ DATE_DR) FROM GL_BALANCES WHERE EPERIOD_NAME=‘JAN-06’ AND ACTUAL_FLAG=‘A’ GROUP BY PERIOD_NAME
改成：
SELECT PERIOD_NAME,SUM(QUARTER_TO_DATE_DR) FROM GL_BALANCES WHERE ACTUAL_FLAG =‘A’ AND PERIOD_NAME =‘JAN-06’ GROUP BY PERIOD_NAME
避免全表扫描，特别是大表的全表扫描
多表链接查询时，Oracle 的解析器按照从右到左的顺序处理FROM子句中的表名，会把第一个表也就是最后一个表进行排序作为驱动表，然后扫描第二个表并与驱动表合并，所以排序快的表（有索引或者数据量少的表）要写在最后面作为驱动表
避免’<>’和’!=’操作符使用
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM WHERE ITEM_ID<>'A_INC'
改写为：
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM WHERE ITEM_ID>'A_INC' OR ITEM_ID<'A_INC'
比较不匹配的数据类型
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ ITEM WHERE ITEM_ID=100
如果ITEM_ID是varchar2类型，应该改成
SELECT ITEM_ID,ITEM_DESC FROM CHECK_ITEM WHERE ITEM_ID='100'
类型应该对应，避免类型转换导致无法使用索引
用UNION ALL 代替UNION
UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，而UNION ALL 操作只是简单的将两个结果合并后就返回，效率高很多
使用WHERE 代替HAVING
HAVING 只会在检索出所有记录之后才对结果集进行过滤，也就是需要排序、总计
SELECT SET_OF_BOOKS_ID_PERIOD_NAME,SUM(QUARTER_TO_DATE_DR) FROM GL_BALANCES GROUP BY PERIOD_NAME HAVING PERIOD_NAME<>'JAN-06'
改写成：
SELECT SET_OF_BOOKS_ID_PERIOD_NAME,SUM(QUARTER_TO_DATE_DR) FROM GL_BALANCES WHERE PERIOD_NAME<>'JAN-06' GROUP BY PERIOD_NAME
建立索引以加快查询速度
我们先比较下没有索引和有索引select数据的区别
例子:select * from emp where deptno = 10;
要分1,2两种情况分析:
1)emp表较小,emp的数据全部缓存在内存中
没有索引:对内存中的emp表做一次全表扫描(FTS,full table scan),内存中遍历一遍速度很快
有索引:先从索引中获得X个满足dept=10的rowid,然后根据这些rowid从emp表中查询结果
结论:数据量大的表字段加索引,不然毫无意义
2)emp表较大,emp的数据在磁盘中
假设emp表共有X个数据块,每次IO操作读取200个数据块(db_file_multiblock_read_count = 200)
没有索引:全盘扫描需要X/200次I/O
有索引:假设索引全部在内存上,根据索引查询index的开销忽略不计,假设满足deptno=10的rownum有Y条,命中率为Z,这需要Y*(1-Z)次IO
假设一个数据块存储P行数据,想要索引效率更高,那么deptno=10检索到的数据占整个emp表的比例Y/(X*P)要小于1/(200*P*(1-Z))(一般一个数据块8K,假设一行数据有4个50byte的字符串,那么一个数据块大概能存40行数据,Z 99.5%的时候=2.5%,Z 99.9%的时候=12.5%)
结论:重复性少的关键字加索引,像性别这种随便查查命中都有50%左右的字段加索引还没有FTS速度快
3)在修改的操作后需要重新生成索引,如果修改过多,索引维护成本太高
结论:不会频繁修改的列加索引
4)因为索引维护需要成本(索引一般放在放在内存中,需要占用内存空间),所以既然加了索引就要多用,不然就亏了
结论:where子句中频繁使用的关键字,进行表连接的关键字,外键关键字可以考虑加索引
5)需要查看建立的索引是否被使用：
select * from v$object_usage;
如果没有用，那可以删掉
避免列上加运算或者函数,如:
select * from staff where salary*2<100;
应该改成:
select * from staff where salary<100/2;
因为运行和函数会不使用索引

1 0