oracle 主键关联 效率,Hash join与nested loops join的效率比较

最新推荐文章于 2022-02-02 12:23:50 发布
最新推荐文章于 2022-02-02 12:23:50 发布
阅读量430 收藏 3
点赞数 1
文章标签： oracle 主键关联 效率

创建测试表

SQL> create table t as select rownum id from dba_objects;

Table created.

SQL> create index idx_t on t(id);

Index created.

SQL> exec dbms_stats.gather_table_stats('sys','t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> create table t1 as select id,'T1' name from t where id<1000;

Table created.

SQL> create index idx_t1 on t1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats('sys','t1',cascade=>true);

PL/SQL procedure successfully completed.

上面的表中t表有7万多行，t1表1000行数据

SQL> set autotrace traceonly;

SQL> select * from t,t1 where t1.id<100 and t.id=t1.id;

99 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3627535484

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 98 | 1176 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 98 | 1176 | 5 (20)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T | 99 | 495 | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T1 | 99 | 693 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T"."ID"="T1"."ID")

2 - access("T"."ID"<100)

3 - filter("T1"."ID"<100)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

13 consistent gets

0 physical reads

0 redo size

2881 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

99 rows processed

从上面看出CBO默认根据统计信息计算使用Hash join方式，逻辑读13

SQL> select /*+ use_nl(t,t1) */ * from t,t1 where t1.id<100 and t.id=t1.id;

99 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 967599162

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 98 | 1176 | 58 (2)| 00:00:01 |

| 1 | NESTED LOOPS | | 98 | 1176 | 58 (2)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T | 99 | 495 | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| T1 | 1 | 7 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."ID"<100)

3 - filter("T1"."ID"<100 AND "T"."ID"="T1"."ID")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

412 consistent gets

0 physical reads

0 redo size

2881 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

99 rows processed

当使用hint强制使用nested join方式逻辑读为412，远远超过了hash join

SQL> select /*+ use_merge(t,t1) */ * from t,t1 where t1.id<100 and t.id=t1.id;

99 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3760646495

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 98 | 1176 | 5 (20)| 00:00:01 |

| 1 | MERGE JOIN | | 98 | 1176 | 5 (20)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T | 99 | 495 | 2 (0)| 00:00:01 |

|* 3 | SORT JOIN | | 99 | 693 | 3 (34)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| T1 | 99 | 693 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."ID"<100)

3 - access("T"."ID"="T1"."ID")

filter("T"."ID"="T1"."ID")

4 - filter("T1"."ID"<100)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

13 consistent gets

0 physical reads

0 redo size

2881 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

99 rows processed

当使用hint强制使用Merge join方式逻辑读与hash join一致，但是多了一次内存排序，因此最优的还是hash join

继续下面实验：

也可以通过hint改变基数的方式走nested loop join

SQL> select /*+ cardinality(t1 1) */ * from t,t1 where t1.id<100 and t.id=t1.id;

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 12 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_T | 1 | 5 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------

从这里就可以看出在连接一个小表的时候CBO会选择nested loop join

SQL> delete t1 where id>10;

989 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)

----------

10

SQL> select * from t,t1 where t.id=t1.id;

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1557655968

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 110 | 12 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 10 | 110 | 12 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T1 | 10 | 60 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_T | 1 | 5 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T"."ID"="T1"."ID")

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

804 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

从上面看出当连接一张小表的时候，CBO计算出使用nested loops join方式

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

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1444793974

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 110 | 35 (3)| 00:00:01 |

|* 1 | HASH JOIN | | 10 | 110 | 35 (3)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T1 | 10 | 60 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T | 73961 | 361K| 32 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T"."ID"="T1"."ID")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

122 consistent gets

0 physical reads

0 redo size

804 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

当我们强制使用hash join 可以看到逻辑读增加到了122，相差100多

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

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4143939902

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 110 | 169 (2)| 00:00:03 |

| 1 | MERGE JOIN | | 10 | 110 | 169 (2)| 00:00:03 |

| 2 | INDEX FULL SCAN | IDX_T | 73961 | 361K| 166 (1)| 00:00:02 |

|* 3 | SORT JOIN | | 10 | 60 | 3 (34)| 00:00:01 |

| 4 | TABLE ACCESS FULL| T1 | 10 | 60 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T"."ID"="T1"."ID")

filter("T"."ID"="T1"."ID")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

804 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

10 rows processed

使用merge join 逻辑读最低，但是多了一次内存排序，CBO最终还是选择了nested loops join

SQL> drop index idx_t ;

Index dropped.

SQL> exec dbms_stats.gather_table_stats('sys','t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from t,t1 where t.id=t1.id;

20 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1444793974

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20 | 220 | 67 (3)| 00:00:01 |

|* 1 | HASH JOIN | | 20 | 220 | 67 (3)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T1 | 10 | 60 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T | 147K| 722K| 64 (2)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T"."ID"="T1"."ID")

Statistics

----------------------------------------------------------

135 recursive calls

0 db block gets

250 consistent gets

0 physical reads

0 redo size

1097 bytes sent via SQL*Net to client

535 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

20 rows processed

如上当删除索引后cbo会选择hash join

总结：当多表连接的时候，如果是大表之间hash join速度最快，如果其中一个表相对来说比较小，并且关联字段上有索引的时候nested loops 速度比较快，上面实验也证实了如果CBO判断基数不准确会导致错误的执行计划。

睡棉絮的人
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql 8.0.18 hash join测试(推荐)
09-09
给大家介绍Mysql 8.0.18 hash join测试的相关知识，本文通过实例代码讲解，非常不错，具有一定的参考借鉴价值，需要的朋友参考下吧
hash join、merge join、nested loop的原理演示及性能对比
hualusiyu的专栏
01-10 845
一、适合hash join的情况 1） SQL演示: SQL> create table tb as select * from dba_objects; 表已创建。//创建大表 SQL> create table ts as select * from dba_objects where rownum 表已创建。//创建小表 SQL> select tb.* from tb,ts w
nested loops 和hash join的一点测试
congji3615的博客
04-11 80
第一种情况： 表上没有索引 ，cbo自动选择 hash join SQL> select count(*) from t; COUNT(*)---------- 50371 SQL> sele...
Hash join与nested loops join的效率比较
weixin_34417183的博客
10-14 463
创建测试表SQL> create table t as select rownum id from dba_objects;Table created.SQL> create index idx_t on t(id);Index created.SQL> exec dbms_stats.gather_table_stats('sys','t',cascade...
Hash Join 与 Nested-Loop Join
基于Oracle11G
07-08 3188
总所周知，Oracle数据库常用的两种优化器：RBO（rule-based-optimizer）和CBO(cost-based-optimizer)。目前更多地采用CBO(cost-based-optimizer)基于开销的优化器。在CBO方式下，Oracle会根据表及索引的状态信息来选择计划；在RBO方式下，Oracle会根据自己内部设置的一些规则来决定选择计划。
oracle联合主键效率,oracle添加联合主键和oracle时间加减
weixin_35965051的博客
04-03 134
oracle添加联合主键alter table tablename add constraint unionkeyname primary key (column1,column2);上面语句中：tablename为要添加联合约束的表unionkeyname为添加的联合约束的名称column1，column2为联合主键作用的两个列列名oracle时间加减加法select sysdate,add_m...
oracle 表连接方式： nested loop 嵌套循环和Hash Join的比较
热门推荐
daf380的专栏
10-08 1万+
一、创建两张实验用表：wireless_site.merchant和wireless_site.bb SQL&gt; select count(*) from wireless_site.merchant;   COUNT(*) ----------      14005 SQL&gt; SQL&gt; select count(*) from wireless_site.clickthr...
HASH JOIN ,MERGE JOIN ,NESTED LOOP用法效率比较
我思，故我在！--My data life
12-19 1万+
概述： NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL
HASH JOIN 跟NESTED LOOP 快一个列子
jinghuainfo
05-06 63
在报表库的每个小时数据量23113条时候 NESTED LOOP 要跑3分钟 而 HASH JOIN 才7秒钟 NESTED LOOP: SELECT * FROM ( SELECT ACTIONID, OPCONTENTID, MOUDULEID, PORTALID, 'lars01.OP_MAIL_VISIT_04_1105' AS table_name, T...
MySQL 8.0.18 Hash Join不支持left/right join左右连接问题
01-19
在MySQL 8.0.18中，增加了Hash Join新功能，它适用于未创建索引的字段，做等值关联查询。在之前的版本里，如果连接的字段没有创建索引，查询速度会是非常慢的，优化器会采用BNL（块嵌套）算法。 Hash Join算法是把...
MySQL 8.0 新特性之哈希连接（Hash Join）
09-09
MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本，带来了一些新特性和增强功能。这篇文章主要介绍了MySQL 8.0 新特性之哈希连接（Hash Join）,需要的朋友可以参考下
Oracle中hash join研究.pdf
10-09
Oracle中hash join研究.pdf
MySQL中Nested-Loop Join算法小结
09-10
数据库中JOIN操作的实现主要有三种：嵌套循环连接（Nested Loop Join），归并连接（Merge Join）和散列连接或者哈稀连接（Hash Join）。其中嵌套循环连接又视情况又有两种变形：块嵌套循环连接和索引嵌套循环连接。
Oracle典型的表连接类型（sort merge join(SMJ) 排序-合并连接、nested loops(NL) 嵌套循环、hash join 哈希连接）
林世广
02-02 2178
1、sort merge join(SMJ) 排序-合并连接 连接过程： 将两个表排序，然后再进行join ①首先生产drving table【驱动表】需要的数据，然后对这些数据按照连接操作关联列进行排序； ②然后生产probed table【被探查表】需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序； ③最后两边已经排序的行被放在一起执行合并操作。 应用场景： ①排序是一个费时、费资源的操作，特别对于大表。所以SMJ通常不是一个特别有效的连接方法，但是如果driving
oracle hash连接语法,oracle 多表连接的三种方式 HASH JOIN 、MERGE JOIN 、NESTED LOOP
weixin_39761422的博客
04-08 785
1、NESTED LOOP(嵌套循环连接)对于被连接的数据子集较小的情况。1)、 过程：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个二重循环。2)、原理：for(int i=0;i<=5;i++) {for(int j=0;j<=10;j++) {System.out.println("伪代码");}}nested loop join的原理就是这个，只...
ORACLE 连接方式 NESTED LOOP、HASH JOIN
ado1986的专栏
03-07 1466
转载自：http://zl198751.iteye.com/blog/992795 ORACLE 连接方式 NESTED LOOP      对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。  一般在nested loop
oracle的表连接hash join、nested loop join
cupid1102的专栏
05-22 4823
今天打算对hash join和nested loop join的原理及使用做下记录，每次总结完，过一段时间就有写模糊了：   一、hash join原理：        hash join只能在等值连接下使用，且只有在CBO优化器才能使用；        其思想是将小的表（内表）存在于hash area内存中做成hash table，之后大表（外表）进行全扫描，每条记录都在连接的值上做ha
Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）
张冲andy的博客
04-28 1979
nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所有数据逐条进行比较（也是等值连接，也可以是非等值连接），如果符合规则，就放在结果集中，然后取驱动表的下一条数据继续循环，直到结束。 简单的理解就是：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个
Oracle nested loops 怎么优化
最新发布
05-30
如果内部表的数据量比较大，可以考虑使用Hash Join代替Nested Loops。Hash Join是一种更高效的连接方式，它可以将内部表中的数据进行哈希处理，从而快速匹配外部表中的记录。 4. 调整Oracle参数。在Oracle中，有...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值