日期:2014-05-17  浏览次数:21062 次

oracle的SMJ,NL和HJ
请问怎样在查询语句中使用SMJ,NL或HJ啊,是ORACLE自动选择的外,另外它们和内联接(inner join),左外连接(left join),右外连接(right join)有什么关系?左外连接中也可以指定SMJ吗?谢谢!

------解决方案--------------------
SQL code
关于左连接、右连接、外接连总结
在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b  
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b  
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)  
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id  
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id  
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id  
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause

------解决方案--------------------
你看执行计划使用了hash join 还是nest loop然后可以使用oracle hint进行指定
. /*+USE_HASH(TABLE)*/
  将指定的表与其他行源通过哈希连接方式连接起来.
  例如:
  SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

/*+USE_NL(TABLE)*/
  将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
  例如:
  SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
------解决方案--------------------
NESTED LOOP(嵌套循环)
对于被连接的数据子集较小的情况,被驱动表有索引,nested loop连接是个较好的选择。
可用 USE_NL(table_name1 table_name2)提示来强制使用nested loop。 

HASH JOIN(散列连接)
hash join是CBO 做大数据集连接时的常用方式。以小表散列作驱动表,不需要索引。 
可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。 

SORT MERGE JOIN(排序合并连接)
sort merge join的操作:对连接的每个表做table access full;排序;进行merge join
对排序结果进行合并。性能不太好,现在一般使用HASH JOIN来替代,如果原数据集已排
序的话,性能比HASH JOIN好。
可用 USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。 

不过上面这些与优化器有关的连接方式与语法上的左、右或外连接没啥关系。