REPOST: Re: CBO - Why does it often choose the Sort Merge Join?

Ian wrote:
>
> Hi,
> I've been doing some basic testing on sort/merge and hash joins and
> was wondering why the CBO often prefers to use the sort/merge join
> rather than the hash join?
>
> I can't find any documentation anywhere!
>
> Many thanks in advance
>
> Si

Haven't got hash_join_enabled = false have you? :-)

As a rough guide, if one of the join tables is small, a hash join is often chosen. If both tables are large (and sort_area_size is large), the sort-merges tyically get the nod.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

========= WAS CANCELLED BY =======:
From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Control: cancel <3C51D07B.844_at_yahoo.com>
Subject: cmsg cancel <3C51D07B.844_at_yahoo.com>
Date: Sun, 27 Jan 2002 23:39:10 GMT
Message-ID: <cancel.3C51D07B.844_at_yahoo.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1         
Path: news.uni-stuttgart.de!nntp.cs.uni-magdeburg.de!RRZ.Uni-Koeln.DE!news-koe1.dfn.de!news-fra1.dfn.de!news-lei1.dfn.de!newsfeed.freenet.de!newsfeed.media.kyoto-u.ac.jp!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40721771

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.