oracle 数据库中几种连接方式执行过程（nested loop、hash join、sort order join）
浏览数：23 /
时间：2015年06月12日
简单介绍了一下oracle 各种连接方式的执行过程，伪代码内容来源于pro oracle sql。
nested loop：
select empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno
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
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
sort-merge join :
select empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno
from emp, dept
where emp.deptno = dept.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
hash-join :
from emp, dept
where emp.deptno = dept.deptno
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
如需转载，请注明出处：http://blog.csdn.net/renfengjun/article/details/18599161
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
