Message-ID: <48898BFE.3000407@williamrobertson.net>
Date: Fri, 25 Jul 2008 09:17:02 +0100
From: William Robertson <william@williamrobertson.net>
User-Agent: Thunderbird 2.0.0.16 (Macintosh/20080707)
MIME-Version: 1.0
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Subject: Re: Parallel hint ignored only in subquery
References: <34908.12.17.117.251.1216917363.squirrel@12.17.117.251>    <0E077BEDC1D3614AA06282FF824D813113837D72F5@XVS2-CLUSTER.yu.yale.edu> <29832.12.17.117.251.1216928679.squirrel@12.17.117.251>
In-Reply-To: <29832.12.17.117.251.1216928679.squirrel@12.17.117.251>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: william@williamrobertson.net

I think that's true in a general sense, however the hint syntax makes 
the distinction because there is a big difference to the optimizer 
between the two constructions, and between the two transformations that 
can be applied to them.

 From the documentation:

"
The MERGE hint lets you merge views in a query.
The NO_MERGE hint instructs the optimizer not to combine the outer query 
and any inline view queries into a single query.

The UNNEST hint instructs the optimizer to unnest and merge the body of 
the subquery into the body of the query block that contains it, allowing 
the optimizer to consider them together when evaluating access paths and 
joins.
Use of the NO_UNNEST hint turns off unnesting.
"

So it's MERGE/NO_MERGE for inline views, UNNEST/NO_UNNEST for subqueries 
(in the classic sense). I can never remember which is which and I always 
have to double-check. Hmm, "SUBQUERY" and "UNNEST" both have the letter 
"U" - maybe I'll use that to remind me from now on.


-----Original message-----
From: Rich Jesse
Date: 24/7/08 20:44
> Relationally speaking, I thought that an inline view was just one type of
> subquery, no?
>
> I did not try the NO_MERGE until just now and it works!  I learned something
> new today, or at least I learned that I'll have to learn more about
> MERGE/NO_MERGE.
>
> Thanks, Amit!
> Rich
>
>   
>> This is not a subquery but an inline view.
>>
>> Did you try no_merge hint
>>
>> Amit
>>
>>     
> [snip]

--
http://www.freelists.org/webpage/oracle-l