Re: Merge join and sorts

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: 8 Jan 2002 10:03:09 -0800
Message-ID: <bde5777e.0201081003.5c884e01@posting.google.com>

Hello Alex,

probably, because you requested USE_MERGE as a join method, it automatically uses a sort on at least one of the row sources.

If you want to join the tables, without a sort, the following statement might do the thing:

SELECT /*+ first_rows ordered use_nl(m)

         */
        xcm_amount * mov_cost
FROM    t_xcome2 x


,       t_move2 m

WHERE xcm_move = mov_id

But, it is probably not the fastest one!

Try also, after setting hash_area_size to at least 8Mb:

SELECT  /*+ all_rows
            use_hash(x)
            use_hash(m)
         */
        xcm_amount * mov_cost
FROM    t_xcome2 x


,       t_move2  m

WHERE xcm_move = mov_id

Kind Regards,

Herman de Boer
sr. consultant
IT Consultancy Group bv Received on Tue Jan 08 2002 - 12:03:09 CST