Re: Hashing two tables for a join to BIG TABLE?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 1 Apr 2002 17:41:59 +0100
Message-ID: <1017679245.8111.0.nnrp-08.9e984b29@news.demon.co.uk>

This may be highly version dependent, but it sounds like you have the tables in the wrong order.

I infer that you are not trying to exclude data by the joins, but are trying to extend the rows of the big table.

The following should do what you want:

select

    /*+

            ordered
            use_hash(big)
            use_hash(small2)
            swap_join_inputs(small2)

    */
    . . .
from

        small1,
        big,
        small2

where

    ...

NOTE ESPECIALLY the order of the tables. Despite the apparent contradiction in the ORDERED hint, Oracle should visit the tables in the order small2, small2, big.

This set of hints should result in a plan like:

hash join

        table access full of small2
        hash join
            table access full of small1
            table access full of big

Provided you have your hash_area_size large enough to hold an in-memory hash of small1 and small2 (NB your hash_area_size will be allocated twice using this plan, once for each small table), the first row should be generated almost as fast as you can scan small1 and small2 in turn to build the two hash tables in memory.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Galen Boyer wrote in message ...


>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 Mon Apr 01 2002 - 10:41:59 CST