oracle 优化学习笔记_优化器

1.概述

优化器负责解析sql。
优化器的目的是为了得到目标sql的执行计划。
基于判断规则分为RBO和CBO，10g后便不再支持RBO。通过RULE Hint能使部分情况下使用RBO。默认及主要规则都通过CBO来实现。

2.CBO

从oracle7就已引入，判断依据是基于统计信息估算出来的成本。

1. 基数（cardinality）
   在执行计划中表示每个操作返回的行数（Rows）。
2. 可选择率（selectivity）
   指过滤后结果集占原始数据的比率。
   基数=总记录数*选择率
3. 可传递性
   简单谓词传递：t1.a=t2.a and t1.a=1 => t1.a=t2.a and t1.a=1 and t2.a=1
   连接谓词传递：t1.a=t2.a and t2.a=t3.a => … and t1.a=t3.a
   外连接谓词传递：t1.a=t2.a（+） and t1.a=1 => t1.a=t2.a and t1.a=1 and t2.a（+）=1
4. 局限性
   4.1 会默认sql语句where条件中出现的各个列之间是独立的。虽然动态采样和多列统计信息能缓解这种问题，但是并不能根本上解决。
   4.2 会假设所有的目标sql都是单独执行的，并且互不干扰
   4.3 对直方图统计信息有比较多的限制。对于oltp类型的要关闭直方图。
   4.4 在解析多表关联的目标sql时，可能会漏掉正确的执行计划。

3.基础知识

1. 模式

SQL> show parameter optimizer_mode 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

默认为ALL_ROWS，为10g及以后版本的默认值。
另外一种常用的为first_rows_[1 | 10 | 100 | 1000]，会优先返回前[1 | 10 | 100 | 1000]行的数据。

2. 访问数据的方法
   直接访问表；先访问索引，再回表
   oracle常用的索引为B-树索引，B-树特点为所有子节点都在同一层。

访问方式	备注
全表扫描	多块读
ROWID扫描
索引唯一性扫描	针对唯一索引的等值查询
索引范围扫描
索引全扫描	结果有序且不需要排序，至少一列为NOT NULL
索引快速全扫描	不一定有序，可以多块读，可以并行
索引跳跃式扫描	条件为非前导列。适合前导列distinct值少，非前导列选择性好的

3. 表连接
   3.1表连接顺序：oracle总是两两表连接。要确定哪个是驱动表（outer），谁是被驱动表（inner）
   3.2表连接类型：内连接，外连接。左外连接，左边为驱动表，查询结果包含驱动表不满足连接条件的记录。oracle用（+）标记以NULL填充的被驱动表。
   3.3表连接方法：常见的是hash join和nest loop，还有排序合并和笛卡儿连接。nest loop的驱动表的结果集如果很大，效率会很慢，这个时候要尝试使用hash join。
   3.4访问表的方法：即上面提到的直接访问表还是先访问索引。

先确定表连接的顺序，然后根据连接条件确定表连接的类型，然后再确定表连接的方法。