第十一章（三）-CSDN博客

三、joins
joins就是对多个表数据进行查询。在from子句中有多个表。
where子句中的join关系对表之间的关系进行定义。

1.查询优化器如何执行join语句
为了执行join语句，优化器需要做以下动作
（1）access paths 即找到查询路径
（2）join method 包括nested loop、sort merge、cartesian（笛卡尔积）、hash joins
（3）join order 对于两个以上的表，需要先将两个表合并得到一个行源，再与其他表合并，直到结束

2.查询优化器如何选择合并的执行计划
（1）优化器首先判断两个或多个表是否会直接生成一行数据。主要是通过唯一约束或主键。
如果存在这种情况，优化器先对这些表进行合并操作。
（2）对于含有外连接的join语句，外连接操作的表必须在join其他表之后关联。
优化器不会考虑违反此规则的join。
Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.

通过查询优化器，根据可能的合并顺序、方式和可选的路径生成了一系列执行计划。之后，优化器评价各个执行计划的成本并选择成本最低的。评价的方式为：
（1）嵌套玄幻的成本是根据向内存中读入的外表选定行与内表中匹配行的成本。优化器使用数据字典中的统计信息评估这些成本。
（2）sort merge join的成本更大程度上是基于对所有数据读取并排序的成本。
（3）hash join的成本主要取决于把内部表合并到其他可能表生成的hash表的成本。

优化器还考虑其他因素，如：
排序区较小可能会增加sort merge join的成本，由于排序会花费排序区中大量的cpu时间和i/o。
大的多块读计数会增加sort merge join 与nested loop join 的成本。
如果数据库可以进行大量数据块的读取，那么嵌套循环中的内表上索引相对于全表扫描并不能显著提高性能。多块读的数量multiblock read count取决于初始化参数db_file_multiblock_read_count。

3.nested loop joins
在如下情况下有效：
数据库合并少量数据
合并条件是一种获取行集有效的方法（The join condition is an efficient method of accessing the second table.）

一定要确保内表依赖于外表。
如果内表不依赖于外表，那么对于每一个重复的外层循环都需要读取相同行，不能提高性能。
这种情况下hash joins连接两个行源更加有效。

实验：
scott用户下的emp和dept表存在外键约束，以下语句是上面提到了类似情况，nested loop join比较合适。
按照前面说的，这里emp表应该是外表，dept依赖它得到数据

SQL> select dname from dept,emp where dept.deptno=emp.deptno and emp.ename ='SMITH';

Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

--------------------------------------------------------------------------------
--------

--------------------------------------------------------------------------------
--------

| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00
:00:01 |

| 1 | NESTED LOOPS | | | | |
|

| 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00
:00:01 |

|* 3 | TABLE ACCESS FULL | EMP | 1 | 9 | 3 (0)| 00
:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00
:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("EMP"."ENAME"='SMITH')
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

嵌套循环nested loop join包含两个步骤：
（1）优化器决定驱动表并将其作为外表
（2）其他表被设计为内表
（3）对于外表的每一行，oracle对所有内表行进行访问。外层循环对外表的每行循环，而内层循环对内表每行进行循环。外层循环在内存循环的执行计划之前出现，如下：
NESTED LOOPS
outer_loop
inner_loop

11g中嵌套循环有了新的特性。包含嵌套循环的执行计划可能与原来不同。

使用时机：
对结果集比较小的合并，并且两个表间存在依赖关系。
由于需要通过外循环来找到内循环，因此执行计划中表的顺序是很重要的。
外层循环是驱动行源，产生一系列行来驱动合并关系。
行源可以是通过索引或者全表扫描的，也可以是其他方式。如：嵌套循环的外层也可以作为另一个嵌套循环的驱动。
内层循环重复外层循环的每一行，用索引扫描比较好。
如果内层循环不依赖于外层循环，将会产生笛卡尔积。对于外层循环的每一行，内层循环都匹配。
因此两个表没有依赖关系时应该用其他合并方式。

hint：
可以使用use_nl(table1,table2),table1\table2是表别名。

对于一些语句，数据非常少，优化器选择全表扫描和使用hash join。

4.hash joins
用于合并大数据量。优化器使用小的行源在内存中以合并键值创建一张hash 表（The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory），之后扫描大表，检索hash找到合并行。
这种方式当小表可以在内存中存储时是最好的方式。成本受两个表的串行读限制（The cost is then limited to a single read pass over the data for the two tables.）

使用时机：
在两个表进行等值连接，且大量数据需要合并、小表的很大一部分需要被合并时。

hint：
use_hash

5.sort merge joins
对两个独立的行源进行合并。
hash join一般来讲比merge join高效，除了以下两种情况：
行源已经排序；
不需要排序；
然而，如果sort merge join包含了一种较慢的方式（索引扫描相对于全表扫描），sort merge 的优势就消失了。

在两个表并非等值连接时采用sort merge joins。对于大数据量，sort merge join比nested loop joins高效。
除非等值条件，否则不要使用hash joins。

merge join中没有驱动表的概念，主要包含两个步骤：
sort join operation：输入值都用合并键值排序
merge join operation：排序的列表被合并起来。
如果数据已经排序，就不再排序了。
a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

使用时机：
在如下情况下选择sort merge join而不选择hash join：
非等值连接
由于其他操作需要排序，优化器发现使用sort merge比hash join成本低。

hint：
use_merge强迫使用merge join，可能还需要前置access path

6.笛卡尔积cartesian joins
当一个或多个表与其他表没有关系时，使用笛卡尔积连接。
有些情况下，如果两个非常小的表都与一个大表合并，优化器也可能对其进行笛卡尔积运算。
hint：
ordered

7.outer joins
外合并扩展了简单join的结果。外合并返回所有满足连接条件的行，如果有一个表中没有满足与其他表连接条件的数据，还会返回一些或所有这张表的数据
（The outer join returns the outer (preserved) table rows, even when no corresponding rows are in the inner (optional) table.）

一般对于外合并，优化器根据成本选择哪张作为驱动表。但在嵌套循环的外合并中，合并关系决定表的顺序。
数据库使用外表中满足条件的数据来驱动内层表。

以下情况下优化器会用嵌套循环合并来产生外合并：
可以通过外表驱动内表
数据量很小，嵌套循环更加高效。

hint：use_nl

（1）Hash Join Outer Joins
除了返回满足条件的数据，还返回外层表有，而内层表中匹配键值为空的数据。
如：
SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
GROUP BY cust_last_name;

除了返回credit_limit大于1000的数据，还返回该字段为空的。

（2）Sort Merge Outer Joins
当外表不能驱动外表的保留字到内表的可选项时，不能使用hash join或nested loop joins，而是使用sort merge outer join
优化器在如下情况为外合并使用sort merge：
嵌套循环效率低。那么nested loop join也效率低
优化器发现使用sort merge比hash join高效，由于其他操作需要排序。

（3）full outer joins
完全外连接就像是左外连接和右外连接的合并。
除了内连接外，所有两个表中不都包含的数据也被输出。

11g开始，oracle自动使用一个本地执行方法，在可能时基于完全外连接的hash join。
使用新的方法执行完全外连接是，执行计划包含hash join full outer。

使用native_full_outer_join这个hint可以提示使用hash full outer join。
no_native_full_outer_join取消使用。

四、读懂执行计划
执行计划就是数据库用于执行语句的步骤。包含每个表的access path访问路径，以及表连接的顺序和连接方式。

查看方式：
执行catplan.sql创建调用plan_table的样本输出表
执行explain plan for statement
使用语句或者包调用刚刚执行的语句，查看计划。

执行计划每行对应一个步骤。有星号的步骤号在谓词信息中列出。
每一步或者返回数据，或者处理数据。

五、控制优化行为
文档311页一些可调整的初始化参数

1.开启查询优化器的特性
optimizer_features_enable允许一系列的优化相关特性。
可以利用此参数保留数据库版本升级前的优化行为。如从11.1.0.7升级到了11.2.0.2，optimizer_features_enable参数默认随之更改。
如果不想要新特性，可以修改这个参数到较早版本。

2.选择优化目标
可以设置如下优化目标：
（1）best throughput（默认）最大吞吐量
使用最少的资源执行语句，这通常是生产系统的要求。因为最快响应时间通常不会去测，而吞吐量是有要求的。
（2）best response time
交互式系统比较关注，由于用户在等着结果。

受以下因素影响：
1）optimizer_mode初始化参数
all_rows
first_rows_n
first_rows

可以在session级别更改，如：ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

2）hints的使用
在语句级使用：
first_rows(n)
all_rows

3）使用数据字典的优化统计信息
使用dbms_stats收集信息。对于严重倾斜的数据，应该收集柱状图信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-751312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-751312/