I bought and read Dan Tow's book. He has a small section where he
explains where it is best to use a HJ over NL. It didn't sound to me
like he was too biased. Early in the book he talked a lot about using
NL, but I figured that was because he didn't want to go into the HS/NL
conversation yet.
"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:<JYjJb.5$Ma6.111_at_news.oracle.com>...
> "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 Sun Jan 25 2004 - 04:51:53 CST
