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

The plan you guessed at below is the
one you are looking for, but the table
order in the from clause would have to be:

small1,
big,
small2
small3
small4
small5

The plan may appeared from just the
ORDERED hint, the use_hash() hints
were to avoid some nested loop options
in my case, and the swap_join_inputs()
all happened automatically with no need
for hinting (Oracle is usually very good at swapping sides in a hash when needed). If you need the swap_join_inputs hint,
it applies for every table after the BIG table in the FROM clause.

star_transformation would only be appropriate if you were eliminating data through the join, not for simple translation of 100% of the data. An alternative for this type of task would be to scan BIG, and then use a nested loop into each of the small tables in turn. It may be even more efficient to make the small table IOTs, or single table hash clusters on their primary key as this would probably reduce the logical I/O for this strategy.

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


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