Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Hashing two tables for a join to BIG TABLE?

Hashing two tables for a join to BIG TABLE?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 31 Mar 2002 19:30:04 -0600
Message-ID: <u1ye03ulx.fsf@rcn.com>


Suppose I have these tables:

create table small1 (id1 integer, stage_id1 integer); create table small2 (id2 integer, stage_id2 integer); create table big (stage_id1 integer, stage_id2 integer, var_value varchar);

I need to join the big table to both small tables to translate the staging/client ids of the big table to the warehouse ids found on the small tables and then insert this result to final data warehouse table.

Is there anyway to get Oracle to build separate independent hash tables for both small tables and then have it hash the appropriate columns on the big table so it won't have to build the hash table for the big table but instead, just use the hashing function on the big table's columns, joining to the small tables in memory and then insert the translated rows to the final production table?

Right now, if I use an ordered combined with hash hints, I get small1 and small2 sort-merged with a MERGE JOIN CARTESIAN, but the cardinality is huge, because these two tables aren't related.

Thanks.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Received on Sun Mar 31 2002 - 19:30:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US