left/right/inner/full join与加号 --hash join

1.建立测试表


SQL> create table system.t1(id number(10),name varchar(10));


Table created.


SQL> insert into system.t1 
  2  select 1 as id,'t1' as name from dual
  3  union
  4  select 2 as id,'t1' as name from dual
  5  union
  6  select 3 as id,'t1' as name from dual
  7  union
  8  select 4 as id,'t1' as name from dual
  9  union
 10  select 5 as id,'t1' as name from dual;


5 rows created.


SQL> commit;


Commit complete.




SQL> create table system.t2(id number(10),name varchar(10));


Table created.


SQL> insert into system.t2 
  2  select 3 as id,'t2' as name from dual
  3  union
  4  select 4 as id,'t2' as name from dual
  5  union
  6  select 5 as id,'t2' as name from dual
  7  union
  8  select 6 as id,'t2' as name from dual
  9  union
 10  select 7 as id,'t2' as name from dual;


5 rows created.


SQL> commit;


Commit complete.


收集一下表信息




SQL> execute dbms_stats.gather_table_stats('SYSTEM','T1');


PL/SQL procedure successfully completed.


SQL> execute dbms_stats.gather_table_stats('SYSTEM','T2');


PL/SQL procedure successfully completed.






2.left join 测试


SQL> select * from system.t1 left join system.t2 on t1.id=t2.id;


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2
         1 t1
         2 t1


返回了 t1 表的5条所有数据.




Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |    60 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        817  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


从执行计划上看,




3.right join 测试


SQL> select * from system.t1 right join system.t2 on t1.id=t2.id;


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2
                               6 t2
                               7 t2
返回了 t2 表的5条所有数据.




Execution Plan
----------------------------------------------------------
Plan hash value: 1426054487


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |    60 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        817  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


4.inner join测试


SQL> select * from system.t1 inner join system.t2 on t1.id=t2.id;


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2


返回了  t1,t2 的交集，共3条数据




Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     5 |    60 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        799  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed


5.full join 测试


SQL> select * from system.t1 full join system.t2 on t1.id=t2.id;


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2
                               6 t2
                               7 t2
         1 t1
         2 t1


返回了 t1,t2 的所有记录




Execution Plan
----------------------------------------------------------
Plan hash value: 53297166


--------------------------------------------------------------------------------
--


| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
 |


--------------------------------------------------------------------------------
--


|   0 | SELECT STATEMENT      |          |     5 |   200 |     5  (20)| 00:00:01
 |


|   1 |  VIEW                 | VW_FOJ_0 |     5 |   200 |     5  (20)| 00:00:01
 |


|*  2 |   HASH JOIN FULL OUTER|          |     5 |    60 |     5  (20)| 00:00:01
 |


|   3 |    TABLE ACCESS FULL  | T1       |     5 |    30 |     2   (0)| 00:00:01
 |


|   4 |    TABLE ACCESS FULL  | T2       |     5 |    30 |     2   (0)| 00:00:01
 |


--------------------------------------------------------------------------------
--




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


   2 - access("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        838  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed


6.左边 (+) 测试


SQL> select * from system.t1,system.t2 where t1.id(+)=t2.id;


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2
                               6 t2
                               7 t2


可以看出 左边的(+) , 返回了 右边表的所有记录,相当于right join




Execution Plan
----------------------------------------------------------
Plan hash value: 1426054487


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |    60 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        817  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed


7.右边 (+) 测试


SQL> select * from system.t1,system.t2 where t1.id=t2.id(+);


        ID NAME               ID NAME
---------- ---------- ---------- ----------
         3 t1                  3 t2
         4 t1                  4 t2
         5 t1                  5 t2
         1 t1
         2 t1
可以看出  右边的 (+) ,返回了左边表的所有记录,相当于 left join




Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     5 |    60 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    30 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     5 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


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




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        817  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed




既然实验做完了，我们回头总结一下.返回的结果很简单.
首先都是 hash join 操作
Hash Join的执行计划第1个是hash表(build table)，第2个探查表(probe table)
我们回顾上面的 5 个执行计划.有时   TABLE ACCESS FULL  T1   在上,有时 TABLE ACCESS FULL  T2 在上.
那么我认为 哪个表为 探查表与hash表,并非系统指定,而是根据我们 join 的条件进行指定的.
这个时候翻查一下教材
hash outer joins: The left/outer table whose rows are being preserved is used to build the hash table,and the right/inner table is used to probe the hash table.
也就是说left/right 时已经指定好了, 那么inner 与 full 呢? 因为这两个操作 不固定.so
大家学会了么

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2135008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2135008/