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

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

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

持续高温 2018-08-12 20:51:18 浏览2469
展开阅读全文

        平时写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次。

网友评论

登录后评论
0/500
评论
持续高温
+ 关注
所属云栖号: 袋鼠云技术团队