Oracle中表连接方式(Nested Loop、Hash join)对于表访问次数的测试

简介: 介绍了sql多表连接的几种方式,如有不正确的地方请指正。

        平时写SQL遇到多表关联的情况经常见到,这也是关系型数据库最大的优势之一。表连接类型可以分为Nested Loops join、hash join、Merge Sort Join三类。每一类都有各自的使用场景,sql语句在数据库中生成执行计划,数据库中优化器会根据代价去判断选择哪种方式。Merge Sort Join 的表访问次数和 Hash Join 是类似的。下面测试Nested Loop、Hash join这两种方式执行时对于表的访问次数。

1、构造测试环境

①创建表test1、test2

SYS@vbox66in>create table test1 (
  2  id number not null,
  3  num number,
  4  val varchar2(100));

表已创建。

SYS@vbox66in>
SYS@vbox66in>create table test2 (
  2  id number not null,
  3  t1_id number not null,
  4  num number,
  5  val varchar2(100));

表已创建。

SYS@vbox66in>

②插入数据

SYS@vbox66in>exec dbms_random.seed(0);

PL/SQL 过程已成功完成。

SYS@vbox66in>insert into test1 
 2     select rownum,rownum,dbms_random.string('a',50) from dual
 3       connect by level <= 100
 4         order by dbms_random.random;

已创建 100 行。

SYS@vbox66in>
SYS@vbox66in>insert into test2
  2    select rownum,rownum,rownum,dbms_random.string('a',50) from dual
  3      connect by level <= 10000
  4        order by dbms_random.random;

已创建 10000 行。

SYS@vbox66in>commit;

提交完成。

SYS@vbox66in>

2、表访问次数测试

①Nested Loops join方式

        Nested Looped join中,驱动表被访问0次或1次,被驱动表被访问0次或N次,N由驱动表返回的结果集条数来决定,下面通过4种情况来测试。
        在测试之前设置一些内容,修改参数statistics_level=all的方式来查看sql语句的执行计划,查看sql语句执行计划方式有多种,这里不做详细介绍;执行set linesize 1000,set linesize 1000对dbms_xplan.display_cursor还是有影响的,如果没有设置,默认情况下输出将会少人多列,如BUFFERS等。

A、第一种情况,test2被访问100次(驱动表被访问1次,被驱动表被访问100次)


SYS@vbox66in>select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* 
 2  from test1,test2 
 3    where test1.id=test2.t1_id;

---查询结果省略
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2ajdvtjv469rm, child number 0
-------------------------------------
select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.t1_id

Plan hash value: 2336902100

--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    100 |00:00:00.12 |    9917 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |       |      1 |    100 |    100 |00:00:00.12 |    9917 |
|   2 |   TABLE ACCESS FULL| TEST1 |      1 |    100 |    100 |00:00:00.01 |      10 |
|*  3 |   TABLE ACCESS FULL| TEST2 |    100 |      1 |    100 |00:00:00.12 |    9907 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TEST1"."ID"="TEST2"."T1_ID")

Note

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)
已选择25行。

SYS@vbox66in>

image

            /+ leading(test1) use_nl(test2) /这个表示以test1作为驱动表,连接方式为Nested Loops join。从执行计划可以看出(starts表示表被访问的次数),test1表被访问了1次,test2表被访问了100次。因为test1作为驱动表返回了100条数据,所以被驱动表被访问了100次。

B、第二种情况

SYS@vbox66in>select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* 
  2  from test1,test2 
  3    where test1.id=test2.t1_id 
  4      and test1.id in (20,30);
--查询结果省略
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c2y038hqtjqg6, child number 0
-------------------------------------
select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.t1_id     and test1.id in (:"SYS_B_0",:"SYS_B_1")

Plan hash value: 2336902100

--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      2 |00:00:00.01 |     203 |
|   1 |  NESTED LOOPS      |       |      1 |      1 |      2 |00:00:00.01 |     203 |
|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  3 |   TABLE ACCESS FULL| TEST2 |      2 |      1 |      2 |00:00:00.01 |     199 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("TEST1"."ID"=:SYS_B_0 OR "TEST1"."ID"=:SYS_B_1))
   3 - filter((INTERNAL_FUNCTION("TEST2"."T1_ID") AND

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          "TEST1"."ID"="TEST2"."T1_ID"))

Note
-----
   - dynamic sampling used for this statement (level=2)
已选择28行。

SYS@vbox66in>

image
        从执行计划来看,test1作为驱动表被访问了1次返回了2行,被驱动表test2被访问了2次,结果和上次类似。

C、第三种情况

SYS@vbox66in>select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* 
  2  from test1,test2 
  3    where test1.id=test2.t1_id 
  4      and test1.num = 789456123;

未选定行

SYS@vbox66in>
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fh6zpk6pbmmp8, child number 0
-------------------------------------
select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.t1_id     and test1.num = :"SYS_B_0"

Plan hash value: 2336902100

--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |       |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  3 |   TABLE ACCESS FULL| TEST2 |      0 |      1 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST1"."NUM"=:SYS_B_0)
   3 - filter("TEST1"."ID"="TEST2"."T1_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
已选择26行。

SYS@vbox66in>

image
        sql语句where条件加了test1.num = 789456123,实际这条数据不存在。观察执行计划,test1作为驱动表被访问了1次,预测返回1条数据,结果返回0条(E-Rows表示预测返回的数据行,A-Rows表示实际返回的数据行),由于驱动表返回0行数据,所以被驱动表被访问0次。

D、第四种情况

SYS@vbox66in>select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* 
  2  from test1,test2 
  3    where test1.id=test2.t1_id 
  4      and 1 = 2;

未选定行

SYS@vbox66in>
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d9hvdrafbz5wt, child number 0
-------------------------------------
select /*+ leading(test1) use_nl(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.t1_id     and :"SYS_B_0" = :"SYS_B_1"

Plan hash value: 3924076509

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      0 |00:00:00.01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |       |      1 |        |      0 |00:00:00.01 |
|   2 |   NESTED LOOPS      |       |      0 |    100 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| TEST1 |      0 |    100 |      0 |00:00:00.01 |
|*  4 |    TABLE ACCESS FULL| TEST2 |      0 |      1 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_0=:SYS_B_1)
   4 - filter("TEST1"."ID"="TEST2"."T1_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
已选择27行。

SYS@vbox66in>

image
        sql语句种加了1 = 2这个条件,这个条件根本不成立,所以 t1 表根本无须访问,直接通过访问数据字典,获取到两表的结构就好了,观察执行计划也可以看到test1和test2均没有被访问。

②Hash join方式

        Hash join中,驱动表被访问0次或1次,被驱动表也是被访问0次或1次,绝大部分场景下是驱动表和被驱动表各被访问1次。

A、第一种情况

SYS@vbox66in>select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* 
  2  from test1,test2
  3    where test1.id=test2.id;
---查询结果省略
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1t2sys8m18yj1, child number 0
-------------------------------------
select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.id

Plan hash value: 497311279

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    100 |00:00:00.06 |     109 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |       |      1 |    100 |    100 |00:00:00.06 |     109 |   964K|   964K| 1261K (0)|
|   2 |   TABLE ACCESS FULL| TEST1 |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| TEST2 |      1 |   9622 |  10000 |00:00:00.02 |     106 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TEST1"."ID"="TEST2"."ID")

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)
已选择25行。

SYS@vbox66in>

image

        sql语句中添加了hint:/+ leading(test1) use_hash(test2) /。leading表示将test1作为驱动表,use_hash表示表连接方式为hash。从执行计划中可以查到,test1作为驱动表被执行了1次实际返回了100条数据,test2作为被驱动表也被执行了一次,放回了10000条数据。从这里看以看出hash join方式表访问的次数和Nested Loops join不同。

B、第二种情况

SYS@vbox66in>select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* 
  2  from test1,test2
  3    where test1.id=test2.id
  4      and test1.num = 987654321;

未选定行

SYS@vbox66in>
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  69x6y0z2nhr4a, child number 0
-------------------------------------
select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.id     and test1.num = :"SYS_B_0"

Plan hash value: 497311279

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |00:00:00.01 |       3 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |       |      1 |      1 |      0 |00:00:00.01 |       3 |   876K|   876K|  183K (0)|
|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| TEST2 |      0 |   9622 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TEST1"."ID"="TEST2"."ID")
   2 - filter("TEST1"."NUM"=:SYS_B_0)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
已选择26行。

SYS@vbox66in>

image
        sql与语句中添加了test1.num = 987654321条件,test1中没有这行数据,所以返回0行。查看执行计划,test1作为驱动表被访问一次,返回0行数据,被驱动表test2被访问0次。

C、第三种情况

SYS@vbox66in>select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* 
  2  from test1,test2
  3    where test1.id=test2.id
  4      and 1 = 2;

未选定行

SYS@vbox66in>
SYS@vbox66in>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxbhu6tb8q5nk, child number 0
-------------------------------------
select /*+ leading(test1) use_hash(test2) */ test1.*,test2.* from test1,test2   where test1.id=test2.id     and :"SYS_B_0" = :"SYS_B_1"

Plan hash value: 4084539893

--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      0 |00:00:00.01 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |       |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN         |       |      0 |    100 |      0 |00:00:00.01 |   876K|   876K|          |
|   3 |    TABLE ACCESS FULL| TEST1 |      0 |    100 |      0 |00:00:00.01 |       |       |          |
|   4 |    TABLE ACCESS FULL| TEST2 |      0 |   9622 |      0 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_0=:SYS_B_1)
   2 - access("TEST1"."ID"="TEST2"."ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
已选择27行。

SYS@vbox66in>

image
        sql语句中加了1 = 2的条件,这种情况不可能成立,所以 test1 表根本无须访问。查看执行计划,驱动表test1被访问0次,被驱动表也被访问0次。

相关文章
|
2天前
|
SQL Oracle 关系型数据库
避坑,Oracle中left join 与 (+) 的区别
避坑,Oracle中left join 与 (+) 的区别
|
2天前
|
SQL 安全 关系型数据库
接上篇文章,在测试宝塔 WAF 的未授权访问漏洞时无意间还发现了一个 SQL 注入漏洞
接上篇文章,在测试宝塔 WAF 的未授权访问漏洞时无意间还发现了一个 SQL 注入漏洞,品相还不错,可执行任意 SQL 语句。 总之,吃了一惊,一个防 SQL 注入的工具居然也有 SQL 注入漏洞。 请看这段代码
434 1
|
3天前
|
移动开发 前端开发 JavaScript
VSCode设置类似Webstorm那样可以用本地局域网IP地址访问自己开发的测试项目,vs code 前端如何以服务器模式打开?
VSCode设置类似Webstorm那样可以用本地局域网IP地址访问自己开发的测试项目,vs code 前端如何以服务器模式打开?
VSCode设置类似Webstorm那样可以用本地局域网IP地址访问自己开发的测试项目,vs code 前端如何以服务器模式打开?
|
2天前
|
SQL Oracle 关系型数据库
oracle11g SAP测试机归档日志暴增排查(二)
oracle11g SAP测试机归档日志暴增排查(二)
17 1
|
2天前
|
Oracle 关系型数据库 Shell
oracle11g SAP测试机归档日志暴增排查(一)
oracle11g SAP测试机归档日志暴增排查(一)
14 1
|
2天前
|
DataWorks NoSQL 关系型数据库
DataWorks操作报错合集之在使用 DataWorks 进行 MongoDB 同步时遇到了连通性测试失败,实例配置和 MongoDB 白名单配置均正确,且同 VPC 下 MySQL 可以成功连接并同步,但 MongoDB 却无法完成同样的操作如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
33 1
|
2天前
|
Java 测试技术 数据库
【SpringBoot】连接数据源并回显(附加单元测试)
【SpringBoot】连接数据源并回显(附加单元测试)
15 0
|
2天前
|
分布式计算 DataWorks 调度
DataWorks报错问题之DataWorks测试连接数据源报错如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台,支持数据集成、数据开发、数据治理等功能;在本汇总中,我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答,以助用户在数据处理和分析工作中提高效率,降低难度。
|
2天前
|
弹性计算 缓存 测试技术
2核4g服务器能支持多少人访问?阿里云2核4G服务器并发数测试
2核4g服务器能支持多少人访问?阿里云2核4G服务器并发数测试,2核4G服务器并发数性能测试,阿小云账号下的2核4G服务器支持20人同时在线访问,然而应用不同、类型不同、程序效率不同实际并发数也不同,2核4G服务器的在线访问人数取决于多个变量因素
|
2天前
|
存储 安全 测试技术
金融相关软件的测试如何平衡数据的访问和安全
金融相关软件的测试如何平衡数据的访问和安全

推荐镜像

更多