平时写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_idPlan 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")NotePLAN_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") ANDPLAN_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.idPlan 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")NotePLAN_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次。