Oracle执行计划

最近做数据迁移，重拾Oracle，买了从来没看的Oracle书可以发挥价值了，学执行计划前先普及一点基本概念
一、基本概念
1、Rowid的概念，Oracle的一个虚拟列，用于命中索引后回表(根据rowid去文件块的某个位置读取数据)，rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变
2、Recursive SQL概念:用户的ddl，dml操作会带来一些隐藏操作，显而易见的就是会修改数据字典，数据字典信息存储在内存中
3、Row Source(行源) ：通俗点说就是查询或连接时的摸个表经过where条件过滤后剩下的结果集
4、Driving Table(驱动表)：该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。ps：读完后面的内容就对潜逃和hash链接有概念了，我第一次读也不是很清晰
5、Probed Table(被探查表) 与4相反
6、组合索引(concatenated index)：由多个列构成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：[b]引导列(leading column)[/b]，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。
7、可选择性(selectivity)：其实就是 distinct 这列/count(1)
二、Oracle读取数据的方法
1、 全表扫描（Full Table Scans, FTS）
极端情况会全表扫面，也不会用到rowid，比如select * from xx 没了，任何条件都没，这种情况可以设置并发读取，提高效率
2、通过ROWID的表存取（Table Access by ROWID或rowid lookup）
通过rowid定位文件块位置

SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3、上面2中都很少见，或者说用到的很少，索引扫描（Index Scan或index lookup）是优化的主要对象
索引扫面分2部，可以想象第一部扫描索引（大部分在内存中）找到rowid，第二步通过rowid定位到文件块，读取，第二步的性能可能会成为瓶颈

SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED] 
INDEX UNIQUE SCAN EMP_I1

极端情况下返回的结果都在第一步，也就是索引中找到，那就不需要第二步了

SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1

4、扫描索引类型:根据where条件不同，产生不同索引策略，其实就是索引和rowid比例的几种关系
[list]
[*]索引唯一扫描(index unique scan)
[*]索引范围扫描(index range scan)
[*]索引全扫描(index full scan)
[*]索引快速扫描(index fast full scan)
[/list]
a、索引唯一扫描(index unique scan) 索引：rowid=1：1

INDEX UNIQUE SCAN EMP_I1

b、索引范围扫描(index range scan) : <索引< 产生范围扫描的原因除了where中有范围查询，还有可能是查询组合索引，但是没有出现引导列

INDEX RANGE SCAN EMP_I1 [ANALYZED]

c、索引全扫描(index full scan):极端。与全表扫描类似，做全表扫描

INDEX FULL SCAN BE_IX [ANALYZED]

d、索引快速扫描(index fast full scan)，与全扫描不同的是不对返回结果排序，可以并行

三.表之间的连接，先看一条sql，后续的例子多按照它讲解

SELECT A.COL1, B.COL2 
FROM A, B
WHERE A.COL3 = B.COL4;
为例进行说明，假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；B表为Row Soruce2，则其对应的连接操作关联列为COL 4；

目前为止，无论连接操作符如何，典型的连接类型共有3种：
[list]
[*]排序 - - 合并连接(Sort Merge Join (SMJ) )
[*]嵌套循环(Nested Loops (NL) )
[*]哈希连接(Hash Join
[/list])这就是开头提到的后面会讲到的排序和潜逃，我们平时join所理解的方式都是循环嵌套，其实还有2种
排序 - - 合并连接(Sort Merge Join, SMJ)
内部连接过程：
1) 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。
2) 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。
3) 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来
下面是连接步骤的图形表示：
MERGE
/\
SORTSORT
||
Row Source 1Row Source 2
排序的例子

SQL> explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;


Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

嵌套循环(Nested Loops, NL)
这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。
内部连接过程：

Row source1的Row 1 ---------------- Probe ->Row source 2
Row source1的Row 2 ---------------- Probe ->Row source 2 
Row source1的Row 3 ---------------- Probe ->Row source 2
…….
Row source1的Row n ---------------- Probe ->Row source 2

从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题
NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。
例子：不加关键字的join应该都是循环的方式join，我个人理解
哈希连接(Hash Join, HJ)

这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。
较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。
例子：

SQL> explain plan for
select /*+ use_hash(emp) */ empno 
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT[CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP

总结一下，在哪种情况下用哪种连接方法比较好：

排序 - - 合并连接(Sort Merge Join, SMJ)：
a) 对于非等值连接，这种连接方式的效率是比较高的。
b) 如果在关联的列上都有索引，效果更好。
c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。

嵌套循环(Nested Loops, NL)：
a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。
b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

哈希连接(Hash Join, HJ)：
a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。
c) 只能用于等值连接中