Re: Join Two Extremely Large Tables

bchorng_at_yahoo.com (Bass Chorng) wrote in message news:<bd9a9a76.0304161946.4c0f5cbd_at_posting.google.com>...
> I have 2 tables A and B. Table A contains 86 million rows, about 4.5 GB
> in size. Table B contains 1.2 billion rows, about 25 GB in size.
>
> For each row in table A, I need to query table B, which is indexed,
> returning in average of 15 rows and process them.
>
> If I use foreach cursor and loop thru table A, the result will be
> probably at least 3-4 billion IOs. Since in reality, my index depth
> is probably far more than 10 levels, I would expect total IOs to be
> near 20 billions.
>
> Basically, this is an enormous index range scan.
>

Usually you'd prefer to run a full scan on a bigger table and join it to the smaller one by index (if available). If join columns constitute primary or unique key on a smaller table, you have an additional performance gain, unique index scan is faster.

BTW, leave join operations to SQL, don't use explicit cursors on each table.

I don't think hash join is a solution here. However, you can always try. Just make hash_area_size for the session as big as you possibly can.

> The # of IO is not a problem, but the time it takes is. This process
> would take 6-7 hours.
>
> Would if be feasible for me to hash join these 2 giant tables
> and not use index at all ? Is it faster this way ?
> What about my temp segment ? I would either give it a
> hash_area_size of 10 GB (not even sure if Oracle accepts that)

I think 2GB is a limit. Not sure though, it might depend on version. You need a big temp segment anyway.

> or prepare a temp segment that size, or my process would
> fail.
>
> Your advice is appreciated.
Received on Thu Apr 17 2003 - 11:49:31 CDT