HASH JOIN/MERGE JOIN/NESTED LOOP

最新推荐文章于 2020-12-25 14:23:00 发布

chekeyang

最新推荐文章于 2020-12-25 14:23:00 发布

阅读量804

点赞数

分类专栏： ORACLE 文章标签： join nested table access merge loops

ORACLE 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

NESTED LOOP

对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。
一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

HASH JOIN

hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

SORT MERGE JOIN

sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。
通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Nested loop join:

步骤：确定一个驱动表(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.

Hash join

步骤：将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对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扫描

Sort merge join

步骤：将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式：

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.数据源已排序

cost = (outer access cost * # of hash partitions) + inner access cost

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

biti的一段话:

举例，表连接返回一条记录
存在两个表，一个 10条记录，一个1000万条记录

若2表都存在连接字段索引，若以小表为驱动表，则
代价：
10* (通过索引在大表查询一条记录的代价)

若以大表为驱动表：

1000万 * (通过索引在小表中查询一条记录的代价)

通过索引获取一条记录，10rows的表，代价通常在 3 blocks
索引2块，表一块

而如果是1000万的表，索引可能达到4块表一块
这样一来参考上面的计算，你说哪个更好？很显然！

小表查询参考

SQL> create table test as select * from all_objects where rownum < 11;

Table created.

SQL> create index test_index on test(object_id);

Index created.

SQL> select object_id from test;

OBJECT_ID
----------
18159
7781
4841
19891
22549
17099
17712
4287
10107
19135

10 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
736 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> select * from test where object_id = 4287;

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS /1033c8a_SqlTypeWithMethods
4287 JAVA CLASS
14-NOV-00 03-JUL-03 2003-07-03:11:18:19 INVALID N N N

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1157 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

=====================================================

排序 - - 合并连接(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) 只能用于等值连接中。
=====================================================