Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Book Review: "SQL Tuning" by Dan Tow

Re: Book Review: "SQL Tuning" by Dan Tow

From: Bob Badour <bbadour_at_golden.net>
Date: Thu, 1 Jan 2004 19:47:26 -0500
Message-ID: <lrOdnZGXoJslImmiRVn-sQ@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. Received on Thu Jan 01 2004 - 18:47:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US