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

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 1 Apr 2002 23:15:11 -0600
Message-ID: <un0wm4io7.fsf@rcn.com>

On Mon, 1 Apr 2002, jonathan_at_jlcomp.demon.co.uk wrote:
>
>

>> in the order small2, small2, big.

>
> That should have been
> small2, small1, big
>
> By the way, this method seems to extend to an arbitrary number of hash
> joins - if there is just one very big table, you can get Oracle to
> prepare a hash of all the small tables, then probe the big table once,
> passing the results through every single hash table very rapidly.

What would be the swap_join_inputs this time? Do I need more than one?

Say I have small1, small2, small3, small4, small5, big.

/*+

   ordered

   use_hash(big)
   use_hash(small1)
   use_hash(small2)
   use_hash(small3)
   use_hash(small4)
   use_hash(small5)

   swap_join_inputs(small?)
*/

I might get a plan looking like?

hash join

        table access full of small5
       hash join
               table access full of small4
              hash join
                      table access full of small3
                     hash join
                             table access full of small2
                             hash join
                                 table access full of small1
                                 table access full of big

What should the hint and plan look like so I can know that this strategy is being followed for more than three tables?

> The drawback is that you end up using hash_area_size once for every
> small table - which could be a lot of memory.

Is there any merit to using star transformations? I was just about to experiment with this strategy until I caught your reply, and then I went off to experiment with what you said. I was thinking the big could be viewed as my fact table and small1/6 could be my dimension tables, although, this is only for thinking about improving the performance, not the design.

Thanks again Jonathan.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Mon Apr 01 2002 - 23:15:11 CST