Re: Tuning question

Thank you!

I did run explan plan and found exactly what you said. My point of confusion was that the cost for sort-merge was lower than for hash, but I am almost positive that hash will do better timewise than sort because the tables are huge.

I am required to document every step I am taking explaining exactly why I am doing this and not that. So, I guess sort versus hash explanation should be sufficient. If you know of any docs that explain these joins pretty well could you let me know please? I have a tuning book and I've read through Oracle docs, but still had questions. Maybe I am trying to understand why hash is sometimes faster than sort - that's not really an Oracle question is it?

Thanks again!

Sergey

In article <N_rf5.1163$nL5.54553_at_typhoon2.ba-dsg.net>,   "Tom Best" <tabest_at_bellatlantic.dontspamme.net> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jul 26 2000 - 00:00:00 CDT