Re: Oracle Subquery Question

There is a little note about this on my web-site under Miscellaneous -> Optimising Sorts.

In this case, the no_merge is there to ensure that Oracle doesn't get clever and join
>> inventory_part_tab a,

 and
>> inventory_part_location_tab

before doing the 'group by', which looked (a) as if it would be possible, and (b) as if it would be more expensive that sorting
in-line before joining.

As a general rule, I would be inclined to put this hint in if I were confident that I knew the better path was without merging.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Davide S. wrote in message ...


>I am quite curious about the hint "no_merge".      Do you suggest it for

>queries stuctured like this one?

>

>Thanks

>

>

>Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message

>980281177.24097.0.nnrp-01.9e984b29_at_news.demon.co.uk...

>>

>> Perhaps you're after something like:

>>

>> SELECT a.part_no, a.description, NDF_Onhand

>> FROM

>>         inventory_part_tab a,

>>         (

>>         SELECT /*+ no_merge */

>>                 part_no, SUM(qty_onhand) as NDF_Onhand

>>         FROM inventory_part_location_tab

>>         group by part_no

>>         )    b

>> WHERE b.part_no =a.part_no

>> order by a.part_no

>>

>> In 8.0, you can put any __standalone__ query in

>> place of a single table, but can't include a reference

>> to a table in the surrounding FROM clause.

>>