连接（join)
如果有主键的列连接，将带主键和唯一键约束的表放在连接的第一个位置，再考虑其它表连接
如果有外键连接，则将该表放在连接的最后。

Nested Loop Joins（嵌套循环连接）
外部表的每一行都和内部表的所有行连接。
当表的行较少的时候，数据库会选择这种连接。
提示：USE_NL(table1 table2)
SQL> CONN SCOTT/TIGER
Connected.
SQL> set autot traceonly explain
SQL> select ename,loc from emp,dept
2 where emp.deptno=dept.deptno ;

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 280 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

Hash Joins
适用于大数据量的连接。
将两个表中较小的表的连接列建立一个hash表，将hash表放入到内存中。
什么时候用HASH连接?大量数据要连接,但要想使hash连接起到作用,必须有等值的条件.
使用hint:USE_HASH

SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1 as select * from dept;

Table created.

SQL> create table t2 as select * from emp;

Table created.

SQL> select ename,loc from t1,t2
2 where t1.deptno=t2.deptno;

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 574 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 4 | 84 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 14 | 280 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------

排序融合连接
HASH连接在大部分时候都比排序连接性能好。
但如果不是等值条件的时候，条件是>,>=,<,<=的时候，不能使用hash连接,使用排序连接和嵌套循环连接。
再有当连接的结果要排好序的时候，也可以选择排序融合连接。
SQL> --1.NEST LOOP
SQL> SET AUTOT TRACE EXP
SQL> SELECT ENAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=10;

执行计划
----------------------------------------------------------
Plan hash value: 568005898

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 60 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10)

SQL> --2.SORT MERGE
SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

执行计划
----------------------------------------------------------
Plan hash value: 270072162

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 20 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 5 | 50 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

4 - filter("SAL"<="HISAL")
5 - access("SAL">="LOSAL")
filter("SAL">="LOSAL")

SQL> --3.HASH JOIN
SQL> SELECT ENAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 280 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> DROP TABLE T1 PURGE;

表已删除。

SQL> CREATE TABLE T1 AS SELECT * FROM EMP;

表已创建。

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建14行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 327 | 28449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 327 | 28449 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> /

已创建28行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 327 | 28449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 327 | 28449 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> /

已创建56行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 327 | 28449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 327 | 28449 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> /

已创建112行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 327 | 28449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 327 | 28449 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 1715696576

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 327 | 10137 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 327 | 10137 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 327 | 6540 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 1715696576

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 224 | 4480 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 224 | 4480 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 224 | 2016 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建224行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 224 | 8512 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 224 | 8512 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> /

已创建448行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 224 | 8512 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 224 | 8512 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 1715696576

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 896 | 17920 | 6 (17)| 00:00:01 |
| 1 | NESTED LOOPS | | 896 | 17920 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 896 | 8064 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建896行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 896 | 34048 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 896 | 34048 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 1290571005

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1792 | 35840 | 7 (15)| 00:00:01 |
| 1 | MERGE JOIN | | 1792 | 35840 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1792 | 16128 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 1792 | 16128 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建1792行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1792 | 68096 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1792 | 68096 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建3584行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1792 | 68096 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1792 | 68096 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> INSERT INTO T1 SELECT * FROM T1;

已创建7168行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1792 | 68096 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1792 | 68096 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 3343471717

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14336 | 280K| 22 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 14336 | 280K| 22 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 14336 | 126K| 18 (6)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("E"."DEPTNO"="D"."DEPTNO")

SQL> SELECT ENAME,LOC FROM T1 E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO ORDER BY E.DEPTNO;

执行计划
----------------------------------------------------------
Plan hash value: 108929063

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14336 | 280K| | 83 (7)| 00:00:01 |
| 1 | MERGE JOIN | | 14336 | 280K| | 83 (7)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 44 | | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 44 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14336 | 126K| 584K| 79 (6)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 14336 | 126K| | 18 (6)| 00:00:01 |
------------------------------------------------------------------------------------

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

4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
SQL> SPOOL OFF

ITPub博客

JOIN连接的3种模式

最新文章