Re: Tuning question

Sergey:

Do you have accurate statistics? If so, you could generate an explain plan. Use the "explain plan" command, and it will not actually *run* the query. Keep in mind that if doing the ANALYZE is going to take too long, you can always use the ESTIMATE STATISTICS clause and it will look at just a sampling of the data.

Theoretically, you should find that the sort-merge join will do a sort, and the hash join will not. So, it makes sense that, (and according to the optimizer docs) the hash join will do better for very large result sets, when the cost based optimizer is being used.

HTH,
Tom Best

<sergey_s_at_my-deja.com> wrote in message news:8llakm$grt$1_at_nnrp1.deja.com...
> I am sure I could test with the subsets of the tables, but I think the
> performance would be a whole different story with the real size tables I
> think.
>
> Sergey
>
>
> In article <uu2ddhncc.fsf_at_yahoo.com>,
> Galen Boyer <galenboyer_at_yahoo.com> wrote:
> > >>>>> "sergey" == sergey s <sergey_s_at_my-deja.com> writes:
> >
> > sergey> How can I justify using hash join over sort-merge join
> > sergey> in theory? I cannot really just run the tests to see
> > sergey> which join performs better because the tables involved
> > sergey> are a couple of billions rows and I cannot tie up the db
> > sergey> like that.
> >
> > Can you take a smaller subset of the tables, load that somewhere
> > else and try?
> > --
> > Galen Boyer
> > Database Architect
> > Primix Solutions, www.primix.com
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jul 26 2000 - 00:00:00 CDT