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

"Ryan" <rgaffuri_at_cox.net> wrote in message news:Dr5Jb.59057$hf1.31218_at_lakeread06...
> HJ with full scans of both tables routinely outperforms NJs by 1000% or
more
> when a large number of rows are involved. Its not even close.

I apologyse for answering to your post in small increments.

I suspect that in your case join index is missing. Clearly, NL without join index is a very dumb method, which is extremely slow on large inner relation size.

Now, one of the steps in HJ method is creating temporary lookup table. That table purpose is similar to join index: to eliminate full scan of inner relation! To be fair to NL, we have to allow creating a temporary join index if the inner relation is large and if join index is missing. Creating temporary index overhead is proportional to the relation size, so that the additional overhead is small compared to the cost of join itself.

Finally, I'm claiming that NL (with temporary join index creation enhancement) is the only method needed for main-memory databases. Received on Fri Jan 02 2004 - 13:42:28 CST