Oracle 常见的表连接方法(join method)
1.哈希连接(hash join)
2.嵌套循环连接(nested loop join)
3.合并排序连接(merge sort join)
4.笛卡尔连接(cartesian jion)
1.哈希连接(hash join)
2.嵌套循环连接(nested loop join)
3.合并排序连接(merge sort join)
4.笛卡尔连接(cartesian jion)
[oracle@sean ~]$ sqlplus scott/tiger
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@sean> set linesize 200
SCOTT@sean> set pagesize 100
--emp表数据
SCOTT@sean> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
--dept表数据
SCOTT@sean> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
--索引信息
SCOTT@sean> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
PK_EMP NORMAL EMP UNIQUE
PK_DEPT NORMAL DEPT UNIQUE
SCOTT@sean> select INDEX_NAME,TABLE_NAME,COLUMN_NAME from user_ind_columns;
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- -------------------- --------------------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
--表和索引段信息
SCOTT@sean> select SEGMENT_NAME,BYTES,BLOCKS from user_segments where regexp_like(segment_name,'EMP|DEPT');
SEGMENT_NAME BYTES BLOCKS
-------------------- ---------- ----------
DEPT 65536 8
EMP 65536 8
PK_DEPT 65536 8
PK_EMP 65536 8
--SQL语句和执行结果
SCOTT@sean> select a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7782 CLARK 2450 10 ACCOUNTING
7839 KING 5000 10 ACCOUNTING
7934 MILLER 1300 10 ACCOUNTING
7566 JONES 2975 20 RESEARCH
7902 FORD 3000 20 RESEARCH
7876 ADAMS 1100 20 RESEARCH
7369 SMITH 800 20 RESEARCH
7788 SCOTT 3000 20 RESEARCH
7521 WARD 1250 30 SALES
7844 TURNER 1500 30 SALES
7499 ALLEN 1600 30 SALES
7900 JAMES 950 30 SALES
7698 BLAKE 2850 30 SALES
7654 MARTIN 1250 30 SALES
14 rows selected.
--查看各种执行计划和相关执行信息,每条语句都执行3次,消除第1次执行硬解析所需要的递归sql的影响,关注执行计划中的cost和statistics中的consistent gets(逻辑读)
SCOTT@sean> set autotrace traceonly
--第1种是合并排序连接(merge sort join),需要两个结果集在连接字段上面先排好序,再进行MERGE JOIN。emp表在全表扫描后有一个sort join。dept表先通过索引全扫描,然后根据索引中有序的rowid去dept表中取得结果集,因此这个结果集也是排好序的。
--这个执行计划的cost是6,逻辑读是10
SCOTT@sean> select a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 420 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1164 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
--第2种是哈希连接(hash join),哈希连接是先将其中的一个小表根据连接字段hash运算,生成多个hash bucket,然后另外表一个表来做hash匹配。hash连接只能用在等值比较。
--这个执行计划的cost是6,逻辑读是13
SCOTT@sean> select /*+ use_hash(a,b) */ a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 420 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1229 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)
14 rows processed
--第3种是嵌套循环连接(nested loop join),在本例中就是emp表中的每行数据都去和dept表中的任意一行数据嵌套匹配,这样循环直到最后一行。这种比较适合,emp是小表,而且dept表中的deptno字段有索引,这样才能让循环次数少,而且匹配效率高
--这个执行计划的cost是10,逻辑读是32
SCOTT@sean> select /*+ use_nl(a,b) */ a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 420 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 68 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
1160 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)
14 rows processed
--此执行计划和优化器默认选择的执行计划相同
SCOTT@sean> select /*+ use_merge(a,b) */ a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 420 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1164 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
--当告知优化器不使用合并排序连接后,优化器选择了哈希连接,哈希连接成本是6,而嵌套循环连接成本是10
SCOTT@sean> select /*+ no_use_merge(a,b) */ a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 420 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1229 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)
14 rows processed
--第4种是笛卡尔连接(CARTESIAN join),也是一种常见的连接方法,emp表中的每一行都会和dept中的每一样做一个连接。假如emp有m行数据,dept有n行数据,结果就是m*n行数据。
SCOTT@sean> select a.empno,a.ename,a.sal,b.deptno,b.dname from emp a,dept b;
56 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 1512 | 10 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 56 | 1512 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 196 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 196 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
2621 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
56 rows processed