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

> "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. Received on Thu Jan 01 2004 - 17:44:38 CST