一． NESTED LOOP:
当一个小的数据集合被连接并且连接条件已等号的方式去连接另一个表时，嵌套循环连接很有用。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。
一点非常重要：内部表是被外部表驱动访问的， 也就说，循环从外部表开始，然后根据外部表的每个值在去全读一次内部表。如果不是这样， 嵌套循环连接会有相当大的性能损耗，这种情况，哈希连接会更好
Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候.
步骤：确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）。需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。
cost = outer access cost + (inner access cost * outer cardinality)
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
EMPLOYEES为outer table, JOBS为inner table.
如下所示：
select count(1) from ta ; ----1000000
select count(1) from tb ; ---18
create index idx_ta_id on ta(id);
create index idx_tb_id on tb(id);
在有索引的情况，优化器将使用嵌套循环连接，结果集较小的表（ta） 做为驱动表，并且根据表的连接顺序无关。
注意： 在Oracle RBO 时代， 由于解析器是从sql语句的后面向前执行的， 所以写在from子句最后的表会先被执行，如果加上过滤条件就会影响到性能上的很大的差距。CBO则不会出现这个情况，它始终将结果集最小的表当做驱动表。
二． HASH JOIN :
哈希连接用于连接大的数据集合。优化器在两个表中或多个数据源中选择一个数据量最小的集合在内存中构造一个含有连接健的结构，然后扫描大表，寻找哈希表对应的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。
也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。
Hash join在两个表的数据量差别很大的时候将被使用.
步骤：将两个表中较小的表根据连接键值构造一个内存HASH表，扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。
cost = (outer access cost * # of hash partitions) + inner access cost
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------
ORDERS为HASH TABLE，ORDER_ITEMS扫描
select count(1) from ta ; ----1000000
select count(1) from tb ; ---18
在没有 索引的情况，优化器将使用哈希连接，结果集较小的表（ta） 做为内存表，并且根据表的连接顺序无关。
评论