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
大家学会了么
最后修改时间：2021-02-06 16:09:05
文章转载自oracle分享技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
