Re: Join Two Extremely Large Tables

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b7mull$l1b$1$8300dec7_at_news.demon.co.uk>...
>
> That must have taken a bit of time ! Roughly
> how big is your key size ? Does your process
> delete most rows after reporting them and then
> add more rows through an increasing sequence ?
>
 

  The key is actually very small, it is a number.   I think the problem is deletion and repopulation   of the table.   

>
> 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.  

  Thanks for your thoughts. Received on Mon Apr 21 2003 - 13:32:51 CDT