"Ryan" <rgaffuri_at_cox.net> wrote in message
news:gu5Jb.59084$hf1.37259_at_lakeread06...
>
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:lrOdnZGXoJslImmiRVn-sQ_at_golden.net...
> > "Mikito Harakiri" <mikharakiri_nospaum_at_yahoo.com> wrote in message
> > news:8a529bb.0401011544.ed69d30_at_posting.google.com...
> > > > "Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message
> > > > news:JSEIb.1$Mf4.30_at_news.oracle.com...
> > > > Let me get this straight are you saying that NL almost always
> > outperforms HJ
> > > > and MJ? Yes I know NL outperforms the other two types of queries in
> > OLTPs...
> > > > how about stating WHY! This is misleading because it makes people
> think
> > they
> > > > should automatically avoid HJs and MJs.
> > >
> > > It is not the percentage of cases where NL outperforms others. It is
> > > the skew in performance difference. Once again, if the best join
> > > method is, say, HJ but you use indexed nested loops, then, I routinely
> > > see 2, 3, and sometimes even 5 times elapsed time difference. Big
> > > deal! When NL outperforms HJ we often speak seconds versus hours.
> > >
> > > > The reason is simple. NL is optimized for returning a relatively
small
> > > > number of rows. MJs and HJs are optimized for returning a relatively
> > large
> > > > number of rows. In OLTPs most of the time you only need a relatively
> > small
> > > > number of rows. Its batch processing where you use MJs and HJs most
of
> > the
> > > > time.
> > >
> > > Not necessarily. If all the required columns of the inner table are in
> > > the index, then we don't incur overhead of table access by row id
> > > (random io) and NL would outperform the others on the big relations as
> > > well.
> > >
> > > > Does Dan Tow discuss this?
> > >
> > > There is a section discussing NL vs other methods, but his arguments
> > > aren't necessarily identical to mine.
> > >
> > > > I have never heard about the 'leverage' of HJ over MJ. Who said
that?
> > Who
> > > > are the often 'sited'? This sounds inaccurate.
> > >
> > > <quote>
> > > the often cited argument in favour of HJ or MJ is the advantage of
> > > leveraging disk sequential io vs. random io
> > > </quote>
> > >
> > > There is no "'leverage' of HJ over MJ" in the above quote. I meant
> > > that NL is associated with random IO, while HJ and MJ - with
> > > sequential io.
> > >
> > > > The book intrigues me. It sounds different from the other SQL Tuning
> > books
> > > > which just give you 'tips' on things to try without an approach.
> > However,
> > > > your review about join types is not correct.
> > >
> > > AFAIK this is the first book solely dedicated to tuning individual SQL
> > > statements, as opposed to competitors where authors compensate their
> > > lack of SQL tuning expertise with dull chapters how to tune overall
> > > system performace. Once again, SQL optimization is a very nontrivial
> > > subject.
> >
> > Is it fair to say this book is a book on tuning Oracle SQL statements?
> >
> > In my experience, optimizing Sybase derivative SQL statements often
> involves
> > breaking them up and using temporary tables, which sounds a little
> different
> > from your description of the book.
> >
> > Are you familiar with Shasha's and Bonnet's book on performance tuning?
It
> > sounds like it would have much greater breadth but not as much depth in
> > overcoming this one major flaw in most if not all SQL dbmses.
> >
> >
>
> you mean this book? Is it really any good? Looks like inter-database stuff
> and that is always watered down.
>
>
http://www.amazon.com/exec/obidos/tg/detail/-/1558607536/qid=1073013652//ref
> =sr_8_xs_ap_i2_xgl14/002-7457171-4222439?v=glance&s=books&n=507846
Yes, that is the book in question. I do not know how you expect it to be
watered down. It has an excellent inventory of available physical structures
describing their impact on performance and takes a principled approach to
performance tuning.
Received on Thu Jan 01 2004 - 22:10:00 CST
