当前位置: 代码迷 >> 综合 >> Oracle 常见的表连接方法(join method)
  详细解决方案

Oracle 常见的表连接方法(join method)

热度:39   发布时间:2024-01-16 02:22:36.0
Oracle 常见的表连接方法(join method)
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 ProductionSCOTT@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                    207499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         307521 WARD       SALESMAN        7698 22-FEB-81       1250        500         307566 JONES      MANAGER         7839 02-APR-81       2975                    207654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         307698 BLAKE      MANAGER         7839 01-MAY-81       2850                    307782 CLARK      MANAGER         7839 09-JUN-81       2450                    107788 SCOTT      ANALYST         7566 19-APR-87       3000                    207839 KING       PRESIDENT            17-NOV-81       5000                    107844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         307876 ADAMS      CLERK           7788 23-MAY-87       1100                    207900 JAMES      CLERK           7698 03-DEC-81        950                    307902 FORD       ANALYST         7566 03-DEC-81       3000                    207934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.--dept表数据
SCOTT@sean> select * from dept;DEPTNO DNAME          LOC
---------- -------------- -------------10 ACCOUNTING     NEW YORK20 RESEARCH       DALLAS30 SALES          CHICAGO40 OPERATIONS     BOSTON4 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                           UNIQUESCOTT@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 ACCOUNTING7839 KING             5000         10 ACCOUNTING7934 MILLER           1300         10 ACCOUNTING7566 JONES            2975         20 RESEARCH7902 FORD             3000         20 RESEARCH7876 ADAMS            1100         20 RESEARCH7369 SMITH             800         20 RESEARCH7788 SCOTT            3000         20 RESEARCH7521 WARD             1250         30 SALES7844 TURNER           1500         30 SALES7499 ALLEN            1600         30 SALES7900 JAMES             950         30 SALES7698 BLAKE            2850         30 SALES7654 MARTIN           1250         30 SALES14 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,逻辑读是10SCOTT@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 calls0  db block gets10  consistent gets0  physical reads0  redo size1164  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)14  rows processed--第2种是哈希连接(hash join),哈希连接是先将其中的一个小表根据连接字段hash运算,生成多个hash bucket,然后另外表一个表来做hash匹配。hash连接只能用在等值比较。
--这个执行计划的cost是6,逻辑读是13SCOTT@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 calls0  db block gets13  consistent gets0  physical reads0  redo size1229  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  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 calls0  db block gets32  consistent gets0  physical reads0  redo size1160  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  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 calls0  db block gets10  consistent gets0  physical reads0  redo size1164  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client1  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 calls0  db block gets13  consistent gets0  physical reads0  redo size1229  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  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 calls0  db block gets15  consistent gets0  physical reads0  redo size2621  bytes sent via SQL*Net to client556  bytes received via SQL*Net from client5  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)56  rows processed


  相关解决方案