Oracle 常见的表连接方法（join method）
来源：互联网 发布：mac pro上能玩的游戏 编辑：程序博客网 时间：2024/06/01 07:28
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/tigerConnected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSCOTT@sean> set linesize 200SCOTT@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 1014 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 UNIQUEPK_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 DEPTNOPK_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 8EMP 65536 8PK_DEPT 65536 8PK_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 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 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，逻辑读是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 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，逻辑读是32SCOTT@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，而嵌套循环连接成本是10SCOTT@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阅读全文
0 0
- Oracle 常见的表连接方法（join method）
- oracle的表连接hash join、nested loop join
- Oracle中的Join（连接）的使用
- oracle join连接方法以及left join中on 和where的区别
- oracle and postgresql join method
- oracle sql调优学习笔记（三）表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN
- Oracle优化器、优化模式、表的连接方式（Hash Join、Nested Loop、Sort Merge Join）
- oracle 内连接（inner join）、外连接（outer join）、全连接（full join）
- oracle 内连接（inner join）、外连接（outer join）、全连接（full join）
- oracle 内连接（inner join）、外连接（outer join）、全连接（full join）
- 分享三种oracle表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN
- 分享三种oracle表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN
- oracle inner join 隐藏的多次连接
- Oracle 的 hash join连接方式
- Oracle的join..on连接区别
- 关于表的连接（join）
- Oracle 表连接的方法 （下）
- oracle常见的连接方式
- Java定时任务Quartz的使用
- Android 7.0 gts测试。
- su: cannot set user id: 资源暂时不可用
- BIGEMAP无偏移影像叠加配准（Auto CAD版）
- 悟透JavaScript（理解JS面向对象的好文章）
- Oracle 常见的表连接方法（join method）
- python set() 函数-leetcode217 contains duplicate
- 使用tableExport.js导出数据出现科学计数法的问题解决
- html的meta总结，html标签中meta属性使用介绍
- 元素浮动后导致母元素div高度失常，子元素脱离母元素范围
- Activity的启动过程之startActivity源码解析
- 关于spring事务处理，service层方法上加锁数据没有更新
- Android 去除头部标题栏
- eclipse svn commit 报错 Commit blocked by pre-commit hook
