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'

oracle hint (續-0)

相關文章