第一章：Oracle里的优化器

1 什么是oracle里的优化器

1. 优化器是oracle数据库中内置的一个核心子系统，可以理解为oracle数据库中的一个核心模块，或一个核心功能组件，其目的是为了得到目标sql的执行计划

2. 优化器分为RBO(基于规则的优化器)和CBO(基于成本的优化器)两种，10g之后使用的都是CBO不再使用RBO

3. sql语句执行过程：用户–解析–查询转换–CBO/RBO–执行计划–实际执行–用户
   其中查询转换和CBO/RBO组合起来为优化器

1.1 RBO

1. Oracle11g已经不再使用它，因为他难以调整，且执行计划会与sql语句中对象的书写顺序有关
2. alter session set optimizer_mode=‘RULE’ 可以开启RBO

1.2 CBO

为解决RBO的弊端，Oracle7之后引入CBO，CBO会从目标sql诸多可能的执行路径中选择一条成本值最小的执行路径作为其执行计划，成本值是根据目标sql语句所涉及的表，索引，列等相关对象的统计信息计算出来的

1.2.1 Cardinality(集的势)

指对目标sql的某个具体执行步骤的执行结果所包含记录数的估算，Cardinality越大成本值往往越大，执行计划中Rows对应的就是每一步的Cardinality，Cost(%CPU)对应的是每一步的成本值

1.2.2 Selectivity(可选择率)

1. 指加条件后返回记录数/加条件前的记录数，范围0~1，值越大，可选择性越差，Cardinality越大，成本值往往越大

2. computed Cardinary(施加谓词条件后返回结果集记录数)=rows=original Cardinary*Selectivity

3. 目标列上没有直方图也没有null值的情况下用目标列做等值查询时可选择率的计算

--NUM_DISTINCT：13
select num_distinct from dba_tab_col_statistics where table_name='emp';
--Selectivity=1/NUM_DISTINCT(目标列distinct值数)
--例
select * from emp where mgr=7902;
--表*13条记录:original Cardinary
select count(*) from emp;
--distinct值的数量为13
select count(distinct mgr) from emp;
--Selectivity:1/13
--computed Cardinary=rows=13*(1/13)=1
--收集统计信息，但不收集直方图，不收集统计信息dba_tab_col_statistics中的num_distinct不会改变
begin
  dbms_stats.gather_table_stats(ownname          => 'C50HST',
                                tabname          => 'WSH_TABLE',
                                estimate_percent => 100,
                                cascade          => true,
                                method_opt       => 'for all columns size 1',
                                no_invalidate    => false);
end;
/
--查看执行计划rows为1

1.2.3 Transitivity(可传递性)

是查询转换中做的第一件事，根据现有的sql谓词条件增加新的谓词条件，从而增加cbo得到更高执行计划的可能性

1. 简单谓词传递：

t1.c1=t2.c1 and t1.c1=10 修改为 t1.c1=t2.c1 and t1.c1=10 and t2.c1=10

2. 连接谓词传递

t1.c1=t2.c1 and t2.c1=t3.c1 修改为 t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1

3. 外连接谓词传递

t1.c1=t2.c1(+) and t1.c1=10 修改为 t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10

1.2.4 CBO的局限性(缺陷)

以后内容会陆续提及

2 优化器的基础知识

以下内容既支持RBO也支持CBO

2.1 优化器的模式

1. 优化器的模式决定了使用RBO还是CBO，也决定了使用CBO时的成本值如何计算，其值记录在参数OPTIMIZER_MODE
2. 查看：

show parameter OPTIMIZER_MODE;
select name,value from v$parameter where name='optimizer_mode';

2. OPTIMIZER_MODE的值：
   1. RULE：Oracle用RBO解析sql，此时目标sql中涉及到的各个对象的统计信息对于RBO来说没有任何作用
   2. CHOOSE：Oracle9i中的默认值，目标sql中有一个对象有统计信息则选择CBO，一个没有则选择RBO
   3. FIRST_ROWS_n:(n=1,10,100,1000):Oracle使用CBO来解析sql，Oracle会把那些能以最快返回头n条记录所对应的执行步骤的成本值修改为一个非常小的值
   4. FIRST_ROWS：Oracle9i时已经淘汰
   5. ALL_ROWS：Oracle10g后的默认值，使用CBO，且此时CBO计算成本侧重点在于最佳吞吐量(最小的系统I/O和CPU资源的消耗量)
3. 修改

alter session set optimizer_mode=first_rows_10;

2.2 结果集(Row Source)

对于优化器而言，一个步骤的执行结果就是该执行步骤所对应的的输出结果集，它也是下一个执行步骤的输入结果集。对于CBO而言，对应执行计划中的列(Rows)反应的就是CBO对于相关执行步骤所对应的的输出结果集的记录数(Cardinality)的估算值

2.3 访问数据的方法

一种是直接访问表，一种是先访问索引再回表

2.3.1 访问表的方法

一种是全表扫描，一种是ROWID扫描

2.3.1.1 全表扫描

从该表所占用的第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描到该表的高水位线，这段范围内的所有数据块都必须读到，当然，Oracle会对这期间读到的所有数据施加where条件中指定的过滤条件。全表扫描使用多块读，所以表中记录少时效率还是很高。delete不会降低高水位线，所以即使删光了表中的数据，使用全表扫描还是会扫描该表高水位线下的所有块，还是非常慢

2.3.1.2 ROWID扫描

ROWID记录的是oracle数据行记录所在的物理地址，也就是说ROWID和Oracle中数据块里的行记录一一对应，他是一个伪列，可以使用函数

--获取文件号，即dba_data_files中的file_id
dbms_rowid.rowid_relative_fno(rowid)
--获取数据块号
dbms_rowid.rowid_block_number(rowid)
--获取行号
dbms_rowid.rowid_row_number(rowid)

2.3.2 访问索引的方法：这里指的都是B*树索引

1. Oracle中使用的B树索引，其实是B*树
2. B*树索引内部结构：
   1. 叶子节点块：存放索引值、该索引值对应表中数据的物理地址(rowid)，当前节点的前后节点的数据块地址
   2. 分支节点块：存放索引值、该索引值对应下一级节点块地址(lmc指针)
   3. 根节点块：一个B树索引只有一个根节点，位于最顶端的分支节点
3. 访问索引的成本：
   1. 访问相关B树索引的成本：从根节点定位到相关分支块，再定位到相关叶子块，最后对这些叶子块进行扫描
   2. 回表的成本：根据得到的ROWID扫描对其对应的数据块
4. B*树索引的优势：
   1. 所有索引叶子块一定在同一层，因此它们距离索引根节点深度相同，即访问索引叶子块任何一个索引键值所花费时间相同
   2. 通过B树索引访问表中行记录的效率不会因表中数据量的递增而显著降低，因为数据量增加非常大，索引深度才会加1，而索引深度增加1成本开销也很小，这也是走索引和走全表扫描最大的区别

2.3.2.1 索引唯一性扫描(INDEX UNIQUE SCAN)：

1. 产生条件：
   1. 对唯一索引对应列进行等值查询
2. 其他：
   1. 由于扫描的对象是唯一索引，所以INDEX UNIQUE SCAN的结果最多只有一条记录

2.3.2.2 索引范围扫描(INDEX RANGE SCAN)：

范围的意思是查询出的结果可能是一个范围

1. 产生条件：
   1. 对唯一索引进行范围查询(between、>、<等)
   2. 对组合类型的唯一索引只使用组合中的部分列进行查询，导致查询出多行
   3. 扫描非唯一索引
2. 其他

--清理Buffer Cache
alter system flush buffer_cache;
--清理数据字典缓存
alter system flush shared_pool;

2.3.2.3 索引全扫描(INDEX FULL SCAN)：

先定位到索引的根节点块，再找到分支节点块，最后找到第一个叶子结点块，从左至右依次顺序扫描该索引所有叶子块的所有索引行，因为索引是有序的所以查出的结果也会是按索引顺序排序的，即执行计划中的sorts(memory)和sorts(disk)都是0，由于有序所以不能并行执行，索引全扫描不需要回表

1. 产生条件:
   1. 需查询的列都在索引中
   2. 查询出的结果集较多，导致Oracle认为走索引范围扫描，再回表的成本较高，因此选择走索引全扫描，不用回表

create table wsh(wsh_1 varchar2(20),wsh_2 varchar2(20));
create index wsh_index on wsh(wsh_1);
--下面语句，所需查询的列wsh_1都在索引wsh_index中，但由于索引中不存空值，如果走索引全扫描，会丢失wsh_1为null的数据，索引还是走全表扫描
select wsh_1 from wsh;
--有两种修改方式可以让这个语句走索引全扫描
--1.将索引中字段改成非空
alter table wsh modify wsh_1 not null;
--2.查询语句中过滤掉wsh_1为空的结果集
select wsh_1 from wsh where wsh_1 is not null;

2.3.2.3 索引快速全扫描(INDEX FAST FULL SCAN)：

1. 产生条件：
   1. /+index_ffs(表名 索引名)/
2. 与索引全扫描类似，如果结果集不要求有序，一般相比索引全扫描，会优先考虑索引快速全扫描
   1. 索引快速全扫描只适用于CBO
   2. 索引快速全扫描可以使用多读块，也可以并行执行
   3. 索引快速全扫描执行结果不一定有序，他是按索引存放的物理位置顺序扫描的

2.3.2.4 索引跳跃式扫描(INDEX SKIP SCAN)：

其功能就好像是在扫描某组合索引时跳过了其前导列(索引中的第一列)，直接从该索引的非前导列开始扫描一样

--例:索引employee(gender,employee_id)
select * from employee where employee_id=100;
--上个语句会用到索引跳跃式扫描，因为系统会先取出gender的所有distinct值，然后对上个语句进行等价改写
select * from employee where gender='M' employee_id=100 union select * from employee where gender='F' employee_id=100;

1. 产生条件：
   1. 目标索引前导列的distinct值数量较少，条件列的可选择性又好：此时Oracle才会认为使用索引跳跃扫描的成本值较低，因为索引跳跃扫描的执行效率一定会随目标索引前导列的distinct值数量的递增而递减

2.4 表连接

决定执行计划的三件事

1. 表连接顺序：不管sql中有多少表连接，oracle都只能一次进行两两连接，直到所有表连接完毕，所以优化器每次判断两表连接，首先决定哪两个表先相连，其次要决定谁是驱动表

2. 表连接方式：

   1. 排序合并连接
   2. 嵌套循环连接
   3. 哈希连接
   4. 笛卡尔连接

3. 访问单表的方法

   1. 全表扫描
   2. 走索引

2.4.1:表连接的类型

1. 内连接：

join==inner join=="a.col1=b.col1"

2. 外连接：

1. left outer join=="a.col1=b.col1(+)"
2. right outerjoin=="a.col1(+)=b.col1"
3. full outer join：为前两个的并集

2.4.1.1 内连接

--oracle自己的写法
select * from t1,t2 where t1.no=t2.no
--标准sql写法
select * from t1 join t2 on (t1.no=t2.no)

2.4.1.2 外连接

--左外连接，outer的意思为驱动，会以表1作为驱动表
表1 left outer join 表2 on (连接条件)
--右外连接
表1 right outer join 表2 on (连接条件)
--全连接，可以理解为取left outer join和right outer join结果集的并集
表1 full outer join 表2 on (连接条件)
--先筛选t1中col1值为1的数据再与t2数据做外连接，可以理解为t1.col1参与外连接
select * from t1 right outer join t2 on (t1.col2=t2.col2 and t1.col1=1);
select * from t1 ,t2 where t1.col2(+)=t2.col2 and t1.col1(+)=1;
--会将t1和t2先做外连接后再对整个结果集筛选t1.col1=1的记录,可以理解为t1.col1不参与外连接，且由于此处t2为驱动表，即如果关联不上时，t1.col1会是null，但又由于加上了条件t1.col1=1，因此最后其结果等同于内连接
select * from t1 right outer join t2 on (t1.col2=t2.col2) where t1.col1=1;
select * from t1 ,t2 where t1.col2(+)=t2.col2 and t1.col1=1;

2.4.2 表连接的方法

2.4.2.1 排序合并连接(SORT MERGE JOIN):

执行步骤

1. 以目标sql中的指定谓词过滤t1表，按连接列排序后得到结果集1
2. 以目标sql中的指定谓词过滤t2表，按连接列排序后得到结果集2
3. 过滤并连接：取出结果集1中第1条记录，查找结果集2中是否有匹配记录，假设结果集2中该匹配记录位置为n1。由于结果集2中记录已被排序，所以不需要继续判断n1后数据是否匹配(因为一定不匹配)。之后取出结果集1中第2条记录，遍历集2中n1位置后的所有数据判断是否匹配(因为n1之前一定不匹配)，依此类推得到最终结果。

优缺点及使用场景

1. 执行效率远不如hash join，但是可以适用于<，>，<=，>=等连接条件，而hash join只适用于=连接条件
2. 如果可以避免排序，其效率会大大增加，即各自连接列上存在索引，可以提升排序合并连接的效率
3. 严格来讲，排序合并连接不存在驱动表的概念

执行计划

--执行语句
select /*+use_merge(t1 t2)*/t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2
--部分执行计划
8	----------------------------------------------------------------------------
9	| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
10	----------------------------------------------------------------------------
11	|   0 | SELECT STATEMENT    |      |       |       |     6 (100)|          |
12	|   1 |  MERGE JOIN         |      |     1 |    20 |     6  (34)| 00:00:01 |
13	|   2 |   SORT JOIN         |      |     1 |    15 |     3  (34)| 00:00:01 |
14	|   3 |    TABLE ACCESS FULL| T1   |     1 |    15 |     2   (0)| 00:00:01 |
15	|*  4 |   SORT JOIN         |      |     1 |     5 |     3  (34)| 00:00:01 |
16	|   5 |    TABLE ACCESS FULL| T2   |     1 |     5 |     2   (0)| 00:00:01 |
17	----------------------------------------------------------------------------
--由于判断T2中是否存在匹配记录时，存在一个过滤的过程，所以谓词条件有两个，filter就是这个过滤的动作
46	   4 - access("T1"."COL2"="T2"."COL2")
47	       filter("T1"."COL2"="T2"."COL2")

2.4.2.2 嵌套循环连接(NESTED LOOPS JOIN):

执行步骤：

1. 优化器决定谁是驱动表，谁是被驱动表，这里假设t1为驱动表，t2为被驱动表
2. 以目标sql中的指定谓词过滤t1表，得到结果集1
3. 先取出结果集1中的第一条数据，遍历t2表，判断是否存在匹配数据，再取出结果集1中的第二条数据，遍历t2表，判断是否存在匹配数据。。。
4. 对t1的循环叫做外层循环，对t2的循环叫做内存循环，这就是嵌套循环的含义

优缺点及使用场景

1. 驱动表记录少，且被驱动表的连接列上有唯一索引，或选择性很好的非唯一索引，那么执行效率就会很高，但如果驱动表的数据量很大，无论如何效率都会很低
2. 因为先循环结果集1是由谓词过滤后的结果，所以大表也可以作为驱动表，关键在于谓词条件是否能将结果集降下来
3. 其他连接方式所没有的有点：循环嵌套连接可以实现快速响应，他可以第一时间返回已经连接且满足连接条件的记录，不必等到所有连接操作都做完再一起返回记录。排序合并连接是排序后做合并时才开始返回数据，哈希连接要等到驱动结果集所对应的hash table全部建完后才开始返回数据
4. 执行计划中nested loops下会有并行的两行，第一行为驱动表的执行动作，第二行为被驱动表的执行动作，对于Oracle 11g，回表动作也被看成一次nested loops，因此会有两层nested loops

--将优化器版本退回到Oracle 9iR2版本，并使用nl
/*+optimizer_features_enable('9.2.0') ordered use_nl(t2)*/

2.4.2.3 哈希连接(HASH JOIN):

哈希连接是为了解决排序合并连接中t1、t2结果集较大或嵌套循环连接时驱动表结果集过大而导致效率很低而产生的

执行步骤：

1. 根据参数HASH_AREA_SIZE，DB_BLOCK_SIZE，HASH_MULTIBLOCK_IO_COUNT的值来决定Hash Partition的数量(一个Hash Table由多个Hash Partition组成，一个Hash Partition是一组Hash Bucket的集合，一个Hash Bucket存放hash_value_1相同的(hash_value_1, 查询列、连接列、hash_value_2值)
2. 对表t1，t2指定谓词条件过滤后得到结果集S和结果集B，少的为驱动结果集，多的为被驱动结果集，假设S是驱动结果集，B是被驱动结果集
3. oracle遍历S，对每一条按其连接列做哈希运算，方法为hash_func_1和hash_func_2，假设他们所计算出结果分别为hash_value_1和hash_value_2
4. 按hash_value_1值将S中的查询列、连接列、hash_value_2值，储存在不同的Hash Bucket中，假设S所对应的每一个Hash Partition为Si
5. 构建Si的同时，Oracle会构建一个位图，用于记录Si所包含的每一个Hash Bucket是否有记录
6. 如果S中数据量很大，那么构建S所对应的Hash Table时，会将PGA的工作区(WORK AREA其实也是内存)填满，那么Oracle会把工作区中记录数最多的Hash Partition写到磁盘上(Temp表空间)，再满再选最多的写到磁盘上，如果某工作区中的Hash Partion已被写回磁盘(即此次被填满的工作区中的这个Hash Partion对应的Hash值的区间，与已经写回磁盘的某Hash Partion对应的Hash值区间重复)，那么Oracle会到磁盘上更新此记录
7. 上述步骤一直持续，直到遍历所有S中的记录
8. 对所有的Si按记录数从小到大排序，并优先放入内存中(PGA的工作区)，根本目的就是想把那些记录数较小的Hash Partition保留在内存中。如果没有Hash Partition被写入磁盘，就不需要排序
9. 按遍历S的方法遍历表B，假设结果为hash_value_1和hash_value_2，按hash_value_1去内存中的Si里找匹配的Hash Bucket，如果找到了匹配的Hash Bucket，遍历其内所有记录，校验连接列是否真的相同(直接看真实值是否相同：因为不同值经过hash运算的结果可能相同)，如果匹配则组合成一条作为最终结果集。如果找不到匹配的Hash Bucket，Oracle会访问5中构建的位图，如果位图显示该Hash Bucket在Si对应的记录数大于0，说明该Hash Bucket已经被写回了磁盘中，此时Oracle会将B中的记录也写入磁盘中(位图过滤)。如果位图显示该Hash Bucket在Si对应的记录数等于0，则不用将B中记录写会磁盘，因为一定不匹配。B所对应的每一个Hash Partition记做Bj
10. 直到B中所有记录遍历后，所有内存中的si和bj已经完全处理，只剩硬盘中的si和bj没有处理，我们用sn和bn表示位于磁盘上，且对应Hash Partition Number值相同的si和bj
11. 对于每一对sn和bn记录少的会被当做驱动结果集，多的为被驱动结果集，然后使用驱动结果集Hash Bucket里记录的hash_value_2构建新的Hash Table，每一对的sn和bn的谁作为驱动结果集都可能发生变化(动态角色互换：记录数少的一方作为驱动结果集)
12. 上述步骤如果存在匹配记录，则也作为结果集返回，直到遍历完所有的sn和bn

优缺点及使用场景

1. 哈希连接不一定排序，大多数情况都不排序
2. 驱动表对应的连接列的可选择性应尽可能好，因为他会影响Hash Bucket中的记录数，从而影响从该Hash Bucket中查找匹配记录的效率，如果一个Hash Bucket里包含记录数过多，典型的表现就是语句很久不结束，cpu很高，但是逻辑读很低，因为遍历Hash Bucket的动作发生在PGA中不产生逻辑读
3. 适用于小表和大表之间做连接，且结果集中记录较多的情况，特别是小表的连接列的可选择性非常好的时候，其执行时间近似可以看做和全表扫描大表的时间相同
4. 如果驱动表被谓词条件过滤后的数据量可以完全容纳在内存中(PGA的工作区)，此时hash join效率非常高
5. 哈希连接只适用于等值连接

2.4.2.4 笛卡尔连接(MERGE JOIN CARTESIAN):

执行步骤

1. 根据指定谓词方位表t1，得到结果集1，假设记录数为m
2. 根据指定谓词方位表t2，得到结果集2，假设记录数为n
3. 对结果集1和结果集2进行合并，因为没有合并条件，所以1中的每条记录，2中都满足连接条件，最后产生m*n条记录

执行计划

连接名为：MERGE JOIN CARTESIAN
这一步的rows应该为下面两个表的rows的乘积

优缺点及试用场景

1. 写语句时写漏的两表连接条件，非常慢
2. sql中使用了ordered hint，且sql文本中相邻两个表间没有直接的关联条件
3. sql中相关表的统计信息不准确

2.4.3 反连接(连接方法 + ANTI (NA))

ORACLE中没有相关的关键字表示反连接，但会把where条件为not exists，not in，<>all的子查询转换为对应的反连接

执行步骤

t1是驱动表，t2是被驱动表，一旦满足t1.col2=t2.col2那么这条记录就会被丢弃

ANTI与ANTI NA区别

1. not in与<> all：子查询结果集有null值，那么查询结果没有返回，这个大部分情况是不想要的结果。如果子查询结果集没有null值，主查询的null值记录也不会返回，这也称作其对null值敏感
2. not exists：子查询有null值不会返回空结果集，主查询为null值的记录会返回
3. not in中子查询一定全表扫描，而not exists不一定，因此子查询结果集大时，应使用not exists
4. 如果使用HASH JOIN ANTI进行过滤，无法得到正确的结果集，所以11g之前都是使用效率低下的filter获取结果
5. 为了提高效率，ORACLE11gR2之后，推出了改良的可以得到正确结果集的反连接：HASH JOIN ANTI NA(Null-Aware缩写)
6. ORACLE11gR2之前，使用not in的情况下走反连接

--1.确保not in后结果集不包含null记录
select * from t1 where t1.col2 not in (select t2.col2 from t2 where t2.col2 is not null);
--2.使用not exists
select * from t1 where not exists (select 1 from t2 where t1.col2=t2.col2);

7. ORACLE11gR2开启反连接参数：_OPTIMIZER_NULL_AWARE_ANTIJOIN

alter session set "_OPTIMIZER_NULL_AWARE_ANTIJOIN"=false;

2.4.4 半连接(连接方法 + SEMI)

ORACLE中没有相关的关键字表示半连接，但会把where条件为exists，in，=any的子查询转换为对应的半连接

--以下语句结果与执行计划完全相同
select * from t1 where col2 in (select col2 from t2);
select * from t1 where col2 = any(select col2 from t2);

执行步骤

t1是驱动表，t2是被驱动表，一旦满足t1.col2=t2.col2那么马上停止搜索表t2，并直接返回t1.col2=t2.col2的记录，即就算t2中有多条满足连接条件的记录，表t1也只返回第一条满足条件的记录

--t1中col1为2的有1条记录，t2中col2为2的有2条记录
--返回2条相同记录
select t1.* from t1,t2 where t1.col1=t2.col2;
--返回1条记录，与上条比较，可看做有去重的作用
select * from t1 where col2 in (select col2 from t2);

2.4.5 星型连接(STAR JOIN)