《收获，不止Oracle》表的连接学以致用

DB架构

于 2023-01-29 14:35:24 发布

阅读量430

点赞数

分类专栏： DBA 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/u011868279/article/details/128740207

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

6.2 三大类型

6.2.1 连接类型

嵌套循环

排序合并及散列连接

第一种方式就是数据库表连接中的嵌套循环连接（Nested Loops Join），而第二种方式就是表连接中的排序合并连接（Merge Sort Join）或者散列连接（Hash Join），请大家记住。

我在这里要告诉大家，在电信、金融等领域的数据库相关应用中，表连接总体的比例情况大致为，嵌套循环连接占70%左右，而散列连接占20%，剩下大致10%是排序合并连接。”

各类连接被访问次数的差异

研究嵌套循环连接的表的被访问次数前的准备

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 29 12:47:17 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> conn maxwellpan/maxwellpan@PDB1
Connected.
SQL> 
SQL> 
SQL> show user;
USER is "MAXWELLPAN"
SQL> 
SQL> 
SQL> drop table t1 cascade constraints purge;

Table dropped.

SQL> drop table t2 cascade constraints purge;

Table dropped.

SQL> create table t1 (
  2    id number not null,
  3    n number,
  4    contents varchar2(4000)
  5  );

Table created.

SQL> create table t2 (
  2    id number not null,
  3    t1_id number not null,
  4    n number,
  5    contents varchar2(4000)
  6  );

Table created.

SQL> execute dbms_random.seed(0);

PL/SQL procedure successfully completed.

SQL> INSERT INTO T1
  2    SELECT rownum,rownum,dbms_random.string('a',50)
  3  from dual
  4  connect by level <= 100
  5  order by dbms_random.random;

100 rows created.

SQL> INSERT INTO T2
  2    SELECT rownum,rownum,rownum,dbms_random.string('a',50)
  3  from dual
  4  connect by level <= 100000
  5  order by dbms_random.random;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
       100

SQL> select count(*) from t2;

  COUNT(*)
----------
    100000

SQL>

测试表连接的写法如下：

select /*+leading(t1)use_nl(t2)*/
 *
  from t1, t2
 where t1.id = t2.t1_id;

研究嵌套循环连接，t2表被访问100次

SQL> set linesize 1000
SQL> alter session set statistics_level=all;

Session altered.

SQL> select /*+leading(t1)use_nl(t2)*/
  2   *
  3    from t1, t2
  4   where t1.id = t2.t1_id;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        12         12
LIoEWaAdhvdNAhpdbdpIawQFQDmzebzEqwwChvFHDhoRfrlVrc
        12         12         12
OtnpOjHihZjuQvFTrbUnzdudicUrhVdHalzMpNSmzDmxpXjChe


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        22         22
oXzAinzjnUtJrhwPzWHTktpdZPTPHTkYAyjsmYMawihqyolJuT
        22         22         22
hsGLxPQWFPExbxjfezItjqsfjSYObxxfmcDjjjhxiUIWORrFhQ


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        10         10
InblvsVdzHVVPFElOsSWtRLkqAJwytraTSsWMoEaoYNAuAoIrI
        10         10         10
uLeVTecJSNhjCPcXeiyONxMIFQvaqLuFyuvWDvBuyiHLeWzlbh

100 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fx7936hqx6118, child number 0
-------------------------------------
select /*+leading(t1)use_nl(t2)*/  *   from t1, t2  where t1.id =
t2.t1_id

Plan hash value: 1967407726

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.30 |     100K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      14 |
|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100 |00:00:00.29 |     100K|
-------------------------------------------------------------------------------------

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

   3 - filter("T1"."ID"="T2"."T1_ID")

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>

100次怎么变成了2 次

在刚才的基础上，我们继续跟踪观察如下语句，差别在于增加了t1的条件：

 select /*+leading(t1)use_nl(t2)*/
  *
   from t1, t2
  where t1.id = t2.t1_id
    and t1.n in (17, 19);

换个语句，这次t2表被访问2次

SQL> 
SQL>  select /*+leading(t1)use_nl(t2)*/
  2    *
  3     from t1, t2
  4    where t1.id = t2.t1_id
  5      and t1.n in (17, 19);

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        17         17
mcbXTSZhvccAomKZZJdfSanBDAlVYefHAurNoryMikSJjeobGT
        17         17         17
RXWlAksGJyRTYvRYzcAApNTJZhZSOXvBPBpdwujRLERFshXvCm


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gfdsv34x0r9gx, child number 0
-------------------------------------
 select /*+leading(t1)use_nl(t2)*/   *    from t1, t2   where t1.id =
t2.t1_id     and t1.n in (17, 19)

Plan hash value: 1967407726

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      2 |      2 |00:00:00.01 |    2019 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      2 |      2 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      2 |      1 |      2 |00:00:00.01 |    2011 |
-------------------------------------------------------------------------------------

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

   2 - filter(("T1"."N"=17 OR "T1"."N"=19))
   3 - filter("T1"."ID"="T2"."T1_ID")


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

2次怎么变成1 次

继续换个语句，这次t2表被访问1次

SQL> 
SQL>  select /*+leading(t1)use_nl(t2)*/
  2    *
  3     from t1, t2
  4    where t1.id = t2.t1_id
  5      and t1.n =19;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b08tygwk2nmbh, child number 0
-------------------------------------
 select /*+leading(t1)use_nl(t2)*/   *    from t1, t2   where t1.id =
t2.t1_id     and t1.n =19

Plan hash value: 1967407726

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |    1006 |
-------------------------------------------------------------------------------------

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

   2 - filter("T1"."N"=19)
   3 - filter("T1"."ID"="T2"."T1_ID")


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

1次又变成 0次

改写到最后，t2表居然被访问0次

SQL> 
SQL>  select /*+leading(t1)use_nl(t2)*/
  2    *
  3     from t1, t2
  4    where t1.id = t2.t1_id
  5      and t1.n =999999999;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b30dn2nuuz6zt, child number 0
-------------------------------------
 select /*+leading(t1)use_nl(t2)*/   *    from t1, t2   where t1.id =
t2.t1_id     and t1.n =999999999

Plan hash value: 1967407726

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------

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

   2 - filter("T1"."N"=999999999)
   3 - filter("T1"."ID"="T2"."T1_ID")


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

嵌套循环连接访问次数的最终结论

t1表的查询返回多少条记录，t2表就被访问多少次。第一次t1表返回100条记录，是因为t1表全表就是100条记录，无条件查询当然就是返回100条；而第二次AND t1.n in（17,19）的条件让t1表返回2条记录，所以t2表被访问2次；第三次AND t1.n=19的条件让t1表的查询只返回1条记录，所以t2表被访问1次；最后一次AND t1.n=999999999这个条件显然是从t1表中查不出任何记录的，所以t2表被访问0次，干脆就不被访问了！

分析t2表被访问次数不同的原因

SQL> ---解释t2表为什么被访问100次
SQL> select count(*) from t1;

  COUNT(*)
----------
       100

SQL> 
SQL> ---解释t2表为什么被访问2次
SQL> select count(*) from t1 where t1.n in (17, 19);

  COUNT(*)
----------
         2

SQL> 
SQL> ---解释t2表为什么被访问1次
SQL> select count(*) from t1 where t1.n = 19;

  COUNT(*)
----------
         1

SQL> 
SQL> 
SQL> ---解释t2表为什么被访问0次
SQL> select count(*) from t1 where t1.n = 999999999;

  COUNT(*)
----------
         0

SQL>

散列连接的表被访问的次数

测试t2表仅被访问1次

select /*+leading(t1)use_hash(t2)*/*
  from t1, t2
 where t1.id = t2.t1_id;

散列连接中t2表只会被访问1次或0次

SQL> 
SQL> select /*+leading(t1)use_hash(t2)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        29         29
UdyZOgyQOYrowfGAmMLgKAwNXFpmsbAhvkwzESohhOaHvLSHfD
        29         29         29
NBgeTJqQwABblhuRRZbsaTdpulkvFzlpGKYwNGnuTiEHxbBQqp

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        96         96
PDjlvYcPtkTrFCITsiRPtGAdqEImtZYckVgLyJzYQpSYKwnbDy
        96         96         96
vxBFUdYBjpaXikSdvkYxaTZulJFcZrHAkDmeBrDYtghvZakvex


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        51         51
OSvQYvjxmyzzGMnFGccgQPuuiLtDgJNADGvxNkSLCWEeCFXWpP
        51         51         51
xwcqdBlQwpumvozClyiAdXkfsfzoQfhfuSyKctENvbfITolNtj


100 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8m9zqja907n7j, child number 0
-------------------------------------
select /*+leading(t1)use_hash(t2)*/*   from t1, t2  where t1.id =
t2.t1_id

Plan hash value: 1838229974

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.01 |    1083 |  1000K|  1000K| 1346K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>

散列连接准确结论

不过更准确的说法应该是：在散列连接中，驱动表和被驱动表都只会被访问0次或者1次。

Hash Join中t2表被访问0次的情况

SQL> 
SQL> select /*+leading(t1)use_hash(t2)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4   and t1.n = 999999999;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9p23u2b7c4tjw, child number 0
-------------------------------------
select /*+leading(t1)use_hash(t2)*/*   from t1, t2  where t1.id =
t2.t1_id  and t1.n = 999999999

Plan hash value: 1838229974

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |   685K|   685K|  179K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      0 |  98810 |      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=999999999)


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

散列连接中t1表和t2表都被访问0次的情况

SQL> 
SQL>  select /*+leading(t1)use_hash(t2)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4   and 1=2;

no rows selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4jcf0cucqftuu, child number 0
-------------------------------------
 select /*+leading(t1)use_hash(t2)*/*   from t1, t2  where t1.id =
t2.t1_id  and 1=2

Plan hash value: 487071653

----------------------------------------------------------------------------
| 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 |   HASH JOIN         |      |      0 |    100 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      0 |    100 |      0 |00:00:00.01 |
|   4 |    TABLE ACCESS FULL| T2   |      0 |  98810 |      0 |00:00:00.01 |
----------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)
   2 - access("T1"."ID"="T2"."T1_ID")

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


27 rows selected.

SQL>

排序合并连接的表被访问的次数

排序合并连接的访问情况和散列连接一样

SQL> 
SQL> select /*+ordered use_merge(t2)*/*
  2   from t1, t2
  3  where t1.id = t2.t1_id;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1          1
rMLTDXxxqXOZnqYRJwInlGfGBTxNkAszBGEUGELqTSRnFjRGbi
         1          1          1
oEDjYpmoOoPPCgRApZewlJvzMTsosHAFerbsFWoxLAExnGcaPH


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2          2
UEDJsfIgoYqwreSuuvjIcPZarpxMdCthpDCsgPlJfvIiylLiBS
         2          2          2
RcGqBYvXQxnttfqIICFyBaiuYPXacaNFUOrGCmzJDKhrrrMGqL
100 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b3dw96yzr1k2k, child number 0
-------------------------------------
select /*+ordered use_merge(t2)*/*  from t1, t2 where t1.id = t2.t1_id

Plan hash value: 412793182

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    100 |00:00:00.03 |    1012 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.03 |    1012 |       |       |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       7 | 13312 | 13312 |12288  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |    100 |  98810 |    100 |00:00:00.03 |    1005 |  9762K|  1209K| 8677K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


27 rows selected.

SQL>

显然可以看出来，在访问次数上，排序合并连接和散列连接是一样的，t1表和t2表都只会被访问0次或者1次。

排序合并连接根本就没有驱动和被驱动的概念，而嵌套循环连接和散列连接要考虑驱动和被驱动情况。

各类连接驱动顺序的区别

嵌套循环连接的表驱动顺序

嵌套循环连接中t1表先被访问的情况

SQL> 
SQL> alter session set statistics_level=all;

Session altered.

SQL>  select /*+leading(t1)use_nl(t2)*/
  2    *
  3     from t1, t2
  4    where t1.id = t2.t1_id
  5      and t1.n =19;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b08tygwk2nmbh, child number 0
-------------------------------------
 select /*+leading(t1)use_nl(t2)*/   *    from t1, t2   where t1.id =
t2.t1_id     and t1.n =19

Plan hash value: 1967407726

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |    1006 |
-------------------------------------------------------------------------------------

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

   2 - filter("T1"."N"=19)
   3 - filter("T1"."ID"="T2"."T1_ID")


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

嵌套循环连接中t2表先被访问的情况

SQL> 
SQL>   select /*+leading(t2)use_nl(t1)*/
  2    *
  3     from t1, t2
  4    where t1.id = t2.t1_id
  5      and t1.n =19;

        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dyt6y9v61aprm, child number 0
-------------------------------------
  select /*+leading(t2)use_nl(t1)*/   *    from t1, t2   where t1.id =
t2.t1_id     and t1.n =19

Plan hash value: 4016936828

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.58 |     701K|

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.58 |     701K|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1006 |
|*  3 |   TABLE ACCESS FULL| T1   |    100K|      1 |      1 |00:00:00.55 |     700K|
-------------------------------------------------------------------------------------

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

   3 - filter(("T1"."ID"="T2"."T1_ID" AND "T1"."N"=19))

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic statistics used: dynamic sampling (level=2)


25 rows selected.

SQL>

散列连接的表驱动顺序

散列连接中t1表先被访问的情况

SQL> 
SQL> ---观察t1先被访问的情况
SQL> alter session set statistics_level=all;    

Session altered.

SQL> select /*+leading(t1)use_hash(t2)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4    and t1.n =19;


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5fthzm536j4c3, child number 0
-------------------------------------
select /*+leading(t1)use_hash(t2)*/*   from t1, t2  where t1.id =
t2.t1_id   and t1.n =19

Plan hash value: 1838229974

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.01 |    1077 |  1000K|  1000K|  423K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1006 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=19)


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

散列连接中t2表先被访问的情况

SQL> ---观察t2先被访问的情况
SQL> alter session set statistics_level=all;    

Session altered.

SQL> select /*+leading(t2)use_hash(t1)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4    and t1.n =19;


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9mgbxa9k7ttms, child number 0
-------------------------------------
select /*+leading(t2)use_hash(t1)*/*   from t1, t2  where t1.id =
t2.t1_id   and t1.n =19

Plan hash value: 2959412835

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.03 |    1013 |    12M|  2564K|   15M (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1005 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")
   3 - filter("T1"."N"=19)


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

t1表先被访问的情况下Buffers是1013，而t2表先被访问的情况下，Buffers也是1013，但是Used-Mem却差异明显，前者是286KB，后者是11MB，说明排序尺寸差异明显。再结合时间来看，前者是0.04秒，后者是0.1秒，差别也不小。“说明散列连接中驱动表的顺序非常重要，性能差别也很明显！

排序合并的表驱动顺序

排序合并连接中t1表先被访问的情况

SQL> ---观察t1先被访问的情况
SQL> alter session set statistics_level=all;    

Session altered.

SQL> select /*+leading(t1)use_merge(t2)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4    and t1.n =19;


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dxvtjqg2dgjfy, child number 0
-------------------------------------
select /*+leading(t1)use_merge(t2)*/*   from t1, t2  where t1.id =
t2.t1_id   and t1.n =19

Plan hash value: 412793182

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.03 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |  98810 |      1 |00:00:00.03 |    1005 |  9762K|  1209K| 8677K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   3 - filter("T1"."N"=19)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


29 rows selected.

SQL>

排序合并连接中t2表先被访问的情况

SQL> ---观察t2先被访问的情况
SQL> alter session set statistics_level=all;    

Session altered.

SQL> select /*+leading(t2)use_merge(t1)*/*
  2    from t1, t2
  3   where t1.id = t2.t1_id
  4    and t1.n =19;


        ID          N
---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        ID      T1_ID          N
---------- ---------- ----------
CONTENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        19         19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
        19         19         19
GjEZVArATZoLjgeBswAqZZshWrhgQeEKLYScUQqMtlFclNtjHK


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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cythx275yxzr7, child number 0
-------------------------------------
select /*+leading(t2)use_merge(t1)*/*   from t1, t2  where t1.id =
t2.t1_id   and t1.n =19

Plan hash value: 1792967693

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.04 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |  98810 |     20 |00:00:00.04 |    1005 |  9762K|  1209K| 8677K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |  98810 |    100K|00:00:00.01 |    1005 |       |       |          |
|*  4 |   SORT JOIN         |      |     20 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   4 - access("T1"."ID"="T2"."T1_ID")

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       filter("T1"."ID"="T2"."T1_ID")
   5 - filter("T1"."N"=19)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


29 rows selected.

SQL>

嵌套循环连接和散列连接有驱动顺序，驱动表的顺序不同将影响表连接的性能，而排序合并连接没有驱动的概念，无论哪张表在前都无妨。