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

2014-09-25 Created By
BaoXinjian

图片 1一、摘要


Oracle三种关键连接方式的较

  1. Hash Join

    (1).概述

        i.
读取一个申明的素材,并将停到外存中,并建立唯一重要字之号图索引

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

    (2).适用对象

        i. 大表连接小表

        ii. 两单大表

  1. Nested Loops

    (1).概述

        i. 循环外表记录

        ii.
进行依次比对和内标的连是否符合条件

    (2).适用对象

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

  1. Merge Join

    (1).概述

        i. 两单说明进行table access
full

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

        iii. 进行merge
join对排序结构进行联

    (2).适用对象

        通过rowid访问数

 

当sql访问多单表时,关联对sql效率就是起老关键之震慑。关联而考虑少独要素,join的花色及join的次第。

 

图片 2二、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的例子

图片 3

 

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的例子

图片 4

 

 

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的扩展

图片 5

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。

 

图片 6三、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关联

 

图片 7季、使用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;

  1. 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]

图片 8

相关文章