`

Oracle表连接方式介绍及如何看懂执行计划和理解sql执行原理

阅读更多
在查看sql执行计划时,我们会发现表的连接方式有多种,本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。



一、连接方式:

嵌套循环(Nested Loops (NL))

(散列)哈希连接(Hash Join (HJ))

(归并)排序合并连接(Sort Merge Join (SMJ) )



二、连接说明:

1.Oracle一次只能连接两个表。不管查询中有多少个表,Oracle 在连接中一次仅能操作两张表。

2.当执行多个表的连接时,优化器从一个表开始,将它与另一个表连接;然后将中间结果与下一个表连接,以此类推,直到处理完所有表为止。



三、表连接详解:

1.嵌套循环(Nested Loops (NL)):

嵌套循环实现机制(伪代码):

For r1 in (select rows from table_1 where colx={value})

loop

for r2 in (select rows from table_2 that match current row from table_1)

loop

output values from current row of table_1 and current row of table_2;

end loop;

End loop;

这段代码由两个循环构成。

嵌套循环中的这两个表通常称为外部表(outer table)和内部表(inner table)。

在嵌套循环连接中,外部表又称为驱动表(driver table)

伪代码中:table_1为驱动表,table_2为内表

从伪代码中可以看出该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小结果集的表作为驱动表的原因。

NEST LOOP JOIN COST = 从第一个表取得数据的成本 + 从第一个表得到结果的基数 Х 对第二个表访问一次的成本
所以嵌套循环一般适合于驱动表记录集比较少(<10000)且内表有高效索引访问方式。
使用USE_NL(table_1 table_2)可强制CBO 执行嵌套循环连接。
驱动表确定:驱动表【select rows from table_1 where colx={value} 】一般为根据where条件能得到较小结果集的表,而不一定是整个表记录比较小的表。


2.(散列)哈希连接(Hash Join (HJ)):

Hash join一般用于一张小表和一张大表进行join时。在绝大多数情况下,hash join效率比其他join方式效率更高。

对于hash join的详细理解,可参看一篇文章写的比较透彻:http://www.fengfly.com/plus/view-210419-1.html



3.排序合并连接(Sort Merge Join (SMJ) ):



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

过程:将两个表排序,然后将排序后两个表合并。



四、连接方式总结:

1))嵌套循环(nest loop):

对于被连接的数据子集较小的情况,嵌套循环连接是较好的选择。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(大于10000不合适),要把返回子集较小的表作为外表(驱动表),而且在内表的连接字段上一定要有索引。

2)哈希连接(hash join):

哈希连接是大数据集连接时常用的方式,优化器使用两个表中较小的表,利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。

这种方式适用于较小的表完全可以放入内存的情况,这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段。

哈希连接只能应用于等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。

3)排序合并连接(Sort Merge Join )

通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序归并连接的性能会忧于哈希连接。



五、连接方式应用场景:

1. 哈希连接只适用于等值连接。

2. 嵌套循环是行源连接方式,只适合小量数据连接。

 哈希连接和排序合并连接是集合连接方式,适合大量数据连接。

3. 在等值连接方式下,返回少量记录(<10000)且内部表在连接列上存在索引,适合嵌套循环连接。若返回大量记录则适合哈希连接。

4. 在等值连接方式下,两个行源集合都很大,若连接列是高基数列,则适合哈希连接,否则适合排序合并连接。

5. 嵌套循环连接可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据。而其它两种连接方式则不行。

6. 排序合并连接的两个数据集可以并行处理,而嵌套循环和哈希连接不能.



原文来自:雨枫技术教程网 http://www.fengfly.com
原文网址:http://www.fengfly.com/plus/view-210420-1.html

分享到:
评论

相关推荐

    ORACLE执行计划和SQL调优

    综上所述,Oracle执行计划和SQL调优是一个涉及数据库底层原理、索引策略和查询优化等多个层面的复杂过程。理解和掌握这些知识对于提升数据库性能至关重要。通过深入理解Rowid、Recursive SQL、Row Source和Predicate...

    Oracle执行计划分析

    本文旨在介绍如何生成及分析Oracle SQL执行计划,帮助读者更好地理解其工作原理,从而提高SQL调优的能力。 #### 二、Oracle执行计划基础概念 ##### 2.1 Rowid Rowid是一个特殊的伪列,由系统自动生成而非用户定义...

    Oracle的执行计划

    执行计划是由Oracle的优化器根据SQL语句的内容和数据库的统计信息自动生成的。可以通过SET AUTOTRACE ON命令或者使用EXPLAIN PLAN来查看具体的执行计划。 #### 十、如何分析执行计划 分析执行计划可以帮助我们了解...

    通过分析SQL语句的执行计划优化SQL

    本文将详细介绍如何理解SQL及其执行计划,并给出具体的优化策略。通过优化SQL,可以显著提升应用程序的响应速度及整体性能。 #### SQL的基础理解 SQL(Structured Query Language)是一种用于管理和操作关系型...

    关于Oracle中执行计划稳定性深入研究

    总之,Oracle执行计划的稳定性与数据库性能紧密相关,理解并掌握优化器的工作原理以及如何控制执行计划的选择,是优化数据库性能的关键。在基于代价的优化器模式下,虽然存在执行计划不稳定的可能,但通过合理管理和...

    oracle连接查询SQL性能测试

    提供的文件名看起来像是Oracle的跟踪文件(.trc),这些文件记录了数据库的活动,包括SQL执行细节、执行计划、等待事件等,可用于分析性能问题。通过分析这些文件,可以深入理解查询的执行过程,找出性能瓶颈。 ...

    SQL查询原理及执行顺序

    ### SQL查询原理及执行顺序详解 SQL(Structured Query Language)是一种用于管理关系型数据库的标准语言,其查询原理和执行顺序对于数据库性能优化至关重要。本文将深入探讨SQL查询的执行过程,帮助读者理解如何...

    PL/SQL Developer 远程连接Oracle数据库

    8. **性能监控**:尽管是远程连接,PL/SQL Developer仍然可以显示执行计划、统计信息和性能指标,帮助分析和优化SQL查询。 9. **版本控制集成**:PL/SQL Developer可以与各种版本控制系统(如Git、SVN)集成,这...

    oracle sql_tuning

    1. **SQL执行计划**:SQL执行计划是Oracle解析器为每个SQL语句制定的执行策略,包括表的扫描方式(全表扫描或索引扫描)、连接顺序、排序操作等。理解执行计划有助于找出性能瓶颈并进行优化。 2. **绑定变量**:...

    ORACLE执行计划和SQL调优知识概述.pptx

    Oracle执行计划和SQL调优是数据库管理...总的来说,Oracle执行计划和SQL调优是一个复杂但必要的过程,需要深入理解数据库工作原理,结合实际业务需求和系统负载,才能有效地优化SQL性能,从而提高整体系统的运行效率。

    读懂oracle的执行计划

    在数据库管理系统领域中,Oracle数据库是其中的佼佼者,特别是在企业级应用中。...对于任何希望深化对Oracle数据库优化理解的数据库管理员或开发者来说,学会读懂和分析执行计划是实现高效数据库管理的基础。

    高清完整版 Oracle 高性能SQL引擎剖析SQL优化与调优机制详解

    通过分析执行计划,可以了解SQL语句的执行路径,包括如何访问数据(全表扫描、索引扫描等)、如何进行数据排序、连接等操作。掌握这些信息对优化SQL语句至关重要。 此外,Oracle提供了丰富的SQL调优工具和方法。...

    linux下批量执行oracle脚本的shell脚本

    在Linux环境下,对Oracle数据库进行批量操作是一项常见的任务,尤其对于系统管理员和DBA来说,高效地执行SQL脚本可以显著提升工作效率。本篇将详细讲解如何利用shell脚本来批量执行Oracle数据库脚本,以及涉及的相关...

    oracle sqltuning workshop

    执行计划展示了数据库如何执行SQL语句,包括表的扫描方式(全表扫描或索引扫描)、连接操作、排序和分组等步骤。 3. **性能分析工具**:Oracle提供了如EXPLAIN PLAN、DBMS_XPLAN等工具,帮助我们理解执行计划并识别...

    oracle sql优化

    理解不同连接方式的工作原理,根据数据量和表关系选择最合适的连接方式,能够有效提高查询性能。 四、优化子查询 子查询可能导致多次数据访问,影响性能。考虑使用连接(JOIN)操作替代子查询,或者将子查询转化为...

    Oracle中表的连接及其调整.

    总结来说,理解 Oracle 中的表连接方法和它们的适用场景是提高 SQL 查询性能的关键。通过合理选择连接方式,配合索引策略,我们可以显著减少查询时间,提升数据库应用的响应速度。在实际工作中,应结合具体业务需求...

    ORACLE_SQL性能优化(全).ppt

    分析执行计划可以帮助我们理解SQL语句的执行流程,包括表扫描、索引访问、排序和连接操作。通过调整SQL或数据库设置来改进执行计划,可以显著提升性能。 综上,Oracle SQL性能优化是一个涉及广泛领域的复杂过程,...

    oracle sql语句执行流程解析

    在执行完SQL语句之后,如果该语句将会被再次执行,那么其执行计划可能会被保存到数据高速缓存中,以便下次可以直接使用,省略语法、语义和权限检查的步骤,进一步提高效率。 以上整个流程涵盖了SQL语句从客户端提交...

    Oracle查看SQL执行计划SQL性能分析.docx

    本文将详细介绍如何在Oracle数据库中查看SQL执行计划以及如何利用这些信息来分析SQL性能。 #### 二、查看执行计划的方法 ##### 1. 设置autotrace Autotrace是Oracle提供的一种方便查看SQL执行计划和统计信息的...

Global site tag (gtag.js) - Google Analytics