【SQL 性能优化】表的三种连接方式-白红宇

NESTED LOOP:

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

HASH JOIN :

散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量

提高I/O 的性能。也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。

排序合并连接

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接

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

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

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

| 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 ptimizer=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 ptimizer=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