PLSQL_品质优化系列02_Oracle Join关联

2014-09-25 Created By
BaoXinjian

图片 1一、摘要


Oracle三种重点连接格局的可比

  1. Hash Join

    (1).概述

        i.
读取3个表的资料,并将停放到内部存款和储蓄器中,并创制唯一首要字的位图索引

        ii.
读取另1个表,和内部存款和储蓄器中表通过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. 笛Carl连接

无涉及条件,应竭尽防止。

 

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 OUTE大切诺基。

可以用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 一千条,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

相关文章