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

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

The drawback is that you end up using
hash_area_size once for every small table - which could be a lot of 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


Jonathan Lewis wrote in message
<1017679245.8111.0.nnrp-08.9e984b29_at_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

>    ...