平时写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>
/+ 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>
从执行计划来看，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>
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>
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>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>
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>
sql语句中加了1 = 2的条件，这种情况不可能成立，所以 test1 表根本无须访问。查看执行计划，驱动表test1被访问0次，被驱动表也被访问0次。