oracle数据库开发优化建议

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第19天，点击查看活动详情 >>

oracle数据库开发优化建议

1.SQL变量类型必须与表字段类型一致【强制】

2.SQL如要使用索引 过滤条件应禁止应用函数或计算【强制】

复制代码 反例 to_char(deal_time,'yyyy-mm-dd')=:B1 deal_time +1 >:B1

正例 deal_time between to_date(:B1,'yyyy-mm-dd') and to_date(:B1,'yyyy-mm-dd') deal_time > :B1-1

若函数或运算无法避免 可以考虑建函数索引 复制代码

3.禁止使用select * from ，需按查询字段【强制】

4.insert必须列出字段名【强制】

5.尽量避免条件写在having子句中 如可能写在where子句中【强制】

复制代码 反例 select a,count(*) from A group by a having a=1 or a=2

正例 select a,count(*) from A where a=1 or a=2 group by a 复制代码

6.在业务含义一致的前提下 使用union all 代替union【推荐】

union会进行去重操作 业务一致的前提下union all效率更高

7.尽量使用多表关联替代子查询【推荐】

8.若子查询无法避免 优先使用EXISTS/NOT EXISTS替代IN/NOT IN【推荐】

9.少量固定值或子查询结果集少的时候可以用IN(通常10个内) 否则建议使用多表关联【推荐】

10.使用union 替换or(or条件字段均为索引时)【推荐】

例 若agent表有id和parent索引

反例： select id,parent from agent where id=:b0 or parent=:b1 正例： select id,parent from agent where id=:b0 union select id,parent from agent where parent=:b1

11.like子句前端匹配【推荐】

模糊查询考虑前端匹配 非前端匹配的模糊查询业务需求 原则上应拒绝 反例： select name from product where pro like '%A01%'

正例： select name from product where pro like 'A01%'

12.尽量使用关联查询改写标量子查询【推荐】

标量子查询数据量大时 存在性能问题 反例 select a.id,(select b.name from t1 b where a.owner = b.name) from t2 a 正例 select a.id,b.name from t2 a,t1 b where a.owner = b.name

13.更新表数据时 建议使用merge替代 update【推荐】

反例 update t1 t set t.nos=(select a.nos from tmp a where a.no=t.no)

正例 merge into t1 t using tmp a on(t.no=a.no) when matched then update t.nos = a.nos

14.分析函数【推荐】

对排序 去重等复杂操作 分析函数简化sql写法提高性能 正例：按课程对学生成绩排序 select name,course,row_number() over(partition by cource order by score desc) rank from student

15.避免关联复杂视图 易导致执行计划不可控 导致全表扫描【推荐】

16.使用高效的分页写法【推荐】

反例 select column_lists from(select rownum as rn,A.column_lists from(select column_lists from table_name order by col_n)A) where rn<=20 and rn>10

正例 select column_lists from(select rownum as rn,A.column_lists from(select column_lists from table_name order by col_n)A where rn<=20)where rn>10

\

\

其它优化手段无法发挥作用时可以考虑HINT

1.强制使用索引

select /*+index(e emp_name_ix) */ first_name from employees e where last_name ='xxx' 2.强制全表扫描

select /*+full(e) */ first_name from employees e where last_name = 'xx' \

\

其它

1.正确使用聚合函数count sum【推荐】

count(col)计算col不为null的条数 count(distinct col)计算不为null且不重复的条数

sum()函数可能返回null 可以nvl(sum(qty),0)避免返回null

2.大表查询并发控制【推荐】

如前端按搜索按钮后禁用按钮