Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Book Review: "SQL Tuning" by Dan Tow
"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.
>
>
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
The book in question appears to have some kind of 'method' for tuning SQL. Ill grab a copy eventually. There appear to be a number of inaccuracies in it though, but they are the typical inaccuracies and are not prevalent. Received on Thu Jan 01 2004 - 21:22:53 CST