oracle优化复习

Oracle数据库中的优化器又叫查询优化器（Query Optimizer）。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。Oracle的优化器有两种，基于规则的优化器（RBO）与基于代价的优化器（CBO)

pl/sql developer设置

tool->preference->window types ->plan window
time移动到右侧

看懂执行计划

文件-》新建-》解释计划窗口
基数rows 当前操作返回结果集行数(cardinality
字节
耗费 执行成本
时间

执行顺序

缩进最多最先执行（缩进相同，最上面最先执行）
同一级如果某个动作没有子id就最先执行
同一级的动作执行遵循最上最右先执行原则

表访问方式

1.table access full 全表扫描
建议：数据量太大不要使用，除非本身取出数据较多，占表数据总量5%-10%以上
2.table access by rowid 通过rowid的表存取
伪
3.table access by index scan 索引扫描
1. index unique scan 至多返回一条记录，表中某字段存在unique\primary key约束
2. index range scan 一个索引存取多行数据，三种情况下，唯一索引列使用了范围操作符（> <）,组合索引上知使用部分列查询（查询时必须包含前导列，否则会走全表扫描），对非唯一索引列进行的额任何查询
3. index full scan（cbo模式下有效）查询出的数据必须从索引中可以直接得到
4. index fast full scan 与索引全扫描类似，但不对查询的数据进行排序
5. index skip scan 当复合索引的前导列没有在查询语句中出现，oracle就会使用该复合索引走indx skip scan
索引扫描得到对应的rowid 通过rowid定位到具体行读取数据

表连接方式

sort merge join 排序-合并连接
select jion on a.id= b.id
条件 < = > 不适用条件：<> like

nested loops 嵌套循环
适用 row source1返回行数尽可能少并且能高校访问row source2,效率较高
hash join 哈希连接
适用等值连接

• inner join 外连接
  left outer join 、right outer join 、full outer join(返回两表全部记录，不匹配项用空值)
• outer join 内连接 ：等值连接、非等值连接
  只返回两表中相匹配的记录

select  from a (inner) join b on a.id =b.id;

cartesian product 笛卡尔积

数据库性能

SQL TURNING

• sql优化一般性原则
  

避免
适用确定的列名 少用*
少嵌套子查询，对于多个or建议分成多个查询用union all连接起来，将记录少的表放在右边（oracle 解析对表解析从右到左）
多用commit语句提交事务，可以及时释放资源。。。
动态执行sql尽量用execute方式，不用dbms sql包

具体：
1.不要让oracle做的过多
避免复杂的多表关联
避免用*（oracle解析会将转换成所有列名）
避免使用耗费资源的操作：distinct union minus in tersect order by,会启动sql引擎执行耗费资源的排序功能
用exists替换distinct in
union all 替换union ,union会去重

2.给优化器更明确的命令
至少要包含组合索引第一列，被where字句引用时候才会使用该索引
避免在索引列使用函数，低效 sal12>25 高效sal>25/12
避免在索引列使用not，此时同上，停止索引而执行全表扫描
避免在索引列使用is null，空值不存在索引列中
避免使用前置通配符 user_no like ‘%1’，oracle在这种情况将全表扫描
避免出现索引列自动转换 字符串转数字
在查询时少用格式转换 to_number
3.减少访问次数
用decode
含有子查询的sql语句 减少对表的查询
4.细节。。。
where字句,最后的表应该是返回行数最小的表，过滤条件放最后
order by
不用联结列||
where替换having
not extists 替换not in
避免在索引里使用函数
<=替代>
避免使用not <>改成< or >
外部联结用+
用truncate代替delete
多用commit
…