no_merge hint tips

转自 http://www.dba-oracle.com/t_hint_no_merge.htm

Question:  I want to understand the no_merge hint and understand when to use the no_merge hint.  Is it true that the no_merge hint has nothing to do with a merge join?

Answer:  You are correct, the no_merge hint has nothing to do with a merge join (or a sort merge join).

Rather, the no_merge is commonly used in in-line views (a case where a sub-select exists in the FROM clause of a SQL statement:

 

select /*+ no_merge(MY_VIEW) */
   stuff 
from 
   (select 
   col1,
   col2,
   col3,
   from view1) MY_VIEW,
...

In the case above, the no_merge hint tells the SQL optimizer not to do complex view merging
and to execute the in-line view before executing the outer query components.

This will make the optimization phase of the SQL statement run faster because there are less permutations of the view.

In other words, the no_merge hint prevents Oracle from re-writing the inner query (the in-line view) and the outer query into a merged view, which may be less efficient that addressing the inner query directly.

In a nutshell, the no_merge hint instructs the optimizer not to combine the outer query and any inline view queries into a single query.