hash join\nest loop join\sort merge join的实验
前几天看了join相关的知识，做了个实验，今天整理看到了，贴出来与大家探讨。分析部分是自己看文档后想的，不知道对不对。
以下结果都是执行多次，消除物理读后的结果
SQL> select ename from emp,dept where dept.dname='SALES' and dept.deptno=emp.deptno;
ENAME
----------
……
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 110 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 15 | 135 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 15 | 135 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
默认情况下选择了sort merge join。
SQL> select /*+use_nl(emp,dept)*/ename from emp,dept where dept.dname='SALES' and dept.deptno=emp.deptno;
ENAME
----------
……
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 110 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 45 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT"."DNAME"='SALES')
3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
来看看hash join的效果：
SQL> select /*+use_hash(emp,dept)*/ename from emp,dept where dept.dname='SALES' and dept.deptno=emp.deptno;
ENAME
----------
BLAKE
JAMES
ALLEN
WARD
MARTIN
TURNER
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 5 | 110 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 15 | 135 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
2 - filter("DEPT"."DNAME"='SALES')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
可以看到，第一种方式的consistent gets值是最小的。可以这样理解：
我们的目的是以部门名为条件，根据部门编号到职员表中得到某一个部门的职员名。
有三种方法可以实现：
（1）sort merge join：先将两个表分别排序，然后按照外键值deptno来对应。先对emp表按照deptno进行排序，然后根据‘SALES’通过索引找到对应的部门编号30，再将已经排序的emp中相应数据输出。由于emp、dept表存在确定的对应关系，即外键约束，因此使用这种方式是比较高效的。
（2）nest loop join：相当于对两个表分别做了一次循环，对两个表做全表扫描。由于dept表中对30以外的部门编号不需要查询，这时候对deptno全表扫描就会多付出一定的成本。
（3）hash join：oracle根据对行的评估，认为dept表中数据较少，将dept表满足条件的数据（即deptno=30的数据）读入内存，称为hash表，然后与磁盘上的emp表匹配，得到结果。
再来看看查找emp全表数据的情况：
select emp.* from emp,dept where dept.dname='SALES' and dept.deptno=emp.deptno;
……
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 250 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 250 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 15 | 555 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 15 | 555 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1089 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
执行计划和只查询职员名基本一样。
下面换一种思路，如果是查找工资最高员工的部门名称，会使用哪种方式呢？
按照最普通的想法，应该是先对emp表全表扫描得到工资最高的员工部门号，再根据部门编号通过索引找到部门名称。应该还是sort merge join吧。
SQL> select dname from dept where deptno=(select deptno from emp where sal=(select max(sal) from emp));
DNAME
--------------
ACCOUNTING
Execution Plan
----------------------------------------------------------
Plan hash value: 603094018
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)|00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7 | 2 (0)|00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_SAL | 1 | | 1 (0)|00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 4 | | |
| 6 | INDEX FULL SCAN (MIN/MAX)| IDX_SAL | 1 | 4 | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT "DEPTNO" FROM "EMP" "EMP" WHERE "SAL"= (SELECT
MAX("SAL") FROM "EMP" "EMP")))
4 - access("SAL"= (SELECT MAX("SAL") FROM "EMP" "EMP"))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
427 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结果与想法差异很大，这个语句确实不好表示。根据执行计划，应该是逐层处理的。oracle根据索引idx_sal找到最大值，然后sort aggregate操作，再对idx_sal进行范围查询；然后根据索引查找rowid，这时应该得到了sal最大的职员信息，在通过主键唯一索引找到对应的部门编号，最后通过dept表中deptno列的索引找到rowid。
Execution Plan
----------------------------------------------------------
Plan hash value: 2360289771
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 4 | | |
| 5 | TABLE ACCESS FULL | EMP | 15 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
删除sal列上的索引后，执行计划如上。对sort aggregate很是不理解，论坛中给出的答案是在：如果返回值是一行，就会有此操作。
============================================================================================
SQL> create table dbt as select * from dba_tables;
Table created.
SQL> create table dbi as select * from dba_indexes;
Table created.
SQL> select dbi.* from dbi,dbt where dbi.table_name=dbt.table_name and dbt.num_rows>1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3672305388
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4612 | 5003K| 80 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 4612 | 5003K| 80 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DBT | 2689 | 61847 | 31 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DBI | 4603 | 4890K| 48 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DBI"."TABLE_NAME"="DBT"."TABLE_NAME")
2 - filter("DBT"."NUM_ROWS">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select * from dbi where table_name=(select table_name from dbt where num_rows>1000);
Execution Plan
----------------------------------------------------------
Plan hash value: 3796313462
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 50048 | 50 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | DBI | 46 | 50048 | 48 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DBT | 2 | 46 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"= (SELECT "TABLE_NAME" FROM "DBT" "DBT" WHERE
"NUM_ROWS">1000))
2 - filter("NUM_ROWS">1000)
Note
-----
- dynamic sampling used for this statement (level=2)
由于oracle在生成执行计划前会对语句进行几个操作：
对视图进行谓词合并，即将对简单视图的操作改写为对表的操作；
对复杂视图，如果可以将条件合并为对表的操作，将进行改写；
对嵌套语句的改写，即如果oracle认为嵌套语句能够改写为两个表的合并操作，将进行改写；
当语句调用了物化视图时，尝试物化视图重写来提高效率。
官方说法是query transformation，包括view merging、predicate pushing、subquery unnesting in subquery nesting、query rewrite with materialized view
上面的语句为什么没有进行subquery nesting的操作呢?
由于我们没有对两个表进行关联，oracle不知道两个表的对应关系，因此无从改写。
来看下面的例子：
SQL> select * from emp where deptno=(select deptno from dept where dname='SALES');
Execution Plan
----------------------------------------------------------
Plan hash value: 2858400319
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 185 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 5 | 185 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"= (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE
"DNAME"='SALES'))
2 - filter("DNAME"='SALES')
SQL> select * from emp where deptno in(select deptno from dept );
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 555 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 15 | 555 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO" IS NOT NULL)
SQL> select * from emp where deptno in(select deptno from dept where dname='SALES');
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 250 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 250 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 15 | 555 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 15 | 555 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DNAME"='SALES')
4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
通过上面的三个执行计划可以看到：
使用等号连接两个嵌套表时，没有进行嵌套到非嵌套的转化
当两个表间存在外键约束时，oracle知道子表中的外键列在主表中肯定是存在的，因此直接进行全表扫描，而不需要再去主表中检查是否有对应关系了。
使用in操作符连接两个嵌套表，且oracle知道两个表间存在依赖关系时，进行了从嵌套查询到非嵌套查询的改写。
更进一步地将，第1、3个例子的结果其实是等价的。但从cost值可以看出很大的差别。
我们的最终目的是成本最低的得到结果，而例1中的成本显然比例3低，虽然使用了嵌套查询。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select d.deptno ,e.empno from emp e full outer join dept d
2 on e.deptno=d.deptno
3 order by d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2430776502
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 416 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 16 | 416 | 7 (29)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 16 | 416 | 6 (17)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 16 | 160 | 6 (17)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| PK_DEPT | 4 | 12 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
以上是hash join full outer的例子
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-751601/ ，如需转载，请注明出处，否则将追究法律责任。
