bchorng_at_yahoo.com (Bass Chorng) wrote in message news:<bd9a9a76.0304211032.681cd7ba_at_posting.google.com>...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b7mull$l1b$1$8300dec7_at_news.demon.co.uk>...
...
> > Sounds like the nested loop is it. You should get
> > a noticeable improvement by doing the join (hinted
> > very carefully to do exactly what you want) and
> > then using array fetches of a few dozen to a few
> > hundred rows at a time. It's messier to code
> > at the boundary conditions than one row at a
> > time - but significantly more efficient.
>
> I think nested loop gives you scalability too.
> If I use hash join, I have to calculate the size
> of hash_area_size to make sure it performs right.
> This makes the memory requirement unscalable.
>
I am not sure nested loop is the best choice, simply because it
requires many more times logical I/Os (compared to hash join),
and most of them will be turned into physical I/Os for tables and
indexes this large. If you can pull all the colums you need in one
join, and your temp tablespace is not created on a very, very slow
drive, hash join should be the winner. Of course your temp tablespace
needs to be large enough. If nested loop turns out to be faster for
joining these tables I'd be very interested to know.
Received on Mon Apr 21 2003 - 16:22:49 CDT
