sql优化个人心得

1.>=代替>，>首先会到该位置再往后选择。

2.exists和in的选择，里小外大用in，里大外小用exists。

3.避免隐式转换(主要在number和varchar2)，隐式转换不走索引列。

4.避免在索引列计算。

5.避免not 和!=

6.聚簇索引的选择

 

7.插入时append的使用，同时注意高水位线。append是在高水位线上直接插入，而不会去找空闲数据块，会引起HWM增大。同时注意append会锁。

8.高水位线引起的碎片整理，体现在全表扫描中，全表扫描时会读取HWM以下所有空间。

对频繁DML的表要经常

1)       清理表碎片,该方法为oracle10g新引进的 alter table <tablename> enable row movement; 但此方法会加上X锁，会造成DML阻塞，所以建议可在凌晨定时任务执行。alter table <tablename> shrink space cascade;(会一起整理表和索引碎片) 。一般OLAP系统不建议drop table然后rename。

2)       刷新表、索引统计信息dbms_stats.gatger_table_stats,选择cascade为ture时会同时刷新索引统计信息。

9.commit提交数量的选择，原则上commit越多越好，但是频繁commit会产生大量的redo和undo。反之，一个事务越大，commit需要的时间就越长。所以合理选择commit数量，建议总数据量的10%一次。

10.union all和union的选择。Union会排序并且去重。

11.尽量避免distinct，distinct会排序，如果要利用distinct排除重复数据尽量根据语句实现。

12.动态sql与静态sql的选择；多数情况下建议使用静态sql，静态sql属于前期联编，即SQL语句在程序编译期间就已经确定；而动态sql属于后期联编，即SQL语句只有在运行阶段才能建立。以及注意SQL语句中注意使用绑定变量，尽量避免硬解析。减少因硬解析产生的额外开销(CPU,Shared pool)。其次提高编程效率，减少数据库的访问次数。

13.decode代替case when

14.hints的使用，比如联合索引时，强制使用某个索引。全表扫描

15.尽量避免select *，有多少列则写多少列名。ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

16.varchar2的选择。Update时，若修改长度大约原长度，而空闲空间过小不足以容纳改行数据时会产生行迁移。

17.Procedure中游标的使用，数据批量读取和提交，bulk collect limit批量提取 和forall批量提交的使用。尽量减少PLSQL和SQL引擎的交互，减少提取数据的额外开销。同时注意commit时提交的次数和数量大小的选择，避免频繁commit和一次commit，提交数量大小则根据高速缓存的大小来进行调试。

18.在查询中From表顺序和Where条件连接顺序，从oracle10g后将再无影响。CBO(cost-based Optimization)会自动选择最优方案。若一个语句使用 RBO的执行计划确实比CBO 好，则可以通过加 " /*+RULE*/" 提示，强制使用RBO。

19.优先选择rowid>索引>全表扫描。rowid直接定位到物理存储地址。