PLSQL_性能优化系列02_Oracle Join关联

2014-09-25 Created By BaoXinjian

一、摘要

Oracle三种主要连接方式的比较

1. Hash Join

    (1).概述

        i. 读取一个表的资料，并将放置到内存中，并建立唯一关键字的位图索引

        ii. 读取另一个表，和内存中表通过Hash算法进行比较

    (2).适用对象

        i. 大表连接小表

        ii. 两个大表

2. Nested Loops

    (1).概述

        i. 循环外表记录

        ii. 进行逐个比对和内标的连接是否符合条件

    (2).适用对象

        小表驱动大表，返回较少的结果集

3. Merge Join

    (1).概述

        i. 两个表进行table access full

        ii. 对table access full的结果进行排序

        iii. 进行merge join对排序结构进行合并

    (2).适用对象

        通过rowid访问数据

当sql访问多个表时，关联对sql效率就有很重要的影响。关联要考虑两个因素，join的类型和join的次序。

二、Join分类

1. Nested Loop Join方式

1.1 适用条件

(1). 关联少量数据(rows)，返回集小。

(2). 关联条件能高效访问第二张表(inner table)。高效访问的关联条件如'='，反之非高效的关联条件如'!=','>'等;inner table（即非驱动表）上要有索引。

因此比较适合OLTP系统，因为OLTP系统中一般返回数据量小，而且表上面索引较多。

1.2 实现步骤

(1). 优化器选择驱动表(driving table)，指定其为outer table

(2). 指定另一张表为inner table(非驱动表)

(3). 根据outer table的每行记录的关联字段，来访问inner table。如下所示：

NESTED LOOPS

Outer_Loop

Inner_Loop

由于Nested Loop从outer table向inner table查询，关联的次序就比较重要了。

1.3 Nexted Loop Join的例子

2. Hash Join方式

2.1 适用条件

(1). 仅用于等值关联equijoin（如=）;

(2). 满足下列任一条件：

大表关联

或者小表的大部分记录参与关联

2.2 实现机制

(1). 优化器选择较小的表，基于join key构建hash table。（驱动表）

(2). 扫描另外一张较大的表，并在hash table中搜寻关联行

如果内存足够，小表全部在内存中，这种情况是最优的，成本可估算为两张表各一次全表读。

如果内存不够，则小表的一部分可以放在temporary tablespace中（Temp表空间应足够大），以尽可能提高io速度。

2.3 Hash Join的例子

3. Sort merge join

3.1 适用情况

通常情况下hash join性能更好，但如果关联的数据已经排序或不需排序，则sort merge join性能会更好。

非等值关联（nonequi join,如<,> ）时很有用，因为sort merge join在返回集很大时比nested loop性能好，而hash join又只能在equijoin中使用。

3.2 实现机制

(1). Sort操作：关联数据按照关联字段进行排序。如果数据本来就是排序的，就不需此操作

(2). Merge操作：经过排序的数据进行merge操作。

需要说明的是，sort merge join没有driving table的概念

4. 笛卡尔连接

无关联条件，应尽可能避免。

5.  Outer Join

5.1 Simple Join的扩展

customers表称为preserved table,orders表称为optional table

5.2 Outer Join的扩展

• Left outer join
• Right outer join
• Full outer join

5.3 和普通join相比，outer join也可以是nested loop、hash join、sort merge等。但有一些不同之处：

1. Nested Loop Outer Join中，以preserved table作为驱动表，而不是像普通join基于cost来选择驱动表。

2. Full Outer Join（equijoin）在11g中，自动使用基于hash join的算法。执行计划中出现HASH JOIN FULL OUTER。

可以用HINT:NATIVE_FULL_OUTER_JOIN/NO_NATIVE_FULL_OUTER_JOIN来指定使用或不使用这一算法。

如果不使用，则Full Outer Jion的执行计划是Left Outer Join和Right Outer Jion的Union。

三、Join次序

基本原则是：记录少的先关联，这样参与后续关联的记录数就会少。具体来说：

(1). 选择能排除掉最多记录的表作为driving table

(2). 剩余的表中，选选择有最好的filter的表（排除最多记录）作为首先参与关联的表

(3). 以此类推

看这个例子：

SELECT   info
  FROM   taba a, tabb b, tabc c
 WHERE       a.acol BETWEEN 100 AND 200
         AND b.bcol BETWEEN 10000 AND 20000
         AND c.ccol BETWEEN 10000 AND 20000
         AND a.key1 = b.key1
         AND a.key2 = c.key2;

假设a表经过filter后记录最少，b次之，c记录最多。那么可以用a作为driving table,先与b关联，最后与c关联

四、使用Hint选择关联方式和次序

1. 使用hint指定关联方式

Oracle优化器自动选择join的方式，但有时不是最优的，开发人员可使用hint来选择join方式，比较执行效率。

相关的hint有：

• USE_NL,USE_HASH,USE_MERGE
• Exists子句中，HASH_SJ,MERGE_SJ,NL_SJ
• Not in子句中，HASH_AJ,MERGE_AJ，NL_AJ

2. 使用hint指定关联次序

如果oracle优化器选择的关联次序不是你所希望的，可以用hint（leading和ordered）来指定。Ordered表示按照sql语句中表出现的先后次序，leading则可任意指定，更为通用。

Leading指定了driving table的选定次序。（在nested loop中,driving table就是outer table，在hash join中，是hash table。）

SELECT /*+ leading (a b c) */info

WHERE a.acol BETWEEN 100 AND 200

AND b.bcol BETWEEN 10000 AND 20000

AND c.ccol BETWEEN 10000 AND 20000

AND a.key1 = b.key1

AND a.key2 = c.key2;

3. Undocumented hint参数:swap_join_inputs

注意，上面例子中，a作为驱动表和b关联，关联结果作为驱动表，再和c关联。有时需要改变次序,如下面例子

SELECT /*+ leading (a b c)*/ info

WHERE a.key1 = b.key1

AND b.key2 = c.key2;

假如a 1000条，b 10万条，c 1万条。由于a和c表没有关联字段，因此a和b先关联，再和c关联。但a关联b产生2万条记录，和c关联时，希望以c为驱动表，能否实现呢？

在hash_join中可以用oracle的隐含hint参数swap_join_inputs实现：

SELECT /*+ leading (a b c) swap_join_inputs(c) */ info

WHERE a.key1 = b.key1

AND b.key2 = c.key2;

Thanks and Regards

参考：http://blog.itpub.net/18474/viewspace-1060728/

参考：metalink：How to switch the driving table in a hash join [ID 171940.1]