Oracle 三种表关联方式的总结, 使用hint来验证

最新推荐文章于 2023-10-06 12:53:22 发布
最新推荐文章于 2023-10-06 12:53:22 发布
阅读量3.8k 收藏 20
点赞数 3
分类专栏： 数据库/Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/icejasmin/article/details/9812897
版权

最近认真参加Dataguru的Oracle培训，正经学了一些东西，有时间就整理一下放到这里，以便以后学习。

今天总结一下三种表关联方式的适用场景。

1. Nested Loop

原理：从外部表中拿数据，去内部表中去比对

适用场景：

1）关联中有一个表比较小

2）被关联表的关联字段上有索引

3）索引的键值重复率不高

例子：t表为大表，d表为小表，t上有索引。

SQL> create table d as select * from dba_objects where rownum<100;

SQL> select * from t, d where t.object_id=d.object_id;


-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    99 | 31482 |   102   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |      |    99 | 31482 |   102   (0)| 00:00:02 |
|   2 |   TABLE ACCESS FULL          | D    |    99 | 22275 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    93 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

相同sql使用Hash Join的结果:

SQL> select /*+ use_hash(t d) */ * from t,d where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 68162843

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 | 31482 |   172   (2)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    99 | 31482 |   172   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| D    |    99 | 22275 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    | 53023 |  4815K|   168   (2)| 00:00:03 |
---------------------------------------------------------------------------

相同sql使用Merge Join的结果：

SQL> select /*+ use_merge(t d) */ * from t,d where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1512134595

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    99 | 31482 |  1008   (1)| 00:00:13 |
|   1 |  MERGE JOIN                  |      |    99 | 31482 |  1008   (1)| 00:00:13 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    | 53023 |  4815K|  1004   (1)| 00:00:13 |
|   3 |    INDEX FULL SCAN           | T_PK | 53023 |       |   112   (1)| 00:00:02 |
|*  4 |   SORT JOIN                  |      |    99 | 22275 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | D    |    99 | 22275 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


2.Hash Join

原理：优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。

适用场景：

1）一个大表一个小表的关联

2）表上没有索引 （关联表上没有索引，CBO不会考虑使用Nested Loop)

3）返回结果集比较大

例子：去掉t表索引

SQL> alter table t drop primary key;

Table altered.

SQL> select * from t, d where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 68162843

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 | 31482 |   172   (2)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    99 | 31482 |   172   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| D    |    99 | 22275 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    | 53023 |  4815K|   168   (2)| 00:00:03 |
---------------------------------------------------------------------------
相同sql使用Nested Loop

SQL> select /*+ use_nl(t d) */ * from t,d where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3796256697

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 | 31482 | 16448   (2)| 00:03:18 |
|   1 |  NESTED LOOPS      |      |    99 | 31482 | 16448   (2)| 00:03:18 |
|   2 |   TABLE ACCESS FULL| D    |    99 | 22275 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T    |     1 |    93 |   166   (2)| 00:00:02 |
---------------------------------------------------------------------------

相同sql使用Merge Join

QL> select /*+ use_merge(t d) */ * from t,d where t.object_id=d.object_id;

xecution Plan
---------------------------------------------------------
lan hash value: 3365178606

-----------------------------------------------------------------------------------
 Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
   0 | SELECT STATEMENT    |      |    99 | 31482 |       |  1311   (1)| 00:00:16 |
   1 |  MERGE JOIN         |      |    99 | 31482 |       |  1311   (1)| 00:00:16 |
   2 |   SORT JOIN         |      |    99 | 22275 |       |     4  (25)| 00:00:01 |
   3 |    TABLE ACCESS FULL| D    |    99 | 22275 |       |     3   (0)| 00:00:01 |
*  4 |   SORT JOIN         |      | 53023 |  4815K|    12M|  1307   (1)| 00:00:16 |
   5 |    TABLE ACCESS FULL| T    | 53023 |  4815K|       |   168   (2)| 00:00:03 |
-----------------------------------------------------------------------------------


3.Merge Join

原理：将两个结果集分别排序，对排序后的结果集进行连接

适用场景：当结果集已经排过序

例子：先排序，再关联

SQL> select * from (select * from t order by object_id) t, (select * from d order by object_id) d
  2  where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2926044903

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    99 | 43758 |       |  1311   (1)| 00:00:16 |
|   1 |  MERGE JOIN          |      |    99 | 43758 |       |  1311   (1)| 00:00:16 |
|   2 |   VIEW               |      | 53023 |    10M|       |  1307   (1)| 00:00:16 |
|   3 |    SORT ORDER BY     |      | 53023 |  4815K|    12M|  1307   (1)| 00:00:16 |
|   4 |     TABLE ACCESS FULL| T    | 53023 |  4815K|       |   168   (2)| 00:00:03 |
|*  5 |   SORT JOIN          |      |    99 | 22275 |       |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | D    |    99 | 22275 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

相同sql使用Nested Loop

SQL> select /*+ use_nl(t d) */ * from (select * from t order by object_id) t, (select * from d order by object_id) d
  2  where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1008542415

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    99 | 43758 |       | 59577   (1)| 00:11:55 |
|   1 |  SORT ORDER BY        |      |    99 | 43758 |       | 59577   (1)| 00:11:55 |
|   2 |   NESTED LOOPS        |      |    99 | 43758 |       | 59576   (1)| 00:11:55 |
|   3 |    VIEW               |      | 53023 |    10M|       |  1307   (1)| 00:00:16 |
|   4 |     SORT ORDER BY     |      | 53023 |  4815K|    12M|  1307   (1)| 00:00:16 |
|   5 |      TABLE ACCESS FULL| T    | 53023 |  4815K|       |   168   (2)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL  | D    |     1 |   225 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

相同sql使用Hash Join

SQL> select /*+ use_hash(t d) */ * from (select * from t order by object_id) t, (select * from d order by object_id) d
  2  where t.object_id=d.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3786187078

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    99 | 43758 |       |  1312   (1)| 00:00:16 |
|   1 |  SORT ORDER BY        |      |    99 | 43758 |       |  1312   (1)| 00:00:16 |
|*  2 |   HASH JOIN           |      |    99 | 43758 |       |  1311   (1)| 00:00:16 |
|   3 |    TABLE ACCESS FULL  | D    |    99 | 22275 |       |     3   (0)| 00:00:01 |
|   4 |    VIEW               |      | 53023 |    10M|       |  1307   (1)| 00:00:16 |
|   5 |     SORT ORDER BY     |      | 53023 |  4815K|    12M|  1307   (1)| 00:00:16 |
|   6 |      TABLE ACCESS FULL| T    | 53023 |  4815K|       |   168   (2)| 00:00:03 |
--------------------------------------------------------------------------------------



icemaple_dl
关注
专栏目录
【Oracle】Oracle优化５３个规则详解
tttzzzqqq2018的博客
10-17 904
Oracle优化５３个规则详解
DBA必备知识：Oracle面试题及参考答案（3万字长文）
最新发布
大模型大数据攻城狮的专栏
04-12 291
在Oracle PL/SQL中，异常处理是一种错误处理机制，它允许程序在遇到错误时优雅地恢复。自定义异常处理可以提高程序的健壮性和可维护性。实现自定义异常处理的步骤：数据库重定义（Redefinition）是Oracle数据库中的一个高级特性，它允许在不卸载数据的情况下修改表或索引的结构。数据库重定义的用途：注意事项：修改数据类型：可以使用数据库重定义来更改列的数据类型，而不需要传统的ALTER TABLE...MODIFY操作。添加或删除列：可以添加新列或删除现有列，而不影响表中的数据。
几种常用的表连接方式
07-08
文档中描述了 ，几种常用的数据库的 表连接方式。
【Oracle】三种表连接方式
Just Do IT
03-06 2452
表连接的方式有三种分别是：排序合并连接（Sort Merge Join）、嵌套循环连接（Nested Loops Join）、哈希连接（Hash Join）。 1、 排序合并连接（Sort Merge Join） 排序合并连接的执行过程如下所示： * 将每个行源的行按连接谓词列排序 * 然后合并两个已排序的行源，并返回生成的行源 例如： select * from employee
三种类型的表关联hash join和merge join和nested loop详解
rede
02-24 6599
https://blog.csdn.net/gao1440156051/article/details/52388644 多表之间的连接有三种方式： Nested Loops Hash Join Sort Merge Join 一． NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行...
ORACLE的三中连接方法
cyxlxp8411的专栏
07-29 736
ORACLE的三中连接方法 1. NESTED LOOP 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。一般在nested loop中， 驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记
数据库表关联的三种方式
zhangqinghao9203的专栏
09-12 6870
数据库表关联的三种方式  (2012-01-20 14:01:15) 转载▼ 标签：  oracle   数据表关联   hash   杂谈 分类： BI理论 NESTED LOOP     对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表
性能优化技巧：有序定位关联提速主子关联后的过滤
Raqsoft
06-12 302
一、问题背景与适用场景 在《性能优化技巧：有序归并》中我们验证了有序归并算法提高关联性能的效果，那么还有没有进一步提升的空间呢？能不能再减少数据的读取量从而再提速呢？ 通常主子关联后还会再施加更多运算，比如过滤。有序归并算法将主子表先分别读出再做关联，当其中一个关联表被过滤后剩下记录少了很多时，另一个关联表也会有大量记录不能关联上，但做归并关联之前并不知道，仍然会将这些不可能关联上的数据读出，浪费了时间。 如果我们能根据被过滤表的主键去另一个表查找可被关联记录再来做关联，那样就能省去很多读取...
Oracle sql优化
扶de起的阿斗 的博客
09-10 445
Oracle sql性能优化 1.SQL语句执行步骤 语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划” 2.选用适合的Oracle优化器 RULE（基于规则） COST（基于成本） CHOOSE（选择性） 3.访问Table的方式 全表扫描 全表扫描就是顺序地访问表中每条记录，ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 通过ROWI
Oracle面试题集锦（2020大厂秋招真题）
数字成都
03-19 1万+
1，范式 7大范式：1NF, 2NF,3NF,BCNF,4NF,5NF,6NF 什么叫normalization？Denormalization? Normalization是数据库规范化，denormalization是数据库逆规范化。 在设计和操作维护数据库时，关键的步骤就是要确保数据正确地分布到数据库的表中。使用正确的数据结构，不仅便于对数据库进行相应的存取操作，而且可以极大地简化应用程序的...
Oracle数据库3种主要表连接方式对比
03-31
Oracle数据库3种主要表连接方式各自优劣对比
oracle表之间的关联方式
chenlin5188的专栏
06-14 4726
  oracle表之间的关联方式多表之间的连接有三种方式：NestedLoops，Hash Join 和 Sort Merge Join.   一． NESTED LOOP:   对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小...
oracle三种关联机制,【PL/SQL数据库】 三种关联机制 - 执行计划
weixin_39923623的博客
04-03 3029
看完这章你会学习到以下内容：1. 三种关联分别是什么？2. 什么时候那个适合用哪个？(总结)一共有三种关联机制：Nest Loop 嵌套循环 (大小表)Sort Merge 排序合并 (添加索引+两张大表)Hash Join 哈希 (内联)嵌套循环对上图进行简单的解释，在进行嵌套循环的时候，通常情况是(大表+小表)的形式，并且会以小表作为内表，因为小表可以常驻内存，这样当进行磁盘IO的时候，只有大...
【DB笔试面试592】在Oracle中，表和表之间的关联方式有哪几种？
小麦苗DBA宝典
07-11 2780
♣题目部分在Oracle中，表和表之间的关联方式有哪几种？ ♣答案部分目前为止，无论连接操作符如何，典型的连接类型共有3种：①　排序合并连接（Sort Merge...
ORACLE表关联
TouchYoni
01-26 8262
内连接（inner_cross_join） INNER_JOIN ： 内连接即关联多张表时根据连接条件筛选符合条件的数据（书写时可以省略inner关键字） 外连接（outer_join） 在外连接中又分为三类，主要有左外连接(left_outer_join)、右外连接(right_outer_join)、全外连接(full_outer_join)：
Oracle关联机制
weixin_57024726的博客
10-06 1895
首先两个表关联后,小表为内侧表,大表作为外侧表,确定外侧表和内侧表的连接后,将外侧表作为驱动表,内侧表作为被驱动表,根据驱动表的连接条件进行筛选.最后获得驱动表的数据合集.-- 两张数据相差不大,并且还是等值关联.在关联的时候,会先对关联字段做哈希值计算,计算完,再拿哈希值匹配./*+ PARALLEL(表名1,并行数)[(表名2,并行数)……-- 先对关联字段做一个排序,再拿排好顺序的数据做关联,……/*+ USE_MERGE(表名1，表名2) *//*+ USE_HASH(表名1，表名2) */
Oracle表连接（转）
dfbmd42668的专栏
04-10 113
表之间的连接 Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。在后面的叙述中，我们将会使用”row source”来代替”表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 2。Join过程的各个步骤经常是串行操作，即使相关的row source可以被并行访...
Oracle 表连接(内连接、外连接(左连接、右连接)、隐式连接、表并集、表交集、表补集)
热门推荐
weixin_42321155的博客
09-03 1万+
Oracle 表连接、内连接、外连接(左连接、右连接、全连接）、隐式连接、表并集、表交集、表补集
6-oracle_表关联
chongzhi0705的博客
03-23 152
表关联是对2个表的列合并，怎么合并呢，这里需要一个连接信息，也就是关联字段，比如一个学生表里有学号+班级+出生日期，另一个表里有学号+科目+成绩，哪如果需要得到学号+班级+科目+成绩这样的信息，这就需要表关联，关联的字段用什么呢，只能用这2个表共同相关的字段，也就是学号这个字段，这样就能把2个...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值