%NOMERGE for query optimization

Hello

I am trying to get a good example, but for now I will just try to give you a general description. As the Docs say this hint is used in a subquery. At compile time the optimizer has 2 options for dealing with a subquery:
1) Treat it as a black box, executing the subquery independently from the other query and then applying the results to the outer query
2) rewrite the query incorporating the subquery into the outer query as a JOIN and combining conditions from the subquery to the WHERE clause of the outer query.

If you look at the show plan of a query you can see which option the optimizer is taking. If you see a section of the plan with a heading of SubQuery then you know it is option 1.

If the query does not have a Subquery section we are in option 2.

You can see that with the following query:

select home_state, avg(age) from sample.employee e
where home_state = ANY (select MAX(v.address_state) from sample.vendor v where balance > 0)
group by home_state

will be rewritten

while:

select home_state, avg(age) from sample.employee e
where home_state = ANY (select MAX(v.address_state) from %NOMERGE sample.vendor v where balance > 0)
group by home_state

will keep the subquery.