ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle hint (续-0)

oracle hint (续-0)

原创 Linux操作系统 作者:season0891 时间:2008-09-26 10:23:26 0 删除 编辑

USE_MERGE(table)

A merge join is an alternative to nested loop and hash joins. All tables are sorted, unless all of the columns in the WHERE clause are contained within an index. This sort can be expensive and it explains why a hash join will often run faster then a merge join.

SELECT /*+ USE_MERGE(w o) */ count(*) 
FROM winners w, owners o 
WHERE w.owner like 'Mr M A Gurry' 
AND w.owner < o.owner 
AND o.suburb = 'RICHMOND' 

 

The entire set of data must be returned before a single row is returned to the user. Therefore hash joins are usually used for reporting and batch processing.

 

 

Don't confuse the MERGE hint and USE_MERGE.

 

 

Merge joins work effectively for equality-based joins as well as for range-based joins. Merge joins also often run much faster than a hash join when all of the columns in the WHERE clause are pre-sorted in an index.

 

USE_NL(table)

Forces the optimizer to join the specified table to another table (or subquery) using a nested loop join. The specified table is joined as the inner table of the nested loops. Nested loop joins are faster than sort/merge or hash joins at retrieving the first row of a query statement

Online screens should definitely use nested loops, because data will be returned immediately. As a rule of thumb, if less than 10% of the rows are returned from the tables, consider using nested loops. Use hash joins or sort merges if 10% or more of the rows are being returned.

SELECT /*+ USE_NL(w o) */ count(*) 
FROM winners w, owners o 
WHERE w.owner like 'Mr M A Gurry' 
AND w.owner= o.owner 
AND o.suburb = 'RICHMOND' 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-464425/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle hint (续)
请登录后发表评论 登录
全部评论

注册时间:2008-06-10

  • 博文量
    791
  • 访问量
    1936403