oracle 数据库中几种连接方式执行过程(nested loop、hash join、sort order join)

简单介绍了一下oracle 各种连接方式的执行过程,伪代码内容来源于pro oracle sql。

nested loop:

select empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno


for each row in (select empno, ename, deptno from emp) loop
  for (select dname, loc from dept where deptno =   outer.deptno) loop
    If match then pass the row on to the next step
    If inner join and no match then discard the row
    If outer join and no match set inner column values to null
    and pass the row on to the next step
  end loop
end loop

oracle 数据库中几种连接方式执行过程(nested loop、hash join、sort order join)


sort-merge join :

select empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno


select empno, ename, deptno from emp order by deptno
select dname, loc, deptno from dept order by deptno
compare the rowsets and return rows where deptno in both lists match
for an outer join, compare the rowsets and return all rows from the first list
setting column values for the other table to null

oracle 数据库中几种连接方式执行过程(nested loop、hash join、sort order join)


hash-join :

select empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno


determine the smaller row set, or in the case of an outer join,
use the outer joined table
select dname, loc, deptno from dept
hash the deptno column and build a hash table
select empno, ename, deptno from emp
hash the deptno column and probe the hash table
if match made, check bitmap to confirm row match
if no match made, discard the row


oracle 数据库中几种连接方式执行过程(nested loop、hash join、sort order join)



如需转载,请注明出处:http://blog.csdn.net/renfengjun/article/details/18599161

oracle 数据库中几种连接方式执行过程(nested loop、hash join、sort order join)

上一篇:关注CSDN社区微信,更多精彩等你来


下一篇:这篇文章来自我的微信朋友圈,并不特别好玩,但可以给创业者补点财务知识