sql优化经验分享

大数据量优化

1.表关联替换 not in

select * from t1,t2 where t1.id = t2.id(+) and t2.id is null

2.慎用distinct和order by

3.去掉重复数据，可以考虑使用row_number() OVER(PARTITION BY CP_NO ORDER BY …)

Select * from (select … , row_number() OVER(PARTITION BY CP_NO ORDER BY …) rowflg) where rowflg = 1

4.对查询字段增加索引

5.统计索引信息

analyze table table_name compute statistics;

6.尽量少使用循环查询数据。